Any notes/ tips to help me prepare for the exam?

Submitted by admin on Fri, 2005-11-11 23:29
Body: 

Some tips for the various exams:

1Z0-001 - Into to Oracle: SQL and PL/SQL

  • Precision: datatype number(p,s) vs number
  • An expr. with NULL always evaluates to NULL, except ||, which treats NULL as ''
  • Comparison to NULL is always FALSE
  • All functions, except COUNT(*), ignores NULL values
  • NULLS are displayed last with ORDERED BY in Oracle7
  • From Oracle 7.0.16 you can order on col alias or position.
  • Logical evaluation: TRUE takes precedence with OR, FALSE with AND, eg "TRUE OR NULL" is TRUE.
  • There are a lot of questions about GROUP BY/ HAVING...
  • Use WHERE to exclude rows, HAVING to exclude groups from a query
  • Know when a cartesian product will be formed. You need a min of N-1 conditions when you join N tables.
  • Know the difference between an equi (=), self (must use tab aliases), outer (+) and non-equi (IN, etc) join
  • You can only have a subquery in a FROM/ WHERE/ HAVING clause of a SELECT statement
  • Sub-queries and VIEWS cannot contain ORDER BY
  • A literal is any char/num/expr in a SELECT LIST that's not a col or col alias
  • MIN and MAX are the only functions that can operate on any datatype
  • PRIMARY KEY and UNIQUE constraints will implicitly create INDEXES
  • CONSTRAINT_TYPE col in USER_CONSTRAINTS shows only a "P" for primary keys
  • There are a lot of questions like: "Which line will return an error?". Look for names starting with numbers, invalid constraints, etc.
  • Cursors, how to use %type and %rowtype
  • Views are stored as select statements in the data dictionary
  • Questions on Sequence Generator - Creation & Pseudocolumns (currval & nextval)
  • Data Dictionary Views - to get constraints attached to a column & columns that make a constraint
  • Exception Handlers - User defined are raised only in the execution section. Pre-defined can be raised in declaration/execution section
  • "For Loop Cursors" - Cursor Open/Fetch/Close is handled implicitly.
  • Three types of Data Dictionary Views: USER_views, ALL_views, DBA_views
  • If a table alias is used, you must used it in the select clause, so "SELECT deptno.deptno FROM dept a" is wrong!!!
  • Check user_cons_columns for constraint columns

1Z0-003 - Oracle7 Database Administration

  • INSTANCE=SGA + background processed (not server processes); INSTANCE will open a DATABASE on disk
  • SGA=System Global Area/ PGA=Program Global Area
  • Oracle server creates PGA as writeable and non-shared/ SGA as shared
  • Know what background processes are doing in detail, DBWR, SMON, PMON, LGWR, etc.
  • SMON coalesces adjacent free extents into larger extent
  • Server processes executes SQL; Main phases are PARSE (setup parse tree=most effective search path), EXECUTE (apply parse tree) and FETCH (get rows)
  • Server configurations: single-task, two-task (dedicated server), MTS (shared server)
  • Shared Server: SQL*Net2/8 required, one requestQ for system, responseQ per dispatcher
  • Dedicated Server: use for batch, connect SYS AS SYSDBA, server manager
  • With shared server session data and cursor state moves to SGA, stack space remains in PGA
  • No quota means segments cannot grow or be created, only object owner needs quotas.
  • By default a user has no access to any TS
  • If quota=0 NO ACCESS; -1=UNLIMITED (see view sys.dba_ts_quotas)
  • Clusters pre-allocate space/ 2 types: index clusters (stored together for faster join performance) and hush clusters
  • I've got about 3 questions on index clusters and none about hush clusters
  • SysPrivs and roles use WITH ADMIN OPTION, use WITH GRANT OPTION for object privs.
  • Revoke a GRANT...WITH ADMIN OPTION is not hierarchical and will not cascade, unlike WITH GRANT OPTION
  • Know how to calculate the size of the N nd extend. INITIAL, NEXT, NEXT := NEXT*(1+PCTINCREASE/100)...
  • PCTUSED is not valid for indexes; OPTIMAL is only valid for rollsegs; PCTINCREASE for rollsegs is 0 (cannot be set)
  • There are at least 3 questions about what will happen when PCTFREE/PCTUSED are increased/decreased
  • MAXTRANS applies to all blocks; INITRANS to new blocks only (23 bytes per freelist entry)
  • Run orapwd utility before setting REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE|SHARED; Grant OSDBA/OSOPER to users; Check V$PWFILE_USERS
  • Form V7.2: Set ORA_NLS=?/ocommon/nls/admin/data; SELECT * FROM V$NLS_VALID_VALUES; SELECT * FROM V$NLS_PARAMETERS
  • DB_NAME is the only mandatory parm; DB_BLOCK_SIZE cannot change after db creation
  • Defaults: DB_BLOCK_SIZE=2K/ DB_BLOCK_BUFFERS=60/ SHARED_POOL_SIZE=3,500,000 bytes
  • Must have at least one control file and 2 log groups
  • Read consistency: readers do not block writers and writers do not block readers
  • AUDIT_TRAIL=DB uses SYS.AUD$ table, view via DBA_AUDIT_TRAIL
  • Use AUDIT SELECT for sequences
  • You need to set RESOURCE_LIMIT=TRUE for PROFILES (resource checking)
  • One can DROP from a READ ONLY ts

