Oracle DB

• unencrypt ctrl file: alter database backup controlfile to c:\f.f

1. Relational Model, RDBMS

emp_id	name	salary	dept_id
01	smith	20	01
... { emp_id (01) - uniq PK; dept_id - FK referrg to 
t_dept }

Types of DB
• hierarchical
• network
• relational
• object relational (eg. ora8i, ora9i)

Relational Model
• by Dr.E.F.Codd (IBM, 1970)
• basic ideas: db = series of unordered tbls (or relatns = related sets of info) which canbe manipulated usg non-procedural operns. [ vocab: relations, attributes, tuples = tables, columns, rows = files, fields, records ]
• 1 table = 1 real-world obj/evt
• each row must be unique
• 3 types of anomalies: insertn, deletn, update (durg insert, del, updt)

Keys
• primary key; col containg unique vals for a tbl; uniquely identifies a row in a tbl, ie. exists only 1 row in tbl havg a primary key value.
  • synthetic or surrogate key: a sys-generated sequence number used as primary key (+ unique & no inherent meang → no change, eg. customer_id)
  • avoid prim keys which change, eg. ph.nu is bad prim key col
  • each tbl can have only 1 primary key (1/+ cols); oracle does not require every tbl to have a prim key yet recommeded
  • composite primary key/concatenated primary key: prim key havg multiple cols
• foreign key = col linkg the multiple tbls together.
• oft foreign key col & prim key have identical names but not requd.
• candidate key - all cols (or combinatns of cols) in a tbl with unique values, = primary key + alternate keys (all candidate keys minus primary key)
• simple key = 1 col, composite key = 2/+ col
• which candidate key = primary key : Fabian Pascal ('SQL and Relational Basics'), notes this tobe based on principles of minimality (choose the fewest cols necessary), stability (choose a key that seldom changes), and simplicity/familiarity (choose a key that is both simple and familiar to users).
• searches and sorts of numeric columns are more efficient than of text columns in Microsoft Access (and most other databases).
• Domain - pool of values from which cols are drawn.

Relationships
• Relationship: if 2 tbls have a common col/cols, the tbls have a relationship
• Cardinality of a Relationship = actual nu of occurrences foreach entity; ratio of a parent and child table from perspective of parent tbl; how many rows exist between 2 tbls for given PK value.
• One-to-One Relatnship (1:1): for every row in table1 thereis at most 1 row in table2; implementn usu as single tbl., rarely as 2 tbls with pk, fk
• tbls in 1:1 must have same primary key servg as join column.
• One-to-Many (1:M) (parent-child or master-detail relatnship): forevery row in tbl1, thereare 0/1/many rows in tbl2 & forevery row in tbl2 there is exactly 1 row in tbl1; most commonly used relatn; eg. lookup tbls for more details; implementn by splittg data 2 tbls with primary key and foreign key relationships.
• Many-to-Many (M:M) relatnships: forevery row in tbl1 thereare many rows in tbl2 & forever row in tbl2 thereare many rows in tbl1; implementn usg junctn tbl with the keys from both tbls formg the composite primary key of the junctn
• pk vs. unique key: both enforce uniqueness on col wherein defined, but pk creates clustered idx on col, unique key mk nonclustered idx (deflt) & pk doesnt allow nulls, unique key allows 1 null only (sqls)
• relational dbe model requmts → resoln of M:M relations into 1:M relation tbls; done usg associative/intersection tbl
• candidate key - can id each row uniquely; 1 candidate key = pk, rest non-pk candidate keys = alternate keys.
• composite key - combin 2+ cols
• default = value used if no value given durg insert
• identity cols + timestamp cols cannot have defaults bound to them
• Schema Diagrams: depict tbl relationships; "single line" = "one" side + crow's foot/fork = "many" side of 1:M relatn
• FK - a foreign key col
• identifying relation : FK propagated to child entity as primary key
• nonidentifying relation : FK becomes 1 of the nonkey cols; may accept null value in the foreign key column.

ERD
• entity: an entity/thg of importance, eg. dept
• attrib: a property/desc of qualities of an entity, eg. name, bday
• relnship: named assocn betwn entities, eg. employee-dept.
• * - mandatory attrib
• o - optnal attrib
• # - primary unique identifier uid
• (#) - secondary uid
• Uppercase - entity; lowercase - attrib
• - - - : `may be'
• _____ : `must be'
• crows foot: 1/+
• ----- (single line): `1 and only 1'

MS Access
• Access: Edit > Relationships (edit relations)
• Access: does not require primary key for each tbl
• Microsoft Access supports only long integer counter values, limit 255 cols / tbl

x. DB Architecture of Oracle

Physical Structure

		________________________________________
User Process	|		Instance		|
    \|/		|					|
Server Process->| > SGA					|
		|  - Shared Pool			|
	PGA     |    o Library Cache			|
		|    o Data Dictionary Cache		|
		|  - Db Buffer Cache			|
		|  - Redo Log Buffer			|
		|  - Java Pool 				|
		|  - Large Pool				|
		| > PMON		> SMON		|
		| > DBWR		> LGWR 		|
		| > CKPT		> Others	|			
		_________________________________________

			_________________________
	Parameter File	|	Database	|
			| > Datafiles		|   Archived
	Password File	| > Control Files	|   Log Files
			| > Redo Log Files	|
			_________________________

Instance - a means to access an ora db
• server = ora instance + ora db
• instance = bg proc's + memory structures (in sga)
• instance started → sga alloc + bg proc's started
• always opens only 1 db

• connectn = comm pathway betwn a user proc + ora server
• session = specific conn of a user to an ora server
• dedicated server conn = 1 user to 1 server proc
• SGA (System Global Area) - alloc every t an instance is started

Oracle DB Architecture
• Db ( = colln of data treated as unit) File Types / Physical Struct:
  1. Datafiles (actual data): incl data dictionary
  2. Redo Log Files (record of changes made to db → recovery)
  3. Control Files (maintain + verify db integrity)
• Other Key File Structs (not part of db)
  • Parameter File (def props of ora instance)
  • Password File (auth users privileged to start + shutdown ora instance)
  • Archived Redo Log Files (offline cps of redo log files for recovery from media failures)
• Memory Structure
  • SGA (System Global Area: info shared by db proc's) : alloc @ instance startup, part of ora instance ( > show sga: )
    • Shared Pool: objs which canbe shared globally; store most recently execd sql stmts; most rectly used data defns; stores fixed + variable structures
      • Library Cache: info abt most rectly usd sql stmts, mngd by LRU (least recently used) algo.: has 2 structs: i) shared sql area; ii) shared plsql area
      • Data Dictionary Cache / Dict Cache / Row Cache : most recently used defins in the db; incl info abt db files, tbls, indexes, columns, users, etc.; cachg data dict info improve perf for dml + queries
      • SHARED_POOL_SIZE param: max shared pool size;
        alter system set SHARED_POOL_SIZE = 64M;
      • recursive calls: db has to query data dict tables repeatedly if data dict cache is too small → slower than direct queries on the ddc
    • Db Buffer Cache: cps of data blocks which have been retrieved from the datafiles in db;
      • ↑ perf gains durg select + updates
      • LRU (least recently used) algo
      • DB_BLOCK_SIZE : primary block size
      • dyn. resizg of db buffer cache:
        alter system set DB_CACHE_SIZE = 96M;
      • indepdt sub-caches: DB_CACHE_SIZE (sizes deflt buffer cache only, cannot be zero), DB_KEEP_CACHE_SIZE (sizes keep buffer cache to retain blocks in memory likely to be reused), DB_RECYCLE_CACHE_SIZE (sizes recycle buffer cache used to eliminate blocks from mem havg little chance of reuse)
      • DB_CACHE_ADVICE = ON | OFF | READY (advisory off, but mem allocated): gather statistics for predictg difft cache size behaviour; set from off - ready - on ensures no error; direct on - off might cause ORA-4031 error
      • V$DB_CACHE_ADVICE: show stats for Buffer Cache Advisory
    • Redo Log Buffer: circular buffer record all changes made to db data blocks; for recovery
      • redo entries: changes recorded
      • LOG_BUFFER: size
    • Large Pool: optnal area in sga; relieves burden on shared pool
      • LARGE_POOL_SIZE non-dynamic param : nu bytes
      • large pool used iff PARALLEL_AUTOMATIC_TUNING = TRUE ; else buffers allocd to Shared Pool
      • RMAN (recovery manager) uses the large pool when BACKUP_DISK_IO = n and BACKUP_TAPE_IO_SLAVE = TRUE
      • does not have an LRU list
    • Java Pool: optnal; requd if usg java; JAVA_POOL_SIZE (= 24M deflt Ora9i)
    • Others
    • sga is Dynamic : change cfg without instance shutdown
    • Sizing SGA: usg SGA_MAX_SIZE param; also
      • DB_CACHE_SIZE : size of cache of stdd blocks (deflt 48m unix, 52m nt)
      • LOG_BUFFER : byte nu alloc for redo log buffer
      • SHARED_POOL_SIZE : bytes for shared sql (deflt 16m, 64m if 64 bit)
      • LARGE_POOL_SIZE : deflt 0 unless init.ora: PARALLEL_AUTOMATIC_TUNING = TRUE, then deflt auto-calctd
      • JAVA_POOL_SIZE : deflt 24m
      • sga size < SGA_MAX_SIZE - DB_CACHE_SIZE - LOG_BUFFER - SHARED_POOL_SIZE - LARGE_POOL_SIZE - JAVA_POOL_SIZE
    • min. sga cfg = 3 granules = fixed sga (incl. redo buffers) + db buffer cache + shared pool granule
    • V$BUFFER_POOL: size of granules view
  • PGA (Program / Process Global Area) : mem reservd foreach user proc cnnectg to ora db; alloc when server proc started, dealloc when proc terminated, used by only 1 proc (sga - shared by many processes); usu contains:
    • Private sql area: OPEN_CURSORS (deflt. 50) - nu of private sql areas a user proc can alloc
    • Session memory: sessn vars
    • SQL Work Areas: mem-intensive operns
    • content of pga depds on if instance runng in Dedicated Server or Shared Server cfg

Process Structure
1. User proc: started at t a db user request conn to ora server
2. Server proc: connects to ora instance, startd when user est sessn; direct intern with server usg OPI (Ora Program Interface); Shared Server env: server proc handles request of many user procs
3. Bg proc: started when ora instance started
   Mandatory Bg Proc
   1. PMON : Process Monitor cleans up after failed procs by rollg back transn, releasg locks, freeg other resrcs, restartg dead dispatchers.
   2. SMON : System Monitor for Instance Recovery, Coalesces free space, Dealloc temp segments
   3. LGWR : Log Writer mks sequential writes from Redo Log Buffer to the redo log file when transactn commits, when Redo Log Buffer 1/3 full, when > 1M changes recorded in Redo Log Buffer, before DBWn writes, or every 3 secs.
   4. CKPT : Checkpoint defs checkpoint positns in th redo log file where db recovery to begin incase of instance failure; fired every 3 secs
   5. DBWn : Db Writer records changes to undo + data blocks in db buffer cache, writes dirty buffers from db buffer cache to datafiles ;
      writes when checkpt occurs, dirty buffers reach threshold, no more free buffers, timeout occurs, rac ping request made, tablespace put OFFLINE, tablespace made READ ONLY, table dropped or truncated, tablespace BEGIN BACKUP occurs
   Optnal Bg Proc
   • ARCn - Archiver, auto archive online redo logs when ARCHIVELOG mode set (NOARCHIVELOG - ARCn off, usu. not used for prod db)
   • RECO - recoverer
   • QMNn - advanced queug
   • Dnnn - dispatcher
   • Snnn - shared server
   • Pnnn - parallel query slaves
   • CJQ0 - coordinator job queue bg proc
   • LMDn - rac dlm monitor - remote locks
   • LMON - rac dlm monitor - global locks
   • LCKn - rac lock mgr - instance locks

Logical Structure

DB Storage Hierarchy

______________________
Database                  
  ____________________ 
  Tablespace             
    __________________   
    Segment             
      ________________
      Extent          
       _______________
       Oracle Block

• Tablespace
  • Types of tablespace:
    1. SYSTEM tablespace : made with db, contains data dict, and SYSTEM undo segmt
    2. Non-SYSTEM tablespace : sept segmts, ease space admin, ctrl amt of space alloctd to user
  • ts can belong to only 1 db at a time
  • ts canbe taken offline except SYSTEM ts or ts with an active undo segment
  • datafile can belng to only 1 tablespace
  • a segmt canot span tablespaces, however a segment can span mult. datafiles belongg to the same tablespace
  • 1 extent cannot span datafiles
  • 1 data block = 1/+ os blocks
  • DB_BLOCK_SIZE - stdd data block size, shouldbe multiple of os block size
  • create tablespace xtablespace datafile '/u01/oradata/f.dbf' SIZE 100M autoextend on next 5M maxsize 200M;
• Segments (space-occupyg objs in a db)
  Types of segmts
  • Table segmt: non-clustered non-partitnd data
  • Table Partition: data from 1 tbl residg in difft ts
  • Cluster : rows in cluster sotred based on key col values
  • Index : all entries for 1 index
  • index-orgzd tbl: data stored based on key value
  • idx partitn: spread across several tablespaces
  • Undo segment: store old value
  • Temp segment: store intermediate sort results
  • LOB segmt = large objects
  • Nested tbl segmt: col = tbl
  • Bootstrap / Cache segmt : made by sql.bsq script durg db creatn
• Extent = space used by segmt
  • extt alloctd when segmt is created/extended/altered
  • extt dealloctd when segmt is dropped/altered/truncated
• Block: min unit of IO
  • block size = 2 - 32K (power of 2)
  • stdd block size = block size of SYSTEM + TEMPORARY ts = DB_BLOCK_SIZE (cannot be changed w/o recreatg db); 9i upto 4 non-stdd block sizes specifiable
  • DB_CACHE_SIZE - spec size of DEFAULT buffer cache for stdd block size; deflt = 48M, min. = 1 granule (1 unit of contiguous virtual memory allocn) (4M if sga < 128M, otherwise 16M)
  • db block contents = header (from top down) + free space (initially contiguous) + data (from bottom up)

x. DBA Tools

Ora Universal Installer
• notroot:INSTALL/install/solaris % ./runInstaller ; or D: ... install\win32 > setup
• usg response files: ./runInstaller -responsefile stage/response/myresponsefile -silent or setup.exe -responsefile Response/myresponsefile -silent

DBA Users auto-created
• SYS:change_on_install ε DBA role (all db sys privs);
  • owns db data dict
  • connect as sysdba or sysoper, else error: "ORA-28009: connecting to SYS should be SYSDBA or SYSOPER"
• SYSTEM:manager ε DBA role; owner of internal tbls + views
• SYSDBA role: SYS schema; requd to mk db
• SYSOPER: public schema

SQL*Plus

• sqlplus /nolog or connect / as sysdba (connect to idle instance)

OEM (Ora Enterprise Mgr)

• OMS (Ora Mgt Server) - core of OEM
• Init Params: OEM > Databases > Instance > Cfg > General > All Init Params
• Creatg SPFILE: OEM > Object > Create spfile ; OEM > Object > Create pfile
• Modify SPFILE Params: Databases > Instance > Configuration > General > All Init Params
• start db: Databases > Instance > Cfg > General > Open > Apply
• Open db in restricted mode: SYSDBA: Instance > Cfg > General > Instance State : Shutdown > Apply > Immediate > OK > Close > Instance > Open > OK > Startup Options > Restrict access to db > OK
• Start Db in readonly mode: SYSDBA: Instance > Cfg > Instance state > Shutdown > Apply > Shutdown Optins > Immediate > OK > Close > Instance State : Open > OK > Startup Options > Read Only Mode > OK > Close
• Shutdown Db: Databases > Instance > Cfg > General > Shutdown > Apply
• Enable/Disable User tracg: Databases > Instance > Cfg > General > All Init Params > SQL_TRACE = TRUE > OK
• Ctrl file info: OEM > Databases > Storage > Controlfile > General > All Init Paramsa
• Addg online redo logs - OEM > Databases > Storage > redo Log Grps > Create > General > Create
• OEM > Database > Storage > Redo Log Groups > Remove > Rclick(remove)
• OEM > Database > Instance > Cfg > General > Database and Instance Info - Archive Log Mode | ... Cfg > Recovery
• Mk Tablespace: OEM > Databases > Storage > Tablespaces > R -> Create : General + Storage > Create
• mk Temp Tablespace usg OEM OEM > Databases > Storage > Tablespaces > Create : General & Type = Temporary > Storage > Create
• mk deflt Temp Tablespace : OEM > Databases > Storage > Tablespaces > r(Create) & General = Temporary + Set as Default Temporary Tablespace > Create
• ro tablespace: OEM > Databases > Storage > Tablespaces > General : Status : Read Only > Apply
• takg ts offline: OEM > Databases > Storage > Tablespaces : Mytablespace > General : Status = Offline > Mode > Apply
• changg deflt storage settgs: OEM > Databases > Storage > Tablespaces > R(Mytablespace) > View/Edit Details > Apply
• specifyg AUTOEXTEND for new datafiles: OEM > Databases > Storage > Datafiles : MyDatafile > Storage : Automatically extend datafile > Apply
• addg datafiles to a tablespace : OEM > Databases > Storage > Tablespaces > Add Datafile > General > Create
• movg datafiles: Databases > Storage > Tablespaces > General > Offline > Apply > General: File Directory > Apply
• add caches: Databases > Cfg > All Init Params : DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_16K_CACHE_SIZE, DB_32K_CACHE_SIZE
• OEM > Databases > Storage > Tablespaces > R() : Create > Storage > Block Size = _ > Create

2 Types of Init Param Files:
• Static param file PFILE = $ORACLE_HOME/dbs/initSID.ora; changes effected next startup; use sample text init.ora made by Univ.Installer durg install
• Persistent param file SPFILE = $ORACLE_HOME/dbs/spfileSID.ora: bin file
  sysdba > create SPFILE = '$ORACLE_HOME/dbs/spfileDBA01.ora' FROM PFILE = '$ORACLE_HOME/dbs/initDBA01.ora';
  reverse: create PFILE from SPFILE;
  • view + edit spfile: export SPFILE to PFILE, edit PFILE, recreate SPFILE from edited PFILE
  • V$SPPARAMETER: another src for viewg SPFILE
• order of precedence for STARTUP cmd:
  • spfileSID.ora
  • deflt spfile; else use PFILE to indicate non-deflt SPFILE loc
  • initSID.ora; else STARTUP PFILE = $ORACLE_HOME/dbs/initdba1.ora
  • deflt PFILE
• Modifyg params in spfile alter system set myparam = myvalue comment 'txt' SCOPE = MEMORY | SPFILE | BOTH (only currt instance, spfile only, both current instance + spfile) SID = 'mysid' | '*' (deflt spfile)
• Params which should be set in the Init Param File:
  • BACKGROUND_DUMP_DEST : where bg proc trace files are written
  • COMPATIBLE : versn of server requd
  • CONTROL_FILES : names
  • DB_CACHE_SIZE : cache size
  • DB_NAME : <= 8 chars
  • SHARED_POOL_SIZE : bytes of shared pool
  • USER_DUMP_DEST : where user debugg trace files for user procs

Startg Up a Db
1. Shutdown
2. NOMOUNT: instance started; only durg db creatn or recreatn of ctrl files
   1. read init files from $ORACLE_HOME/dbs/spfileSID.ora, if fail, spfile.ora, if fail initSID.ora (STARTUP .. PFILE... overrides this deflt behaviour)
   2. alloc sga
   3. start bg procs
   4. open alertSID.log file + trace files
   5. db mustbe named in DB_NAME param in init param file or in STARTUP cmd
3. MOUNT : ctrl file opened for this instance
   Db mounted but not open durg
   • renamg datafiles
   • en/disablg redo log archivg optns
   • performg full db recovery
   • alter database mydb MOUNT | OPEN;
4. OPEN: all files opened as descd by ctrl files: online datafiles opened, online redo log files opened
   • alter database mydb OPEN READ ONLY | OPEN READ WRITE;
   • verificn all datafiles + redo log files openable
   • db consistency checked, SMON perfs instance recovery if requd
   • STARTUP
   • STARTUP PFILE = $ORACLE_HOME/dbs/initdb01.ora
   • unix: cf. /var/opt/oracle/oratab for auto-startup/shutdown
   • startup + shutdown are sql*plus cmds not sql cmds
5. restricted mode: startup restrict ; alter system enable|disable restricted session (place instance in restricted mode; only users with RESTRICTED SESSION priv can access)
6. alter system kill session (term sessn)

How to Create Db
1. Ora Univ Installer
2. Ora Db Cfg Assistt (java based gui)
   1. set env vars ( = create db )
   2. Programs > Oracle - OraHome90 > Cfg + Mig Tools > Database Cfg Assist > Create a Database > Data warehouse | Gen purpose | New Db | Transn Processg
3. create database
   1. set env vars:
      • ORACLE_BASE = /u01/app/oracle (dir at top of ora sw)
      • ORACLE_HOME = $ORACLE_BASE/product/release (where ora sw installed, ofa reco)
      • ORACLE_SID = instance name, mustbe unique for ora instances runng on same machine
      • ORA_NLS33 = $ORACLE_HOME/ocommon/nls/admin/data (for non-ascii charsets)
      • PATH = ... $ORACLE_HOME/bin
      • LD_LIBRARY_PATH = $ORACLE_HOME/lib
   2. mk init param file: cp init.ora initSID.ora
   3. start instance in nomount
   4. run script containg CREATE DATABASE ...
   5. open the db
   6. run scripts catalog.sql (SYS:SYSDBA; mk views on base tbls + on dyn perf views, mk basic plsql env) , catproc.sql (SYS:SYSDBA; mk pkgs, procedures requd to use plsql), pupbld.sql (as SYSTEM; mk product user profile tbl + prevt warng messg each time user connects to sql*plus)

   ---

   create database mydatabase  { DB_NAME used if no name given }
    logfile
     GROUP 1 ('/$HOME/ORADATA/u01/redo01.log') SIZE 100M, 
     GROUP 2 ('/$HOME/ORADATA/u01/redo02.log') SIZE 100M, 
     GROUP 3 ('/$HOME/ORADATA/u01/redo03.log') SIZE 100M, 
    MAXLOGFILES 5  { max nu of redo log file grps ever creatable }
    MAXLOGMEMBERS 5 { max nu of log file members for a log file grp }
    MAXLOGHISTORY 1
    MAXDATAFILES 100
    MAXINSTANCES 1
    DATAFILE '/$HOME/ORADATA/u01/system01.dbf' size 325M
    UNDO TABLESPACE undotbs
    DATAFILE '/$HOME/ORADATA/u02/undotbs01.dbf' size 200M
        AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED { enable auto extensn of data files }
    DEFAULT TEMPORARY TABLESPACE temp
    CHARACTER SET US7ASCII
    NATIONAL CHARACTER SET AL16UTF16
    SET TIME_ZONE = 'America/New_York'
   

4. OMF (Oracle Managed Files: ora_*.dbf): estd by setting DB_CREATE_FILE_DEST = /$HOME/ORADATA/u05 or DB_CREATE_ONLINE_LOG_DEST_1 = /$HOME/ORADATA/u01 in init param file → use only create database mydb;
   omf namg conventn:
   • ora_%u.ctl (ctrl files; %u - unique 8-char strg)
   • ora_%g_%u.log (redo log files; %g - redo log file grp nu)
   • ora_%t_%u.dbf (datafiles; %t - tablespace name)
   • ora_%t_%u.tmp (temp datafiles)

• shutdown abort | normal | transactional | immediate (abort | wait forall users to disconnect | no clt can start new transn | almost like abort)

  shutdown mode 			A	I	T	N
  allow new conns			N	N	N	N
  wait until currt sessn ends	N	N	N	Y
  wait until currt transns end	N	N	Y	Y
  force a chkpt + close files	N	Y	Y	Y
  

• alertSID.log (alert log file; diag file); BACKGROUND_DUMP_DEST specs loc
• User trace file: xsid_ora_xpid.trc ; (prod by user proc) USER_DUMP_DEST - loc
  enablg sessn level + instance level tracg:
  • alter session set sql_trace = true; (sessn level tracg)
  • dbms_system.SET_SQL_TRACE_IN_SESSIO (sessn level tracg)
  • SQL_TRACE = true (instance level user tracg)
• orapwd
  • Passwd File Authn: % orapwd file=$ORACLE_HOME/dbs/orapwU15 password=admin entries=5 (entries: max nu distinct users allowed to connect as sysdba or sysoper; no spaces allowed ard = sign; w2k passwd file loc: %ORACLE_HOME%/database)
  • REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE (only 1 instance can use the passwd file; sysdba + sysoper privs canbe granted to usrs)
  • connect sys/admin as sysdba ... GRANT SYSDBA TO XUSER;

OFA (Optimal Flexible Arch)

product/
	9i/
		/bin
		/dbs
		/rdbms
		/sqlplus
admin/
	myinstance/
	pfile/
oradata/
	db01/
	system01.dbf
	control01.ctl
	redo0101.log ... 
	db02/
	system01.dbf
	control01.ctl
	redo0101.log ...

x. Normalization

Normalizatn & Normal Forms
• Normalizn: eliminatg redundancy (eg. rem data duplicatn) → avoid future data manip problems.
• Normal Forms: linear progressn of rules with each higher form achievg better effict design
• First Normal Form (1NF): all col vals mustbe atomic (ie. each row-by-col pos isnot array/list); eg. rem all repeatg grps to new tbl
• Second Normal Form (2NF) (only for tbls havg composite primary keys) : if tbl in 1NF + every non-key col is fully dependt on the (entire) primary key: ie. tbl onlyhas data for 1 entity + that entity is described by its primary key. (each non-key column must be fully dependent on primary key); eg. tbl describes single entity, primary key implies all other cols in tbl
• Third Normal Form (3NF): if tbl in 2NF + all non-key cols are mutually independant; ie. every non-key col mustbe a fact about the prim key col, eg. rem computed cols, rem mutually depdt cols. usu. noneed to normalize beyond 3NF
• BoyceCodd Normal Form: superset of 3NF + deals with deletn anomalies.
• 4NF: tackles potential problems when 3/+ cols pt of unique identifier & their mutual dependencies.
• 5NF: splits the tables further apart → eliminate all redundancy.
• decomposition = process of breakg a non-normalized tbl into normalized parts
• non-loss decomposition - no info sacrificed durg normalizn; → common col (ie. foreign key) mustexist.

Denormalization addn of redundancy to physical db design → improve query perf as nu joins reduced
• performance ↑ ∴ data split into many tbls & performance poor as many joins
• logical models: usu. fully normalized or at least in 3NF.
• hence denormalizn in data warehousg as perf ↑

Integrity Rules: i) db-specific rules : per db
ii) General Integrity Rules
• entity integrity: primary keys cannot contain null (missing) data (applies to both simple and composite keys: no individual cols can be null for comp keys)
• referential integrity rule: db mustnot contain any unmatched foreign key vals → row cannot be added to tbl with FK unless the referenced value exists in the referenced tbl + if the value in a table that's referenced by a foreign key is changed (or the entire row is deleted), the rows in the table with the foreign key must not be "orphaned."
  • change/deletn of refcd primary key optns : Disallow | Cascade | Nullify (forbid | update cascaded to depdt tbls | depdt foreign key vals set to null)
  • Access referential integrity: Edit > Relatnships > disallow | cascade only + FK col.Required = yes

Constraints
• constraints = enforce rules @ tbl level, enable rdbms to auto enforce db integrity w/o need for triggers/rules/deflts.
• types: NOT NULL - col cannot contain null, CHECK - a condn mustbe true , UNIQUE - col/1+ cols whose values mustbe unique forall rows, PRIMARY KEY - uniquely id each row, FOREIGN KEY - est fk reltnship.
• enable existg constrt: alter table xtable enable constraint xconstraint;
• SYS_C1: constrt names given by ora if no user-suppld names.
• constrt level: tbl or col
• sys-named + user-named constrts: create table xtable (acol number(6), bcol varchar2(10) NOT NULL, ccol date CONSTRAINT dcol NOT NULL, CONSTRAINT ecol UNIQUE(bcol) );
• create table xtable ( acol number(4), CONSTRAINT bcol_pk PRIMARY KEY(acol) );
• tbl canhave only 1 pk, unique idx auto created for pk col
• fk table-level : create table atable ( ... CONSTRAINT xconstraint_fk FOREIGN KEY (xcol) REFERENCES btable(xcol), ... );
• col-level FK: create table atable ( ... xcol number(4) constraint myconstraint REFERENES btable(xcol), ... )
• on delete cascade: del depdt rows in child tbl when row in parent tbl is deleted if fk
• on delete set null: convert depdt fk values to null
• ... xcol number(2) constraint xconstraint CHECK (xcol > 0) ...
• currval, nextval, level, rownum pseudocols
• userenv, user, uid, sysdate fu's
• alter table atable ADD CONSTRAINT myconstraint FOREIGN KEY(xcol) references btable(xcol);
• alter table xtable drop primary key cascade;
• alter table xtable disable constraint xconstraint cascade;
• select constraint_name, consrtaint_type, search_condition from user_constraints where table_name = 'xtable';

DB Design Steps + Tips
• Requirements Analysis: get data requiremts, user requiremts, learn sys: talk to people
• list requiremts + basic requiremts
• rough data entry forms (usg paper forms/old db)
• mk 1 huge non-normalizd tbl/form tobe normalizd later
• add dummy data
• start normalizatn: choose primary key (minimal, stable, familiar)
• draw relatns 1-1, 1-M, M-M
• decompose to 1NF, then 2NF, then 3NF. Higher not requd usu.
• create tbls, sample sqls
• user feedback
• conceptual data model - logically groups the major data elements from the requirements analysis into individual entities; data model entity, attribute, and candidate key or unique identifier = db table, column, and primary key, respectively; noncritical attribs excluded for focus; M-M reltns acceptable.
• logical data model = erd (entity relationship dia) - represt business requiremts + rules w/o technical issues.
• physical data model/schema diagram - a graphical model of the physical design implemtn of db; phys data model has tables & columns instead of entities + attribs of logical model.
• Ora9i < 512 petabytes data

• transaction = logical unit of work
• ACID = Atomicity, Consistency, Isolation, Durability

x. DB Objects

DB Objects
• table: basic unit of storage; = rows + cols
  • 1. User tables: made + maintained by users
  • 2. Data Dictionary: collectn of tbls + views made by ora server
  • tbl + col names: must startwith letter, < 30 chars, canonly have a-Z, 0-9, _, $, #, cannot be reserved word
• view: subsets of data from 1/+ tbls
• sequence: numeric value generator
• index: improve perf of some queries
• synonym: alternative names to objs
• schema - user name

Data Dictionary (tbls + views created by oracle serer)
• readonly tbls + views stored in SYSTEM tablespace owned by user SYS describg db + objs
• parts of data dictny:
  • Base tbls: store info abt db; 1st objs created in any ora db; autocreated when ora server runs sql.bsq script when db created; only ora server should write to tese tbls; created with create database
  • Data dicty views: base tbl summaries, created usg catalog.sql script

  ---

  Data Dicty view Categories:
• 1. DBA_*: what is in all the schemas; restricted to viewg only for DBAs / any user grated SELECT ANY TABLE priv.
  • query all objs: select owner, object_name, object_type from dba_objects;
  • genl overview: dicionary, dict_columns
  • schema objs: dba_tables, dba_indexes, dba_tab_columns, dba_constraints
  • space alloc: dba_segments, dba_extents
  • database struct: dba_tablespaces, dba_data_files
  • select * from dictionary where table_name like `dba_seg%';
  • get list of cols: desc dba_users;
• 2. ALL_*: what a currt user can access; info abt all tbls accessible to user
  • all objs to which user has access: select owner, object_name, object_type from all_objects;
  • all_tab_comments
  • all_col_comments
• 3. USER_* : abt objs owned by currt user
  • ret all objs contained in user's schema: select owner, object_name, object_type from users_objects;
  • user_tables: table_name
  • user_objects: object_type
  • user_catalog = CAT
  • user_constraints: constraint_type, consrtaint_name, search_condition, table_name
  • user_col_comments: comments
  • user_tab_comments: comments
  • user_cons_columns: constraint_name, column_name, table_name
• 4. V$ : dynamic perf views; virtual tbls recordg curt db activity, owned by sys, listed in V$FIXED_TABLE
  • select * from V$FIXED_TABLE; - listg of dynamic perf views
  • V$CONTROLFILE - names of ctrl files
  • V$DATABASE
  • V$DATAFILE
  • V$INSTANCE
  • V$PARAMETER
  • V$SESSION
  • V$SGA
  • V$SPPARAMETER
  • V$TABLESPACE
  • V$THREAD
  • V$VERSION
• other: DICTIONARY, DICT_COLUMNS
• select * from database_properties;

Admin Scripts
• cat*.sql: catalog + data dicty info; eg. catadt.sql and catnoadt.sql
• dbms*.sql: db pkg specs, eg dbmspool.sql
• prvt*.plb: wrapped db pkg code
• utl*.sql: views + tbls for db utils; eg. utlxplan.sql (mk tbl to view exec plan ofsql stmt)

Ctrl Files
• Multiplexg ctrl files (storg each cp on a difft phys disk) : init param file - ... CONTROL_FILES = $HOME/ORADATA/u01/ctrl01.ctl, $HOME/ORADATA/u02/ctrl02.ctl
• ctrl files canbe multiplexed upto 8x
• loc + names of ctrl files: select name from V$CONTROLFILE;
• views containg info abt ctrl files: V$CONTROLFILE, V$CONTROLFILE_RECORD_SECTION, V$PARAMETER, show parameter control_files

• sga - sys global area: memy area alloc each t db started for db info shared by db users
• oracle instance - bg procs + mem buffers

OCP misc

• unique constraints & primary keys are the 2 integrity constraints which auto-create an index when defined
• omittg where clause from delete → all records deleted from table
• restricted sessn: user connect to db in restricted session mode
• if "Exit" left out from simple loop → infinite loop
• all exit condns for while loops r handled in "exit when" clause
• delete from xtable → rem all data but keep tbl struct, cmp drop table xtable
• implicit cursors r declared implicitly forall DML & SELECT statements
• implicit cursors need not be closed before end of progs
• where, having, groupby : order oracle evaluates clauses
• where clause cannot be used to restrict groups
• update is not a sql*plus cmd, list, accept, change, describe are
• projectn allows choice of columns
• explicit cursor necessary if select stmt retrieves >1 row
• SELECT E.ENAME FROM EMP E WHERE E.EMPNO NOT IN (SELECT M.MGR FROM EMP M WHERE M.MGR IS NOT NULL); → all emps who dont have subordinates
• outblock vars visible in inner blocks
• = oper cant be used in nonequal join
• from user prompt:

  Declare 
  v_hiredate Date := Sysdate;
  Begin
  Insert into emp(empno, ename, hiredate, depno)
  values(empno_seques.nextval,'&Name',v_hiredate, &deptno);
  End;
  

• export ... compress → mk initial extent = sum ( all extents currently alloc'd to an obj)
• select tablespace_name, max_blocks from dba_tablespace_quotas where username = 'Fred'; →

  tablespace_name max_bytes
  xts		-1	→ tablespace xts hasbeen dropped
  

• reco MINEXTENT (creatg rollback segmts) = 20 to minimize dynamic extensn
• MINEXTENTS >= 2 when rollback segmt created
• %ORA_NLS33% - dir of nls support files for non-ascii charsets
• use existg data file: create database mydb ... datafile '/home/oracle/f.dbf' reuse;
• deflt role = role auto enabled when user logs on
• Nobody = owner of a role
• temporary segmts - most likely to cause fragmentn in permanent tablespaces
• block pace utilizn can be specfd only at segmt level
• with admin option - grant system privilege to other usrs/roles
• mult. ctrl files: list f's in CONTROL_FILES param
• ctrl file: should be multiplexed & is required to start the instance
• mount stage → open stage: online data files are opened + online redo log files are opened
• restricted sessn mode - requd for exportg consistt img of large nu of tbls
• alert file logs block corruptn errors, internal errors (ora-600) + db startup activities
• NLS_LANGUAGE init param: defs deflt sortg sequence of char data + lang used for d + m names/abbrevs
• create db → declare natnl charset + db charset
• ora instance = shared pool + redo log files; incl SGA + PMON proc
• 2 main procs involved if user start new sessn on db server: user proc + Oracle Server proc.
• shared pool area of SGA - used when compilg a sql stmt
• enable novalidate → accept constraint-violatg data but prevt constraint-violatg changes
• online redo logs: store cmd data NOT written to data files if instance failure
• online redo log members in a group: all members in a group are same size + members should be on difft disk drives
• archive log list; → list currt status of archivg
• alter database and logfile (`/u01/Oracle/dba/f.rdo' `/u02/Oracle/dba/f.rdo') size 1M; → mk new redo log grp of 2 members
• FAST_START_TO_TARGET param: improve perf of crash + instance recovery

Redo Log Files (redo transns in evt of db failure)
• record all changes made to data
• at least 2 grps requd
• forcg log switch: alter system switch logfile;
• force chkpts: FAST_START_MTTR_TARGET = 600 or ALTER SYSTEM CHECKPOINT;
• addg online redo log file grps: alter database add logfile group 3 ('$HOME/ORADATA/u01/log3a.rdo', '$HOME/ORADATA/u02/log3b.rdo') SIZE 1M;
• addg online redo log file members: alter database add logfile member '$HOME/ORADATA/u04/log1.rdo' TO GROUP 1, '$HOME/ORADATA/u04/log2.rdo' TO GROUP 2, '$HOME/ORADATA/u04/log3.rdo' TO GROUP 3;
  alter database add logfile; if OMF used
• droppg online redo log file grps: alter database drop logfile group 3;
• droppg online redo log file members: alter database drop logfile member '$HOME/ORADATA/u04/log3.rdo';
• if db runng in archivelog mode + log file grp not archived, then member cannot be dropped
• relocatg or renamg online redo log files: alter database clear logfile '$HOME/ORADATA/u01/log.rdo';
• for managg online redo log files with OMF define DB_CREATE_ONLINE_LOG_DEST_1, ...2, etc
• V$LOG, V$LOGFILE
• V$LOG.STATUS = unused | current | active | clearing | clearing_current | inactive (never been written to | online + active | active but not the currt online redo log file grp | beg recreated as empty log | beg cleared of a closed thread, usu due to io error | no lnger needed for instance recovery)
• V$LOGFILE.STATUS = invalid | stale | | deleted (file inaccessible | contents incomplete | in use | no longer used)
• NOARCHIVELOG - online redo log files overwritten each t online redo log file is filled, log switch occurs; deflt db made in NOARCHIVELOG mode
• ARCHIVELOG mode: archivg in either of 2 modes, LOG_ARCHIVE_START : TRUE | FALSE (archivg auto | manual archivg of redo log files)
• info abt archived logs: select archiver from V$INSTANCE;
• Space Mgt:
  • Locally managed tablespace (deflt): extents managed via bitmaps; each bit ε bitmap = block/grp of blocks
    • create tablespace xts datafile ... EXTENT MANAGEMENT LOCAL uniform size 128k (deflt size = 1M)
    • + reduce contentn of data dicts
    • + no coalescg requd as tracks adjact free space auto tracked
    • + avoid recursive space mgt opers (eg. repetitive memory freeg)
  • Dictionary managed tablespace: extents managed by data dicty. create tablespace ... EXTENT MANAGEMENT DICTIONARY ...
    • create tablespace ts datafile '/u01/oradata/f.dbf' size 500M extent management dictionary default storage (initial 1M next 1M pctincrease 0);
• Undo tablespace: used to store undo segmts only, extents locally mgd, can only use datafile + extent management clauses: create undo tablespace xundo datafile '/u01/oradata/f.dbf' size 40M;
• Temporary tablespace: used for sort operns, cannot contn permant objs, locally mgd extents reco
  create temporary tablespace temp TEMPFILE '/u01/oradata/f.dbf' size 500M extent management local uniform size 4M;
• deflt temp tablspace: alter database default temporary tablespace temp; (prevt system tablespace beg used as deflt temp tablespace)
• tempfiles cannot be readonly, cannot be renamed, cannot be made with the alter database cmd, requd for ro db, media recovery does not recover tempfiles, always set to NOLOGGING mode.
• ro tablespace: alter tablespace userdata read only
  but tbls + indexes canbe dropped from ro tablespace as only data dicty affected (DROP updts only data dicty)
• takg tablespace offline: alter tablespace mytablespace online | offline;
• system tablespace, ts with active undo segmts + deflt temp tablespace cannot be taken offline
• change storage settgs: alter tablespace mytablespace default storage (initial 2M next 2M maxextents 999);
• resizg tablespace: i) auto usg AUTOEXTEND (usg create tablespace, create database, alter tablespace ... add datafile), ii) manually usg ALTER TABLESPACE, iii) usg alter tablespace
• query dba_data_files to find if AUTOEXTEND is enabled
• movg datafiles: alter tablespace xts rename datafile '/oradata/f.dbf' TO '/oradata/g.dbf';
  alter database xdb rename file '/oradata/f.dbf' TO '/oradata/g.dbf';