1Z0-005 - Oracle7 Performance Tuning

  • Tuning process is ITERATIVE and PROGRESSIVE
  • Many inserts/updates with OLTP, many table scans with DSS
  • By default the SYSTEM tablespace will be used for sorts, CHANGE IT!!!
  • SEP=Sort Extent Pool is in SGA, Sorts are done in user memory except with MTS it's in UGA
  • Use DBMS_APPLICATION_INFO to register and track modules/ created with dbmsutil.sql/ See V$SQLAREA and V$SESSION
  • OPTIMIZER_MODE is for instance, OPTIMIZER_GOAL is for session
  • RULE is based on rank/ COST is based on lowest relative cost/ COOSE goes to COST if stats exists
  • TKPROF: Logical reads = QUERY (logical reads in consistent mode) + CURRENT (logical reads in current mode)
  • V$SYSSTAT: Logical reads = consistent gets + db block gets
  • Tune table scans: DB_FILE_MULTIBLOCK_READ_COUNT=/ CACHE_SIZE_THRESHOLD=
  • Dict. row cache: V$ROWCACHE - getmisses/gets must be < 15%
  • Library cache: V$LIBRARYCACHE - pins/reloads <= 1% and GETHITRATIO > 0.90
  • DB Block cache: V$BH/V$CACHE - logical_reads/ (logical_reads + physical reads) must be > 80% (90% with RAW)
  • V$CACHE is useful for Oracle Parallel Server
  • Set DB_BLOCK_LRU_LATCHES if you have misses in V$LATCH
  • If Redo log space requests <>0, increase LOG_BUFFER= with 5%
  • Latches can be WILLING-TO-WAIT (eg redo allocation) or NOWAIT (eg redo copy)
  • Oracle maintains all locks as ENQUEUES/ Deadlocks are resolved at statement level
  • Lock modes: RX=DML/ TM=table/ TX=transaction and rollseg/ RS=SELECT... FOR UPDATE/ SRX=DML and no SELECT FOR UPDATE/ UL=user lock/ S=prevents any DML
  • Index foreign key column on child table to prevent SHARE lock during DML on parent!!!
  • Use SID and Serial# in V$SESSION to kill a session
  • Impact of reducing DB_BLOCK_BUFFERS: Set DB_BLOCK_LRU_STATISTICS= and query X$KCBCBH
  • Impact if increasing DB_BLOCK_BUFFERS: Set DB_BLOCK_EXTENDED_LRU_STATISTICS= and query X$KCBRBH
  • To keep sorts in memory, increase SORT_AREA_SIZE
  • From 7.2: SORT_DIRECT_WRITES=TRUE bypasses the buffer cache/ use its own memory buffers
  • SORT_WRITE_BUFFERS (16k - 32k) * SORT_WRITE_BUFFER_SIZE (2-8) + SORT_AREA_SIZE
  • Any MTS parameter may be wrong, but if MTS_SERVICE=SID, you will get a dedicated server connection if shared cannot be established