• droppg ts: drop tablespace xt including contents and datafiles cascade constraints ; (drop segmts + datafiles) (cannot drop system ts or ts havg active segmt)
• usg OMF: alter system set db_create_file_dest = '/oradata/dba01'; → datafile auto stored in dir given, deflt size = 100M, autoextend = unlimited
  create tablespace ts datafile size 20M; (creatg omf ts)
  alter tablespace ts add datafile; (add omf datafile to existg ts)
• ts info:
  tablespaces : DBA_TABLESPACES, V$TABLESPACE,
  datafile info : DBA_DATA_FILES, V$DATAFILE,
  temp file info : DBA_TEMP_FILES, V$TEMPFILE
• creatg nonstdd block size ts: create tablespace ts datafie 'f.dbf' size 10M BLOCKSIZE 4K; (DB_CACHE_SIZE + at least 1 x DB_nK_CACHE_SIZE must exist, and value must = one of the DB_nK_CACHE_SIZE params)
• nonstdd block sizes cannot be used for temporary ts

Indexes

• b-tree index
• bitmap index
• function-based index
• reverse-key index: spread distrib of an index across index tree
• reverse b-tree index canbe converted to normal idx durg rebuildg idxs
• Wrapper: encrypt pl/sql pkg bodies + hide app code
• fire db evt trigger: drop user + alter view
• if PCTUSED threshold crossed → block inaccessible to insert stmts
• analyze table .. compute statistics
• profile assignmts do not affect currt sessns
• alter user cmd used to assign profiles to usrs
• profiles enable grp resrc limits for similar usrs
• passwd change not avail by usg profile
• profiles ctrl: idle time, cpu time, nu of concurrt sessns for a username
• sql*loader uses log file + data file
• in sql*loader, redo for inserts is optnal + durg load, tbl data canbe modifd by other usrs
• parallel direct-load insert: server proc inserts rows into temp segmts, which r convertd to extents
• incr size of tablespace: i) alter tablespace ... MAXEXTENTS .. ii) alter tablespace .... MINEXTENTS ...
• alter database .. rename datafile → updts ctrl f
• temp tablespaces cannot hold permtt schema objs
• segments can span multiple data files, cannot have zero extents, cannot span multiple tablespaes, cannot belong to more than 1 tbl
• WORM drive (write-once)
• create tablespace ... minimum extents : det initial size of tablespace
• permtt tablespace canhave permtt + temp segmts
• Orajari file=orapsORCL password=xpwd entries=5 → 2 usrs SYS + SCOTT havg passwd xpwd auto-included
• if user member of DBA grp → usr not require passwd
• renamg datafile: tk tablespace offline, cp/mv file, alter database rename file ... , tk tablespace online
• objs canbe dropped from readonly db
• media recovery does not recover tempfiles
• tempfile cannot be mk usg alter database cmd
• tempfiles cannot contn permtt segmts
• size of datafile canbe reduced to size of last block of last obj
• ctrl f + online redo log f should be multiplexed
• alter system switch logfile → forces log switch + causes checkpoint
• sql*loader: direct load: clustered tbls cannot be used with direct loads DEFAULT = deflt profile for usrs
• profiles can ctrl cpu time, connect t, nu blocks read
• create profile ... cannot use password_change_new clause
• instance = lib cache + CKPT proc + ...
• mk new passwd file: orapwd file*orapwORCL password*secure entries*5
• index made for pk col auto durg table creatn; also for UNIQUE constraint create table xtable, (xcol number(9) constraint xpk primary key, ycol number(9) constraint xnk not null); → idx made for xcol
• addg not null constraint: alter table xtable modify(xcol varchar2(25) NOT NULL);
• drop table xtable; → drop tbl + struct without any rollback optn
• truncate table xtable; → del all data + keep tbl struct + reset highwatermark

Views

• session_privs : list of privileges avail for currt session to a usr
• v$controlfile_record_section : dyn view showg info abt difft sectns of the ctrl file
• v$sga + v$instance : only 2 dyn views avail when db started in nomount state
• nls_database_parameters : db char set
• dbms_tts → verify tablespace self-contained
• dba_free_space - data dicty view showg avail free space in a certain tablespace
• all_objects - all db objs accessible to usr
• dba_segments - data dicty view for tbl header block nu
• dba_tables : nu rows in a tbl
• dba_users : data dicty info abt acct expirn dates
• nls_instance_parameters : show NLS init params explicitly set
• MY_* data dicty views does not have an owner col
• data dictys: my_*, dba_*, all_*, user_*
• pl/sql prog unit stored in data dicts
• base tbls of data dict created when db created
• dba_sys_privs : which usrs have select any table privilege
• sql+> startup → open a db
• v$log, v$thread - dyn. perf.views for nu of online redo log grps + currt log grps
• if all members of currt online redo log grps not avail → instance crashes
• dba_extents + dba_segments - show nu of extents of a certn segmt
• dba_extents, dba_free_space - 2 data dict views queried to find space usage in a tablespace
• ctrl f shouldbe multiplexed; → cp ctrl f when db shutdown, or list multiple ctrl fs in param f durg db creatn
• alter ... user steve default role → mk all roles granted to steve deflt roles
• dba_constraints, dba_cons_columns - data dicty tbls for info abt all constraints on tbls + cols invoked
• dba_users: passwd expirn info data dicty
• multi-row o/p of subquery to 1-row result : grant select,insert,update on xtable to manager with grant option; → give manager ability to select,insert,updt xtable, and pass those optns on.
• select xcol from xtable where salary > any (select avg(salary) from xtable group by xid);
• group by clause cant have functions (eg. avg())
• complex view (virtual complex tbl) = a view canbe made as a join on 2/+ tbls
• a view cannot have an order by clause in select stmt, except inline views.