1Z0-004 - Backup and recovery

  • Backups types: Image copies (operating system backups) or logical backups (imp/ exp)
  • Files to backup: select * from v$controlfile/v$datafile/v$logfile, init.ora and password file
  • NOARCHIVELOG mode is default, you need to switch to ARCHIVELOG to do online-backups and point-in-time recovery.
  • You can only switch log mode in MOUNT-state (STARTUP MOUNT). You need ALTER DATABASE privs to do this.
  • LOG_ARCHIVE_START=TRUE will start ARCH process
  • An on-line DB is backed up tablespace by tablespace. Use the ALTER TABLESPACE BEGIN BACKUP command (Not READ ONLY tablespaces!!!)
  • With 7.2, if your DB crashes while in backup mode, you can take the FILES out of backup mode without recovering
  • Exp/imp DIRECT=YES (7.3 feature) uses RECORDLENGTH= and not BUFFER=. Data written directly to TTC (Two task common buffer) and bypasses BUFFER CACHE
  • Use exp CONSISTENT=YES if you need to preserve integrity between tables.
  • INCTYPE=INCREMENTAL - objects changed since last export of any type/ INCTYPE=CUMULATIVE - obects changed since last cumulative export
  • You cannot roll forward after a logical recovery (ie apply redo log files after an import).
  • After DB structure changes (know when - 2-3 questions) do an ALTER DATABASE BACKUP CONTROLFILE...
  • Complete Recovery: DATABASE/ TABLESPACE/ DATAFILE based
  • Incomplete Recovery: TIME (YYYY-MM-DD:HH24:MI:SS)/ CANCEL/ CHANGE based
  • Incomplete recovery not possable on SYSTEM ts or ts with active rollsegs.
  • Be careful not to confuse CANCEL and CHANGED based recovery.
  • All datafiles must be restored for an incomplete recovery. Incomplete recovery is to a point in the past.
  • After incomplete recovery, do an OPEN RESETLOGS and take a backup.
  • With V7.3 deferred transaction recovery, rollbacks are done after DB open.
  • If LOG_BLOCK_CHECKSUM=TRUE and checksum fails: ALTER DATABASE CLEAR LOGFILE...
  • Use DBVERIFY (dbv file=...) to check offline datafiles for structural damage (be careful, the training guide incorrectly states that both On-line and Off-line files can be checked)

1Z0-010 - Oracle7.3 to Oracle8 Upgrade Exam

  • RMAN cannot write image copies to tape.
  • Quite a lot of questions on RMAN (Recovery Manager) and Partitioning. Be clear on syntax (both CREATE/ALTER PARTITION as well
    as RMAN script syntax and invocation), restrictions in partitioning (no
    LONG/BLOB in Partitioned tables?).
  • Quite a few questions on Advanced Queueing (that I hadn't expected - even one/two on the syntax used in AQ!)
  • I was expecting a lot of questions on the Objects option - there were just 2-3 and they were very basic ones!
  • Some questions on the new ROWID format
  • Some questions on LOB usage and restrictions
  • Some questions (basic) on Net8, Connection pooling, etc.

1Z0-111 - Developer/2000 Forms 4.5 I

  • A lot of questions about triggers (scope, ES, type, etc)
  • Know how to create the different types of items in a Form module
  • Get to know the categories of properties and what is in each for the different types of items/objects in forms
  • Understand the difference between modal and modeless windows
  • Understand the difference between content canvas and stacked canvas
  • Know the different built-ins for referencing other forms
  • Understand how to reference internal ids
  • Understand when query triggers fire and how often
  • Using Alerts, suppressing messages, etc
  • Mouse system variables
  • System variables in general
  • Understand navigation
  • Know how to write flexible code
  • Understand how to share objects and code
  • Know how to customize the menu and the types of items/objects which can be included in a menu

1Z0-113: Developer/2000 Reports 2.5

  • There are a lot of questions about matrixes, packages, and function/placeholder/summary columns
  • Understand repeating frames and what frames are created by each layout
  • Know the difference between Nested Matrix and Matrix break reports
  • Know how to develop and test reports for both Character and Bitmap environments
  • Know the tools options menu
  • Be familiar with print type/object
  • Read questions VERY carefully. Some are quite lengthy. Use the paper provided in the exam room to diagram the data models to help clarify the
    question

»