Error Messages

• "Ora-01555 Snapshop too old" : ↑ extent size of rollback segments / ↑ size of rollback segmt tablespace
• "Ora-01562 Insuffict space in rollback segment" ← no room in tablespace for rollback segmt to extend | maxextents has been reached
• "Ora-4031 Inability to allocate from the Shared Pool when the parameter is switched to ON" ← DB_CACHE_ADVICE has been attempted to directly set from OFF to ON without gog thro intermediate READY state

Business Intelligence

Business Objects - 14.7% BI market leader, 

BI Market (IDC):
1) OLAP (Online Analytical Processing)
2) EIS (Executive Information Systems)
3) End-user Query and Reporting: BO no.1 in West Europe
(26.3%)
4) Packaged Data Marts/Warehouses
5) Data Mining Tools


"Leading Analyst Research finds BO Number One BI Tools Vendor 
in Western Europe", PRWeb, 3/10/2002, 
http://www.prweb.com/releases/2002/10/prweb47367.htm

BO Install
• unable to login: host cannot be on domain, must be in myworkgroup
• mk BO entries in properties files

Ora vs sql server

• dbbest.com: tool for mig
• dual tbl notexist in sql server as sql server canhave select w/o from clause
• row level security notexist in sqls
• "lsnrctl start" → start listener man

References

• Oracle Tips and Tricks http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm
• Oracle Exam Questions http://www.certyourself.com
• otn: http://otn.oracle.com/documentation/index.html
• Rosengard: Oracle Tutorials: http://www.jmrosengard.com/Edu/DB/
• Toad (Tool for Oracle App Developers) http://www.toadsoft.com
• "Developing Personal Oracle7 for Windows 95 Applications" (*****) http://docs.rinet.ru:8083/Oru7na95/index.html
• slides on pl/sql http://www.npac.syr.edu/users/gcf/arldatabase/arloracle-data-accessfall97/fullhtml.html
• Dilip's RDBMS tutorial http://www.cs.unc.edu/Courses/wwwp-s98/members/barman/databaseLesson/index.html
• r937 sql links (*****) http://r937.com/links.cfm?links=sql
• dba interview questions: (*****) http://www.techinterviews.com/index.php?cat=12
• vyas' sql server links http://vyaskn.tripod.com/sqlserverres.htm
• Teach Yourself SQL in 21 Days, 2nd ed, full bk, (*****) http://members.tripod.com/er4ebus/sql/index.htm
• The Oracle PL/SQL CD Bookshelf http://www.ittepic.edu.mx/eBooks/computacion/80oreilly/books/oracle/ http://81.86.160.62/knowledge/
• Oreilly CD Bookshelf http://www.ittepic.edu.mx/eBooks/computacion/80oreilly/books/oracle/
• Oracle App Server fastest on Solaris, 13/11/2002 : http://www.serverwatch.com/news/print.php/1499521 Solaris 1476.81 bops (business operns/sec), Linux 189.63 bops, HP-UX 558.85 bops.
• Ora9i Db Admin Fundas I, Vol. 1 Student Guide, oracle educatn; Vol.2: essence extracted + used in this guide.
• ***** Oracle Current Release Documentation http://otn.oracle.com/documentation/index.html ; http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
• ****** ThinkForward Scipt Dump http://www.thinkforward.com

version 0.05: added ora9i db admin fundas I, vol.1+2 Student Guide by ora educn essence included
version 0.04: added all of oracle_9i_intro_sql_studguide_1_oracle
version 0.03: added 2 of deshabandhu bar's practicals
version 0.02: 18/4/2004 - added 137 deviq ocp q