Oracle Interview Questions - Part I

Skillbuilders

Keyword/Topics for an Oracle Interview :  

• Oracle 9i, 10g, 11g Architecture (Oracle Internals - Part of SGA, PGA)
• General :
• ORACLE Architecture by Lee
• Oracle Process Architecture
• ORACLE Architecture and Terminology
• Oracle Concepts - Physical Oracle Architecture
• Oracle-database-architecture-tutorial
• 9i : 
• Oracle Database 9i Basic Architecture
• Syntax Architecture
• Basics of Oracle Database Architecture
• Introduction to Oracle Server
• Optimal Flexible Architecture
• 10g :
• Oracle 10g Memory Architecture
• Logical Design
• Architecture Diagram
• 10g Architecture Diagram
• OWB
• Oracle 10g Architecture by Khalid
• Architecture 10g
• Oracle 10g Architecture with diagram
• Memory Architecture
• Introduction to Oracle Database
• 11g : 
• 11g Architecture
• 11g Architecture Simplified
• Oracle 11g Memory Architecture
• 12c
• 12c Architecture
• RAC :
• RAC Architecture Diagram
• Summary
• Oracle Server = Oracle Instance + Oracle Database
• Oracle Instance = Background Processes and Memory Structures
• Oracle Database = Set of Files
• Oracle Memory Structure
• SGA (System Global Area) - Part of Oracle Memory shared by all processes in an instance
• Fixed SGA
• Variable SGA
• DB Buffer Cache = db_block_buffers
• Shared Pool = shared_pool_size
• Library Cache comprising of (Shared SQL area for sql and execution plan + UGA)
• Reserved Pool
• DD Cache
• Result Cache (11g)
• Enqueues and Latches
• Redo Log Buffer = log_buffer
• Java Pool
• Large Pool (optional) = large_pool_size
• KEEP Pool
• Recycle Buffer Pool
• Streams Pool (11g)
• Other structures like lock and latch management
• Links
• The Role of the buffer cache
• Oracle's buffer cache
• PGA - Program Global Area - Part of Oracle Memory private to each process.
• Session Memory
• Private SQL Area
• Bind Variables
• Query Execution State info
• SQL Work Areas
• Sorting Data
• Hash Tables for hash join
• Processes
• Server Processes
• Background Processes
• Server (Snnn)
• Dispatcher (Dnnn)
• Queue Monitor (QMNn)
• System Monitor (SMON)
• Process Monitor (PMON)
• Database Writer (DBW0 or DBWn)
• Log Writer (LGWR)
• Checkpoint (CKPT)
• Recoverer (RECO)
• Lock Manager Server (LMS) – Real Application Clusters only
• Archiver (ARCn)
• Files
• Data files
• Undo files
• Redo files
• Control files
• Parameter file
• ora files
• Alert log
• Trace files
• Archive files
• Backup dump files 
• SMON - Server Monitor manages the server in case of failure, performs instance recovery.
• PMON - Process Monitor helps to release resources held by a failed process or help in process recovery.
• SGA - System Global Area  is part of oracle memory that stores data and control info common for all oracle user processes.
• PGA - Program Global Area is part of oracle memory that stores data and control info for a single oracle user process.
• UGA - User Global Area
• PFILE - Oracle param file specifies path of the control files and how the memory is allocated and other characteristics of the Oracle Instance.
• Redo Log File - Records chgs made to the db
• CKPT - puts a timestamp on all control and data files when a checkpoint occurs
• A checkpoint switch occurs when DBWR writes all modified buffers in the SGA buffer cache to the datafiles.
• A Tablespace in Oracle comprises of one or more data files.
• PCTFREE - a param which specifies how much space shld be left free in a block for future updates.
• DBWn - writes the modified blocks from db buffer cache in the SGA to the db files
• LGWR is a sys log writer that records the system activities and writes nto a log file.
• A Chained Row is a row that is stored across multiple database blocks as it is too large to fit into a single database block
• HWM - High Water Mark
• http://gerardnico.com/wiki/database/oracle/high_water_mark
  The database doesn't know what is on a block unless and until it reads the block.
  You can think at the high-water mark (HWM) as the rightmost block that ever contained data in a segment.
• The High water mark is the boundary between used and unused space in a segment.
• High Water Mark = TOTAL BLOCKS−UNUSED BLOCKS−1 
• OR High Water Mark = USER_SEGMENTS.BLOCKS−USER_TABLES.EMPTY_BLOCKS−1
• Truncated, (TRUNCATE will reset the HWM of a table back to 'zero')

• SCN - System Change Number                           http://gerardnico.com/wiki/database/oracle/scn
• A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction.
  Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.
  SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time with respect to the database. Several events may share the same SCN, which means that they occurred at the same time with respect to the database.
  A scn can be created with the savepoint command ?

• Transaction

  Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit.

• Process

  Oracle Database increments SCNs in the system global area (SGA). When a transaction modifies data, the database writes a new SCN to the undo data segment assigned to the transaction. The log writer process then writes the commit record of the transaction immediately to the online redo log. The commit record has the unique SCN of the transaction. Oracle Database also uses SCNs as part of its instance recovery and media recovery mechanisms.
• ORA_ROWSCN
• For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.

• SELECT MAX(ORA_ROWSCN), SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM SCOTT.EMP;

• 9i vs 10g vs 11g
• How does Oracle execute query internally
• All SQL statements have to go through the following stages of execution:
• 1) PARSE: Every SQL statement has to be parsed which includes checking the syntax, validating, ensuring that all references to objects are correct and ensuring that relevant privileges to those object exist.
• 2) BIND: After parsing, the oracle server knows the meaning of the oracle statement but still may not have enough info(values for variables) to execute the statement. The process of obtaining these value is called as bind values.
• 3) EXECUTE: After binding, the Oracle server executes the statement.
• 4) FETCH: In the fetch stage, rows are selected and ordered and each successive row retrieves another row of the result until the last row has been fetched. This stage is only for certain DML statements like SELECT.
• When we pass a SELECT query it executes under some phases. That includes
• 1) User submits a request from the client or request is recieved from an app via jdbc/odbc
• 2) The request goes from the client to the listener which identifies the db service and passes it to the server.
• 2) Checking by Server Process - Which type of Query is this - SQL or PL/SQL.
• 3) If SQL- server process checks if it's already available under library cache under shared pool and acquires a handle, if not then it places the code in SQL area available after parsing if space is available else apply LRU to create space.
• 4) If PL/SQL - server process puts the SQL code in SQL area after above step and checks if PL code is available in PL area available under library cache area under shared pool, if not then it places the code in PL area available after parsing if space is available else apply LRU to create space.
• 5) Now Parsing (Syntax, Privileges on user passing the query checks)
• 6) Check if execution plan is already available or not in shared sql area by creating a hash key and doing a lookup.
• 7) If not then it creates an execution plan on the basis of available statistics in the database dictionary cache. Checks if space is available in the shared pool to place the execution plan. If space is available it places the plan on the shared pool, waits for a latch and uses the same. If the Shared pool is full it will apply LRU algo before putting the sql on the shared sql area. If sql and plan are already available, uses available execution plan directly.
• 8) Checks if the data is available in the result cache, if available will use the same.
• 9) Else checks if the data blocks contains required data are available in buffer cache or not
• 10) If yes, server process pick the data from BC and return to the user. If not server process reads the data blocks from data files and put the data blocks into buffer cache and return to the user.
• The order of Query Execution is following (Right to Left, Bottom Up)
• 1)From Clause
• 2)Where Clause
• 3)Group By Clause
• 4)Having Clause
• 5)Select
• 6)Order By Clause

• Sum of the sizes of all the data files specified in the create tablespace determines the init size of a tablespace
• INIT size of Diff between 9i, 10g, 11g
• Optimistic and Pessimistic Locking
• Data Concurrency and Consistency
• Session (login) vs Transaction (commit) vs Unit of Work (begin end block)
• Use the BEGIN_DISCRETE_TRANSACTION procedure to improve the performance of short, non-distributed transactions.
• Use the SET TRANSACTION command with the USE ROLLBACK SEGMENT parameter to explicitly assign a transaction to an appropriate rollback segment. This can eliminate the need to dynamically allocate additional extents, which can reduce overall system performance.
• Use the SET TRANSACTION command with the ISOLATION LEVEL set to SERIALIZABLE to get ANSI/ISO serializable transactions.
• Establish standards for writing SQL statements so that you can take advantage of shared SQL areas. Oracle recognizes identical SQL statements and allows them to share memory areas. This reduces memory usage on the database server and increases system throughput.
• Use the ANALYZE command to collect statistics that can be used by Oracle to implement a cost-based approach to SQL statement optimization. You can supply additional "hints" to the optimizer as needed.
• Call the DBMS_APPLICATION_INFO.SET_ACTION procedure before beginning a transaction to register and name a transaction for later use when measuring performance across an application. You should specify what type of activity a transaction performs so that the system tuners can later see which transactions are taking up the most system resources.
• Increase user productivity and query efficiency by including user-written PL/SQL functions in SQL expressions as described in "Calling Stored Functions from SQL Expressions".
• Create explicit cursors when writing a PL/SQL application.
• When writing precompiler programs, increasing the number of cursors using MAX_OPEN_CURSORS can often reduce the frequency of parsing and improve performance.
• To commit a transaction, use the COMMIT command. The following two statements are equivalent and commit the current transaction:

  COMMIT WORK;
  COMMIT;
  
  

  The COMMIT command lets you include the COMMENT parameter along with a comment (less than 50 characters) that provides information about the transaction being committed. This option is useful for including information about the origin of the transaction when you commit distributed transactions:

  COMMIT COMMENT 'Dallas/Accts_pay/Trans_type 10B';

• 
  A read-only transaction is started with a SET TRANSACTION statement that includes the READ ONLY option. For example:
  SET TRANSACTION READ ONLY;
  

• 
  A transaction explicitly acquires the specified table locks when a LOCK TABLE statement is executed. A LOCK TABLE statement manually overrides default locking. When a LOCK TABLE statement is issued on a view, the underlying base tables are locked. The following statement acquires exclusive table locks for the EMP_TAB and DEPT_TAB tables on behalf of the containing transaction:
  LOCK TABLE Emp_tab, Dept_tab
      IN EXCLUSIVE MODE NOWAIT;
  

• LOCK TABLE Emp_tab IN ROW SHARE MODE;
  LOCK TABLE Emp_tab IN ROW EXCLUSIVE MODE;
  

• 
  Table 7-2 Nondefault Locking Behavior
  
  Statement
  Case 1: row
  Case 1: table
  Case 2: row
  Case 2: table
  Case 3: row
  Case 3: table
  
  
  
  
  
  
  SELECT
  
  
  
  
  
  -
  
  
  
  
  
  -
  
  
  
  
  
  -
  
  
  
  
  
  S
  
  
  
  
  
  -
  
  
  
  
  
  S
  
  
  
  
  
  
  INSERT
  
  
  
  
  
  X
  
  
  
  
  
  SRX
  
  
  
  
  
  X
  
  
  
  
  
  RX
  
  
  
  
  
  X
  
  
  
  
  
  SRX
  
  
  
  
  
  
  UPDATE
  
  
  
  
  
  X
  
  
  
  
  
  SRX
  
  
  
  
  
  X
  
  
  
  
  
  SRX
  
  
  
  
  
  X
  
  
  
  
  
  SRX
  
  
  
  
  
  
  DELETE
  
  
  
  
  
  X
  
  
  
  
  
  SRX
  
  
  
  
  
  X
  
  
  
  
  
  SRX
  
  
  
  
  
  X
  
  
  
  
  
  SRX
  
  
  
  
  
  
  SELECT...FOR UPDATE
  
  
  
  
  
  X
  
  
  
  
  
  RS
  
  
  
  
  
  X
  
  
  
  
  
  S
  
  
  
  
  
  X
  
  
  
  
  
  S
  
  
  
  
  
  
  LOCK TABLE... IN..
  
  
  
  
  
  -
  
  
  
  
  
  -
  
  
  
  
  
  -
  
  
  
  
  
  -
  
  
  
  
  
  -
  
  
  
  
  
  -
  
  
  
  
  
  
  ROW SHARE MODE
  
  
  
  
  
  -
  
  
  
  
  
  RS
  
  
  
  
  
  -
  
  
  
  
  
  RS
  
  
  
  
  
  -
  
  
  
  
  
  RS
  
  
  
  
  
  
  ROW EXCLUSIVE MODE
  
  
  
  
  
  -
  
  
  
  
  
  RX
  
  
  
  
  
  -
  
  
  
  
  
  RX
  
  
  
  
  
  -
  
  
  
  
  
  RX
  
  
  
  
  
  
  SHARE MODE
  
  
  
  
  
  -
  
  
  
  
  
  S
  
  
  
  
  
  -
  
  
  
  
  
  S
  
  
  
  
  
  -
  
  
  
  
  
  S
  
  
  
  
  
  
  SHARE ROW EXCLUSIVE MODE
  
  
  
  
  
  -
  
  
  
  
  
  SRX
  
  
  
  
  
  -
  
  
  
  
  
  SRX
  
  
  
  
  
  -
  
  
  
  
  
  SRX
  
  
  
  
  
  
  EXCLUSIVE MODE
  
  
  
  
  
  -
  
  
  
  
  
  X
  
  
  
  
  
  -
  
  
  
  
  
  X
  
  
  
  
  
  -
  
  
  
  
  
  X
  
  
  
  
  
  
  DDL statements
  
  
  
  
  
  -
  
  
  
  
  
  X
  
  
  
  
  
  -
  
  
  
  
  
  X
  
  
  
  
  
  -
  
  
  
  
  
  X
  
  
  

• 
  Table 7-3 Summary of ANSI Isolation Levels
  
  Isolation Level
  Dirty Read (1)
  Non-Repeatable Read (2)
  Phantom Read (3)
  
  
  
  
  
  
  READ UNCOMMITTED
  
  
  
  
  
  Possible
  
  
  
  
  
  Possible
  
  
  
  
  
  Possible
  
  
  
  
  
  
  READ COMMITTED
  
  
  
  
  
  Not possible
  
  
  
  
  
  Possible
  
  
  
  
  
  Possible
  
  
  
  
  
  
  REPEATABLE READ
  
  
  
  
  
  Not possible
  
  
  
  
  
  Not possible
  
  
  
  
  
  Possible
  
  
  
  
  
  
  SERIALIZABLE
  
  
  
  
  
  Not possible
  
  
  
  
  
  Not possible
  
  
  
  
  
  Not possible
  
  
  
  
  
  
  Notes:
  
  
  
  
  
  (1) A transaction can read uncommitted data changed by another transaction.
  
  
  
  
  
  
  (2) A transaction rereads data committed by another transaction and sees the new data.
  
  
  
  
  
  
  (3) A transaction can execute a query again, and discover new rows inserted by another committed transaction.
  
  
  
  The behavior of Oracle with respect to these isolation levels is summarized below:
  
  Isolation Level
  Description
  
  
  
  
  
  
  READ UNCOMMITTED
  
  
  
  
  
  Oracle never permits "dirty reads." Although some other database products use this undesirable technique to improve thoughput, it is not required for high throughput with Oracle.
  
  
  
  
  
  
  READ COMMITTED
  
  
  
  
  
  Oracle meets the READ COMMITTED isolation standard. This is the default mode for all Oracle applications. Because an Oracle query only sees data that was committed at the beginning of the query (the snapshot time), Oracle actually offers more consistency than is required by the ANSI/ISO SQL92 standards for READ COMMITTED isolation.
  
  
  
  
  
  
  REPEATABLE READ
  
  
  
  
  
  Oracle does not normally support this isolation level, except as provided by SERIALIZABLE.
  
  
  
  
  
  
  SERIALIZABLE
  
  
  
  
  
  You can set this isolation level using the SET TRANSACTION command or the ALTER SESSION command.
  
  
  
  Table 7-4  Read Committed Versus Serializable Transaction
  
  
  Operation
  Read Committed
  Serializable
  
  
  
  
  
  
  Dirty write
  
  
  
  
  
  Not Possible
  
  
  
  
  
  Not Possible
  
  
  
  
  
  
  Dirty read
  
  
  
  
  
  Not Possible
  
  
  
  
  
  Not Possible
  
  
  
  
  
  
  Non-repeatable read
  
  
  
  
  
  Possible
  
  
  
  
  
  Not Possible
  
  
  
  
  
  
  Phantoms
  
  
  
  
  
  Possible
  
  
  
  
  
  Not Possible
  
  
  
  
  
  
  Compliant with ANSI/ISO SQL 92
  
  
  
  
  
  Yes
  
  
  
  
  
  Yes
  
  
  
  
  
  
  Read snapshot time
  
  
  
  
  
  Statement
  
  
  
  
  
  Transaction
  
  
  
  
  
  
  Transaction set consistency
  
  
  
  
  
  Statement level
  
  
  
  
  
  Transaction level
  
  
  
  
  
  
  Row-level locking
  
  
  
  
  
  Yes
  
  
  
  
  
  Yes
  
  
  
  
  
  
  Readers block writers
  
  
  
  
  
  No
  
  
  
  
  
  No
  
  
  
  
  
  
  Writers block readers
  
  
  
  
  
  No
  
  
  
  
  
  No
  
  
  
  
  
  
  Different-row writers block writers
  
  
  
  
  
  No
  
  
  
  
  
  No
  
  
  
  
  
  
  Same-row writers block writers
  
  
  
  
  
  Yes
  
  
  
  
  
  Yes
  
  
  
  
  
  
  Waits for blocking transaction
  
  
  
  
  
  Yes
  
  
  
  
  
  Yes
  
  
  
  
  
  
  Subject to "can't serialize access" error
  
  
  
  
  
  No
  
  
  
  
  
  Yes
  
  
  
  
  
  
  Error after blocking transaction aborts
  
  
  
  
  
  No
  
  
  
  
  
  No
  
  
  
  
  
  
  Error after blocking transaction commits
  
  
  
  
  
  No
  
  
  
  
  
  Yes
  
  
  
  

• Locking and Blocks
• 2 Phase Commit
• Distributed Transaction is a transaction that includes one or more statements that update the db.
• Drop in 10g vs 9i
• Data on 2 or more distinct nodes of a distributed db.
• Flash back syntax and recycle bin
• Yes, Recycle table gets records after TRUNCATE.
• Difference between DB Buffer Cache and Result Cache in 11g.
• The Server Result Cache holds result sets and not data blocks
• Datawarehouse Result Cache
• A Useful Oracle 11g New Feature
• Server Result Cache
• Query result cache in Oracle 11g
• There are three new result caching features in 11g:
• Query Result Cache;
• PL/SQL function result cache; and
• Client OCI result cache.
• Query Result Cache : As its name suggests, the query result cache is used to store the results of SQL queries for re-use in subsequent executions. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset in the first place (for example, sorting/aggregation, physical I/O, joins etc). The cache results themselves are available across the instance (i.e. for use by sessions other than the one that first executed the query) and are maintained by Oracle in a dedicated area of memory. Unlike our homegrown solutions using associative arrays or global temporary tables, the query result cache is completely transparent to our applications. It is also maintained for consistency automatically, unlike our own caching programs.
• A brief explanation of each of these parameters is as follows.
• result_cache_mode: the result cache can be enabled in three ways: via hint, alter session or alter system. Default is MANUAL which means that we need to explicitly request caching via the RESULT_CACHE hint;
• result_cache_max_size: this is the size of the result cache in bytes. The cache is allocated directly from the shared pool but is maintained separately (for example, flushing the shared pool will not flush the result cache);
• result_cache_max_result: this specifies the highest percentage of the cache that is able to be used by a single resultset (default 5%); and
• result_cache_remote_expiration: this specifies the number of minutes for which a resultset based on a remote object can remain valid. The default is 0 which means that resultsets defendant on remote objects will not be cached.

Oracle 11g New Features

• Oracle 11g new features
• SQL New Features
• PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1
• New "PIVOT" SQL clause - The new "pivot" SQL clause will allow quick rollup, similar to an MS-Excel pivot table, where you can display multiple rows on one column with SQL.  MS SQL Server 2005 also introduced a pivot clause. Laurent Schneider notes that the new SQL "pivot" syntax is great for converting rows-to-columns and columns-to-rows.
• Dynamic SQL - DBMS_SQL is here to stay. It's faster and is being enhanced. DBMS_SQL and NDS can now accept CLOBs (no more 32K limit on NDS). A ref cursor can become a DBMS_SQL cursor and vice versa. DBMS_SQL now supports user-defined types and bulk operations.          Source : Lewis Cunningham
• The /*+result_cache*/ SQL hint - This suggests that the result data will be cached in the data buffers, and not the intermediate data blocks that were accessed to obtain the query results.  You can cache SQL and PL/SQL results for super-fast subsequent retrieval.  The "result cache" ties into the "scalable execution" concept.  There are three areas of the result cache:
• The SQL query result cache - This is an area of SGA memory for storing query results
• The PL/SQL function result cache - This result cache can store the results from a PL/SQL function call. 
• The OCI client result cache - This cache retains results from OCI calls, both for SQL queries or PL/SQL functions.
• PL/SQL New Features
• PL/SQL Enhancements
• PL/SQL "CONTINUE" keyword - This will allow a "C-Like" continue in a loop, skipping an iteration to bypass any "else" Boolean conditions.  A nasty PL/SQL GOTO statement is no longer required to exit a Boolean within a loop.  Oracle professional Jurgen Kemmelings has an excellent PL/SQL example of the PL/SQL continue clause in-action:
• begin
    for i in 1..3
    loop
      dbms_output.put_line(’i=’||to_char(i));
      if ( i = 2 )
      then
        continue;
      end if;
      dbms_output.put_line(’Only if i is not equal to 2′);
    end loop;
  end;
• Native PL/SQL use of Seq.Nextval in a PL/SQL program.
• Disabled state for PL/SQL - Another 11g new feature is a "disabled" state for PL/SQL (as opposed to "enabled" and "invalid" in dba_objects).
• Improved PL/SQL stored procedure invalidation mechanism - A new 11g features will be fine grained dependency tracking, reducing the number of objects which become invalid as a result of DDL.
• Enhanced PL/SQL warnings - The 11g PL/SQL compiler will issue a warning for a "when others" with no raise.
• Stored Procedure named notation - Named notation is now supported when calling a stored procedure from SQL.
• PL/SQL can use CLOBs to get around 32KB limitation of SQL character strings.
• Invisible Indexes in Oracle Database 11g Release 1 used to test dropping trigger
• Partitioning Enhancements in Oracle Database 11g Release 1
• Extended Composite Partitioning
• Interval Partitioning
• System Partitioning
• Reference Partitioning
• Virtual Column-Based Partitioning
• Single Partition Transportable for Oracle Data Pump
• Partition Advisor
• Enhanced Statistics Collection for Partitioned Objects
• Query Result Cache in Oracle Database 11g Release 1
• Result Cache
• PL/SQL Function cache
• OCI Cache
• Cross-Session PL/SQL Function Result Cache in Oracle Database 11g Release 1
• DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1
• Enhanced Finer Grained Dependency Management in Oracle Database 11g Release 1
• PLSTIMER identifies hotspots and performance tuning opportunities in PL/SQL.
• Conditional Compilation
• Commit in Trigger
• Easy PL/SQL compiling - Native Compilation no longer requires a C compiler to compile your PL/SQL. Your code goes directly to a shared library. Source : Lewis Cunningham
• Scalable PL/SQL - The next scalable execution feature is automatic creation of "native" PL/SQL (and Java code), with just one parameter for each type with an "on/off" value. This apparently provides a 100% performance boost for pure PL/SQL code, and a 10%-30% performance boost for code containing SQL.
• Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
• Online Table Redefinition Enhancements in Oracle Database 11g Release 1
• Read-Only Tables in Oracle Database 11g Release 1
• SQL Performance Analyzer in Oracle Database 11g Release 1
• SQL Access Advisor in Oracle Database 11g Release 1
• Data Pump Enhancements in Oracle Database 11g Release 1
• PL/SQL Hierarchical Profiler in Oracle Database 11g Release 1
• PL/SQL New Features and Enhancements in Oracle Database 11g Release 1
• Trigger Enhancements in Oracle Database 11g Release 1
• FOLLOWS keyword to control sequence of execution for same timing triggers
• COMPOUND TRIGGERS helps define single trigger for Statement level and row level trigger
• Now ENABLE and DISABLE triggers during creation 
• Real-Time SQL Monitoring using DBMS_SQLTUNE (REPORT_SQL_MONITOR, REPORT_SQL_MONITOR_LIST and REPORT_SQL_DETAIL)
• DBMS_PARALLEL_EXECUTE
• APPEND_VALUES Hint in Oracle Database 11g Release 2

• FORALL i IN l_tab.first .. l_tab.last
      INSERT /*+ APPEND_VALUES */ INTO forall_test VALUES l_tab(i);

• Oracle 11g R2 new parallel query management enhancements
• Oracle 11g fully automated SQL tuning tips
• Oracle 11g SQL performance analyzer concepts
• 11g SQL Plan baseline tips
• Lillian Hobbs speaks on Oracle 11g SQL tuning transformations
• Inside Oracle 11g Database replay
• Inside the 11g SQL Performance Analyzer (SPA)
• Oracle 11g function-based virtual columns
• Virtual Columns in 11g
• Oracle Omotion tips
• Omotion is an online migration utility (new in 11gR2) that is used with RAC One Node to facilitate super fast instance relocation is cases of server failure.
• Oracle column level compression
• Oracle optimizer extended statistics tips
• Oracle Instance Caging tips
• Recursive subquery factoring using the SQL WITH clause

• Oracle 12c New Features
• Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)
• SQL*Loader Enhancements in Oracle Database 12c Release 1 (12.1)
• PL/SQL New Features and Enhancements in Oracle Database 12c Release 1 (12.1)
• TRUNCATE TABLE ... CASCADE in Oracle Database 12c Release 1 (12.1)
• UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram in Oracle Database 12c Release 1 (12.1)
• WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)
• Oracle OpenWorld 2012 : Day 5
• Oracle Database 12c New Features – Part I
• Oracle Database 12c New Features – Part 2
• • Smart Flash Cache Oracle Database 12C release 1 (12.1)
  • Extended Data Type Oracle Database 12C release 1 (12.1)
  • Default Values for Columns Based on Sequences Oracle Database 12C release 1 (12.1)
  • DEFAULT Values for Columns on Explicit NULL Insertion Oracle Database 12C release 1 (12.1)
  • IDENTITY Columns Oracle Database 12C release 1 (12.1)
  • SQL CROSS APPLY, OUTER APPLY and LATERAL Oracle Database 12C release 1 (12.1)
  • Multiple Indexes on Same Set of Columns Oracle Database 12C release 1 (12.1)
  • Cascade Functionality for TRUNCATE and EXCHANGE Partition Oracle Database 12C release 1 (12.1)
  • Interval Reference Partitioning Oracle Database 12C release 1 (12.1)
  • Advanced Index Compression Oracle Database 12C release 1 (12.1)
• Codd 12 Rules
• Codd's 12 rules
• Codds Rules
• Coddsrules
• Coddrules
• Codds rules 
• Normalization
• 1NF - A table is in first normal form if it contains no repeating groups
• 2NF - A relation is in second normal form (2NF) if and only if it is in 1NF and every nonkey attribute is fully dependent on the primary key. That is No non-prime attribute in the table is functionally dependent on a proper subset of any candidate key. All non-prime attributes should depend on the entire primary key.
• 3NF - Every non-prime attribute is non-transitively dependent on every candidate key in the table.
• BCNF / 3.5 NF - Every determinant must be a candidate key.
• 4NF - A relation is in 4NF if it has no multi-valued dependencies.
• Links
• Database Normalization
• NF3
• Normalization
• Normalization
• Data Types
• Date function print diff between dates and how to print Milliseconds
• Max size of varchar2 in SQL and PL/SQL
• CHAR= 2000 bytes
• VARCHAR2 = 4000 bytes in SQL and 32K in PL/SQL
• Data type conversion matrix
• Explicit casting using CAST in SQL Query. Syntax : CAST(column AS DATATYPE(size))
  
• MAX LIMITS
• Logical Database Limits
• Max cols in a table = 1000
• Indexes per table = Unlimited
• Limit to max no of chars in DBMS_OUTPUT.PUT_LINE = 255 (9i), 32K (10g)

• 
  
  1  BEGIN--9i
  
  
  2     DBMS_OUTPUT.PUT_LINE( RPAD('x',256,'x') );
  
  
  3  END;
  
  
  4  /
  
  
  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
  
  
  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
  
  
  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
  
  
  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
  PL/SQL procedure successfully completed.

•   1 BEGIN--10g
    2    DBMS_OUTPUT.PUT_LINE( RPAD('x',32767,'x') || 'even more char');
    3  END;
    4  /
  
  BEGIN
  *
  ERROR at line 1:
  ORA-20000: ORU-10028: line length overflow, limit of 32767
  bytes per line
  

• SQL> set serveroutput on size 1000
  

  SP2-0547: size option 1000 out of range (2000 through 1000000)

• SQL> set serveroutput on size unlimited 

• SQL> show serveroutput

• serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

• SQL> set serveroutput on

• SQL> show serveroutput

• serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

• MAX Limit on the static values in the IN clause is 1000
• MAX Limit on the no of sub queries IN the where clause  = 255
• MAX Limit on the no of sub queries IN the from clause = No Limit
• Triggers = 11
• Programming Structures
• For Loop i index CANNOT be referred or incremented outside the LOOP construct.
• PSUEDO Columns
• ROWID - Physical location of a row on the disk
• ORA_ROWSCN - SCN no for the row 
• ROWNUM - Logical Number of Row in a resultset
• NEXTVAL - Next Value of a Sequence
• CURRVAL - Current Value of a Sequence
• SQL
• LIKE is used for wild card compares with % and _
• Concatenation operator ||
• A many to many relation is implemented using a intersection table / bridge table
• Sub-query in IN clause can't have order by clause.
• MERGE and Conditional MERGE for 3 operations
• Delete from parent when child exist (CASCADE OPT)
• Select * from emp where pos = 'sales' or pos = 'pur' and sal > 1500 what will execute first AND or OR. Refer Operator Precedence.
• Single row sub query returns more than 1 row error can be avoided using In clause
• What is the output of select * from emp where rownum = 2; No rows returns whereas
• Select * from emp where rownum <= 2 will give an output, doesn't work with > or =. The filter is applied after the resultset is returned.
• But <=1, = 1 and >=1 all will give result
• CO-RELATED queries O(m*n) instead of O(m+n) for IN
• ANSI compliant equivalent statement
• Selecting from a cursor / collection
• X RIGHT OUTER JOIN Y will return all rows from the table Y on the RIGHT
• In outer join (+) side table can have NULL Cols
• Oracle creates implicit cursor for DML operations Insert/Update/Delete
• Delete on of the identical rows
• Delete from tab t1 where t1.rowid > (Select min(t2.rowid) from tab t2 where t2.emp_no = t1.emp_no);
• 4 table join will have min 3 join conditions
• NULL in IF means do nothing and pass control to the next statement.
• Drop Table with purge option drops the table including from the recycle bin
• Handling NULLs
• NVL - The NVL function allows you to replace null values with a default value. If the value in the first parameter is null, the function returns the value in the second parameter. If the first parameter is any value other than null, it is returned unchanged.
• DECODE - The DECODE function is not specifically for handling null values, but it can be used in a similar way to the NVL function, as shown by the following example.
• NULLIF - Returns NULL for a condition. The NULLIF function was introduced in Oracle 9i. It accepts two parameters and returns null if both parameters are equal. If they are not equal, the first parameter value is returned.
• NVL2 - The NVL2 function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter.
• COALESCE - The COALESCE function was introduced in Oracle 9i. It accepts two or more parameters and returns the first non-null value in a list. If all parameters contain null values, it returns null.
• PL/SQL
• Variables types as per scope : Local / Global / Constant
• DDL in PL/SQL (Yes its possible using EXECUTE IMMEDIATE)
• OPERATORS
• Operator precedence
1. NULL , LIKE , BETWEEN , IN, EXISTS
2. NOT
3. AND
4. OR * / + -  

• INTERVAL
  BINARY, COLLATE
  !
  - (unary minus), ~ (unary bit inversion)
  ^
  *, /, DIV, %, MOD
  -, +
  <<, >>
  &
  |
  = (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
  BETWEEN, CASE, WHEN, THEN, ELSE
  NOT
  AND, &&
  XOR
  OR, ||
  = (assignment), :=

• Data Dictionary Tables
• user_views - Objects owned by current schema
• all_views - Objects in all schema wherein the current schema has privilege to access
• dba_view - All Object in the DB irrespective of any access
• user_objects - gives info abt list of objects
• user_tables - gives info abt list of tables
• user_col_privs - gives info abt column priviledges
• user_tab_columns - gives info abt table columns
• dba_data_files - gives info about tablespace and size of file
• EXTERNAL Tables
• External Tables an Introduction
• Art of Ext Tabs
• Ext Tables
• External Table Concepts
• ORAFAQ - External Tables
• PSOUG - External Table
• Oracle External Tables
• External Tables
• Art more ext tables
• Preprocess External Tables
• ExternalTables in Oracle 9i
• External Tables
• Tips for Loading External Tables

• create directory etl_dir as 
  'C:\etl_dir';

  Grant read, write on directory etl_dir to etladmin; 

  Create table indata1
  (
      cust_id     number,
      cust_name    varchar2(20),
      credit_limit number(10)
  )
  organization external
  (
      type oracle_loader
      default directory etl_dir
      access parameters
      (
         records delimited by newline
         fields terminated by ","
      )
      location ('indata1.txt')
  )
  /
  

• CURSOR SHARING AND TYPES
• EXACT : Queries exactly match textually including case, space and literals.
• SIMILAR : Queries match ignoring minor textual difference like case, space and literals as long as the plan is the same (as data distribution is for the literal is same).
• FORCE : SIMILAR but Plan is different still they are forced to match.
• Links
• Cursor Sharing
• Adaptive Cursor Sharing 11g R1
• Cursor Sharing
• Initparams
• 11g Adaptive Cursor Sharing
• MATERIALIZED VIEW (aka. time based snapshots aka summary tables)
• A materialized view stores both definitions of view plus rows resulting from the execution of the view. It is more efficient to use materialized views if query involves summaries, large or multiple joins or both. It is a pre-computed table comprising aggregated or joined data from fact and possibly dimensions tables. Also known as a summary or aggregate table and mainly used for improving query performance or providing replicated data.
• A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multi-master replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site, as illustrated in Figure 3-1. The arrows in Figure 3-1 represent database links.
• Types of materialized views
• There are three types of materialized views :
• Read-only materialized view
• Updateable materialized view
• Writeable materialized view 
• Techniques used to refresh a Materialized View (Procedure used to refresh a mview)
• Materialized Views Refresh Techniques
• Manual Refresh: Can be performed using DBMS_MVIEW package. (REFRESH, REFRESH_DEPENDENT, REFRESH_ALL_VIEWS)
• Automatic Refresh: Can be performed in two ways:
• a)ON COMMIT – Materialized view gets updated whenever changes to one of these tables are committed.
• b)ON DEMAND – At Specified Time – Refresh is scheduled to occur for specified time by using START WITH & NEXT clauses. For such refreshes, instance must initiate a process with JOB_QUEUE_PROCESSES.
• Materialized Views Refresh Methods
• FAST Clause 
• The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.
• You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
• SQL> CREATE MATERIALIZED VIEW LOG ON emp;
• Materialized view log created.
• Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function. 
• COMPLETE Clause (Also know as full refresh)
• The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible. 
• FORCE Clause
• When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.
• PRIMARY KEY and ROWID Clause 
• WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.
• PRIMARY KEY materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
• ROWID materialized views should have a single master table and cannot contain any of the following :
• DISTINCT or AGGREGATE functions
• GROUP BY Subqueries , JOINS & SET operations
• TIMING the refresh
• The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes
• REFRESHING materialized view
• Refreshing a materialized view synchronizes is with its master table. Oracle performs the following operations when refreshing a materialized view. In the case of a complete refresh (using dbms_mview.refresh) 
1. sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh.
2. The materialized base view is truncated.
3. All rows selected from the master table are inserted into the snapshot base table.
4. sys.slog$ is updated to reflect the time of the refresh.
• In the case of a fast refresh, the steps are :
1. sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh.
2. Rows in the materialized base view are deleted.
3. All rows selected from the master table are inserted into the snapshot base table.
4. sys.slog$ is updated to reflect the time of the refresh.
5. Rows that are not needed anymore for a refresh by any materialized view are deleted from the materialized view log (.MLOG$_table)
• If a materialized view is being refreshed can be checked by querying the type of v$lock: if the type is JI a refresh is being performed.
• The following query checks for this : select  o.owner "Owner", o.object_name "Mat View", username "Username", s.sid "Sid"  from v$lock l, dba_objects o, v$session s  where o.object_id = l.id1 and l.type ='JI' and l.lmode = 6 and s.sid = l.sid and o.object_type = 'TABLE'
• Can a mview be partitioned - Yes
• Can you create an index on a mview - Yes
• SNAPSHOT too old error
• The ORA-01555 is caused by Oracle read consistency mechanism.  If you have a long running SQL that starts at 10:30 AM, Oracle ensures that all rows are as they appeared at 10:30 AM, even if the query runs until noon!
• Oracles does this by reading the "before image" of changed rows from the online undo segments.  If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear.
  From the docs we see that the ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter:
  
  
  ORA-01555: snapshot too old: rollback segment number string with name "string" too small.
  Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
  Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.
  
  
  The ORA-01555 snapshot too old error can be addressed by several remedies:
  1. Re-schedule long-running queries when the system has less DML load.
  2. Increasing the size of your rollback segment (undo) size.  The ORA-01555 snapshot too old also relates to your setting for automatic undo retention.
  3. Don't fetch between commits.
• QUERY RE-WRITE
• Query rewrite is the process of modifying a query to use the view rather than the base table.
• User do not explicitly need privileges on materialized views, permission on underlying base tables are required.
• Optimizer rewrites the queries in order to utilize materialized views.
• GLOBAL_QUERY_REWRITE > QUERY_REWRITE system privileges allows user to enable materialized views.
• Links
• Oracle 9i - Materialized View Concepts and Architecture
• ss64.com - Views, Materialized Views and Synonyms
• SkillGuru - Understanding materialized view in Oracle
• Wiki - Materialized View
• MV Presentation
• Materialized Views in Oracle
• Materialized Views
• Geek Interview - Whats a MV
• Configuring for Materialized Views
• Materialized View Selection and Maintenance Using MultiQuery Optimization
• CUBE ORGANIZED MATERIALIZED VIEWS, DO THEY DELIVER
• ORACLE - CREATE MATERIALIZED VIEW
• Maximizing Materialized Views
• PSOUG - Oracle Materialized Views
• Two-Phase Optimization for Selecting Materialized Views in a Data Warehouse
• in-oracle.com Oracle Materialized views (=snapshot)
• Overview of Data Warehousing with Materialized Views
• Why use materialized view instead of a table?
• Aggregates outside OBIEE - materialized views and query rewrite
• DataWarehousing and Materialized Views
• Dev's Weblog - Materialized views
• Materialized View Maintenance – Part I: Guidelines
• Cube, Rollup and Materialized Views
• Greedy selection of materialized views
• Materialized View Fast Refreshes are Slow
• Aggregate Navigation using materialized views and query re-write
• Adapted Extremal Optimization For Materialized Views Selection
• Automated Selection of Materialized Views and Indexes for SQL Databases
• Intelligent Query Answering with Virtual Mining and Materialized Views
• Fast materialized view refresh with prebuilt table option
• Oracle Database - Methodology for Designing and Building Materialized Views
• DBA Tips for Determining Invalid Materialized Views!!
• Materialized Views and Functional Index
• GTT
• Definition
• Applications often use some form of Temporary Data Store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
• Common Errors
• Use
• Improve Performance
• Reporting
• Global Temporary Tables have three major benefits:
• Non-interference between private sets of data.
• Ease of getting rid of 'scratch' data. In a heap table you either rollback, or delete it. But in a GTT, you can truncate explicitly, without affecting anyone else (or allow the implicit "truncate on commit / exit" effect to do the same thing).
• Decreased redo generation as, by definition, they are non-logging.
• Get your remote data into a cursor using database link
• Processes your data based on your custom requirement (such as filtering/modifying).
• Insert the changed data into a global temporary table.
• Join the global temporary table with other local oracle tables based on your requirement.
• Reduce complexity and no of joins
• Put the resultant data into its final destination table in your local database or to another remote database.
• Limitation:
• Mixing Global Temporary Tables (GTTs) with permanent tables usually causes some grief to the CBO. It has no information about the number of rows in the GTT, and therefore guesses (badly).
• Even if you analyze table .. or dbms_stats.gather_table_stats() you don't get stats on the temporary table.
• Set the init parameter dynamic_sampling to at least 2 for GTTs to be sampled at run-time.
• Note: All DDL's includes two implicit commits so any rows in a GTT specified with ON COMMIT DELETE ROWS will empty the table.
• ON COMMIT
• PRESERVE ROWS - truncate table when session ends.
• DELETE ROWS - truncate data after each commit.
• Links
• Temporary Tables
• GTT
• Difference between local and global temporary tables
• Oracle global temporary table example
• Global temporary tables
• Global temporary tables
• Miscellaneous Features
• If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
• Data in temporary tables is stored in temp segments in the temp tablespace.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• There are a number of restrictions related to temporary tables but these are version specific.
• VIEWS
• FORCE - Creates view even if the underlying table does not exist
• WITH CHECK OPTION - Restricts data for insert statements on view
• Read Only - Disallows DML on the view
• Indexes on views - Only possible in MView, not possible in regular view
• Create or Replace is used to add new columns to a view as it helps preserves grants.
• If a table / view is dropped nothing happens to its synonym 
• SEQUENCE
• Sequence and uses
• Syntax for sequence create sequence seq1 start with 1
• NOCACHE
• CYCLE
• NEXTVAL
• CURRVAL
• Packages/Procedures/Functions/Blocks
• Adv of a package
• Package spec without a body
• Adv proc vs sql vs pkg
• Encapsulation
• Overloading in pkg & outside for proc
• Overriding
• NOCOPY Hint and Other Hints
• Pipelined functions - PIPE ROW () Table ()
• EXECUTE
• EXEC
• Call functions in sql
• PRAGMA passes information to compiler uses SGA. EXCEPTION_INIT, SERIALLY REUSABLE, AUTONOMOUS TRANSACTION, RESTRICT REFERENCES (PURITY LEVEL), INLINE
• A package that is marked SERIALLY_REUSABLE has the following properties:
  • Its package variables are meant for use only within the work boundaries, which correspond to calls to the server (either OCI call boundaries or PL/SQL RPC calls to the server).

    Note: If the application programmer makes a mistake and depends on a package variable that is set in a previous unit of work, then the application program can fail. PL/SQL cannot check for such cases.

  • A pool of package instantiations is kept, and whenever a "unit of work" needs this package, one of the instantiations is "reused", as follows:
    • The package variables are reinitialized (for example, if the package variables have default values, then those values are reinitialized).
    • The initialization code in the package body is run again.
  • At the "end work" boundary, cleanup is done.
    • If any cursors were left open, then they are silently closed.
    • Some non-reusable secondary memory is freed (such as memory for collection variables or long VARCHAR2s).
    • This package instantiation is returned back to the pool of reusable instantiations kept for this package.
  • Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, then Oracle Database generates an error.
  • With SERIALLY_REUSABLE packages, application developers have a way of modelling their applications to manage their memory better for scalability. Package state that they care about only for the duration of a call to the server should be captured in SERIALLY_REUSABLE packages.
• SERIALLY_REUSABLE 
  The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.
• A SERIALLY_REUSABLE pragma can appear in the specification of a bodiless package, or in both the specification and body of a package. The pragma cannot appear only in the body of a package.
• Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, the database generates an error.
• The EXCEPTION_INIT pragma associates a user-defined exception name with an Oracle Database error number. You can intercept any Oracle Database error number and write an exception handler for it, instead of using the OTHERS handler.
• The RESTRICT REFERENCES pragma asserts that a user-defined subprogram does not read or write database tables or package variables.
  Subprograms that read or write database tables or package variables are difficult to optimize, because any call to the subprogram might produce different results or encounter errors.
• The RESTRICT REFERENCES pragma is deprecated. Oracle recommends using DETERMINISTIC and PARALLEL_ENABLE (described in Function Declaration and Definition) instead of RESTRICT REFERENCES.
• The INLINE pragma specifies that a subprogram call is, or is not, to be inlined. Inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram.
• Example

• DECLARE
     deadlock_detected EXCEPTION;
     PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
  BEGIN
     NULL; -- Some operation that causes an ORA-00060 error
  EXCEPTION
     WHEN deadlock_detected THEN
        NULL; -- handle the error
  END;
  /
  
  raise_application_error(
        error_number, message[, {TRUE | FALSE}]);
  
  EXCEPTION
    WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN
      -- handle the error
  END;
  
  DBMS_STANDARD.RAISE_APPLICATION_ERROR
  

• The INLINE pragma specifies that a subprogram call is, or is not, to be inlined. Inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram.
• The AUTONOMOUS_TRANSACTION pragma marks a routine as autonomous; that is, independent of the main transaction.
In this context, a routine is one of the following:
• Top-level (not nested) anonymous PL/SQL block
• Standalone, packaged, or nested subprogram
• Method of a SQL object type
• Database trigger
  You cannot apply this pragma to an entire package, but you can apply it to each subprogram in a package.
  You cannot apply this pragma to an entire an object type, but you can apply it to each method of a SQL object type.
  Unlike an ordinary trigger, an autonomous trigger can contain transaction control statements, such as COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.
  In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.
  If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. The database raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.
  If you try to exit an active autonomous transaction without committing or rolling back, the database raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.
• AUTONOMOUS TRANSACTION defn and commit
• Adv of PL/SQL Procedure over block (AUTHID, DEFINER)
• INTO clause variable should be declared
• RETURNING CLAUSE
• HIERARCHICAL Queries
• Used to navigate a hierarchy
• CONNECT BY PRIOR ... START WITH
• In the hr.employees table, the employee Steven King is the head of the company and has no manager. Among his employees is John Russell, who is the manager of department 80. If we update the employees table to set Russell as King's manager, we will create a loop in the data:

  UPDATE employees SET manager_id = 145
     WHERE employee_id = 100;
  
  SELECT last_name "Employee", 
     LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
     FROM employees
     WHERE level <= 3 AND department_id = 80
     START WITH last_name = 'King'
     CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
    2    3    4    5    6    7  ERROR:
  ORA-01436: CONNECT BY loop in user data
  
  

  The NOCYCLE parameter in the CONNECT BY condition causes Oracle to return the rows in spite of the loop. The CONNECT_BY_ISCYCLE pseudocolumn shows you which rows contain the cycle:

  SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
     LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
     FROM employees
     WHERE level <= 3 AND department_id = 80
     START WITH last_name = 'King'
     CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;
  

• Simple Example :

  SELECT            
  
  
  CONNECT_BY_ROOT 
  
  Examples The following example returns the last name of
  
  each employee in department 110, each manager
  
  above that employee in the hierarchy, the number
  
  of levels between manager and employee,
  
  and the path between the two:
  
  
  
  
  
  
  SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
  
  
  
  

     LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
     FROM employees
     WHERE LEVEL > 1 and department_id = 110
     CONNECT BY PRIOR employee_id = manager_id;
  

  last_name, employee_id, manager_id, LEVEL
  FROM              employees

  START WITH        employee_id = 100
  CONNECT BY PRIOR  employee_id = manager_id
  ORDER SIBLINGS BY last_name;

• In the hr.employees table, the employee Steven King is the head of the company and has no manager. Among his employees is John Russell, who is the manager of department 80. If we update the employees table to set Russell as King's manager, we will create a loop in the data:

  UPDATE employees SET manager_id = 145
     WHERE employee_id = 100;
  
  SELECT last_name "Employee", 
     LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
     FROM employees
     WHERE level <= 3 AND department_id = 80
     START WITH last_name = 'King'
     CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
    2    3    4    5    6    7  ERROR:
  ORA-01436: CONNECT BY loop in user data
  
  

  The NOCYCLE parameter in the CONNECT BY condition causes Oracle to return the rows in spite of the loop. The CONNECT_BY_ISCYCLE pseudocolumn shows you which rows contain the cycle:

  SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
     LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
     FROM employees
     WHERE level <= 3 AND department_id = 80
     START WITH last_name = 'King'
     CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL 

     <= 4;
  

• Simple Example :

  SELECT            last_name, employee_id, manager_id, LEVEL
  FROM              employees

  START WITH        employee_id = 100
  CONNECT BY PRIOR  employee_id = manager_id
  ORDER SIBLINGS BY last_name;

• INDEXES
• Index is useful when 80%+ columns values are not null
• FULL TABLE scan is better than Index when fetching more than 20% of the records in the table.
• Covering Indexes
• Invisible Indexes
• Indexes Compression
• Types of indexes btree vs bitmap vs function vs reverse key
• Bitmap join index
• Bitmap join indexes
• Difference between bitmap join indexes and materialized view
• In Oracle 8i performance improvements were made using materialized views to store the resulting rows of queries. The benefits of this mechanism are still relevant, but a certain subset of the queries used in a data warehouse may benefit from the use of Bitmap Join Indexes.
• Oracle 11g bitmap join indexes
• A bitmap join index would allow you to index (for example) the DNAME column from DEPT - against the EMP table (emp has deptno, dept has deptno->dname - instead of query and join to find out everyone in the RESEARCH department - you can bitmap join index the data and just query the emp table) to do the same in a materialized view would have you replicate the entire EMP table and the DNAME (at least) column - and then create a bitmap index on it. 
• A bitmap join index allows you to denormalize in the index, not in the tables. 
• In a Bitmap Index, each distinct value for the specified column is associated with a bitmap where each bit represents a row in the table. A '1' means that row contains that value, a '0' means it doesn't. 
  Bitmap Join Indexes extend this concept such that the index contains the data to support the join query, allowing the query to retrieve the data from the index rather than referencing the join tables. Since the information is compressed into a bitmap, the size of the resulting structure is significantly smaller than the corresponding materialized view.

  
  

  CREATE BITMAP INDEX cust_sales_bji ON sales(customers.state) FROM sales, customers WHERE sales.cust_id = customers.cust_id; SELECT SUM(sales.dollar_amount) FROM sales, customer WHERE sales.cust_id = customer.cust_id AND customer.state = 'California' 

  
  

  Restrictions

  Bitmap Join Indexes have the following restrictions:
• Parallel DML is currently only supported on the fact table. Parallel DML on one of the participating dimension tables will mark the index as unusable. 
• Only one table can be updated concurrently by different transactions when using the bitmap join index. 
• No table can appear twice in the join.
  • You cannot create a bitmap join index on an index-organized table or a temporary table.
  • The columns in the index must all be columns of the dimension tables.
  • The dimension table join columns must be either primary key columns or have unique constraints.
  • If a dimension table has composite primary key, each column in the primary key must be part of the join.
• Clusters and Indexes
• What init param needs to be set for function indexes to work
• Oracle Function Based Indexes
• QUERY_REWRITE_ENABLED=TRUE
• QUERY_REWRITE_INTEGRITY=TRUSTED
• CURSORS
• Def : Named Pointer to a query
• Types : Static (Regular) and RefCursor (Dynamic)
• RefCursors
• declare  type r_cursor is REF CURSOR;  c_emp r_cursor;  en emp.ename%type;begin  open c_emp for select ename from emp;  loop      fetch c_emp into en;      exit when c_emp%notfound;      dbms_output.put_line(en);  end loop;  close c_emp;end;
  Read more at http://www.devshed.com/c/a/Oracle/Working-with-REF-CURSOR-in-PL-SQL/#iIAso18fIwMfD18J.99
• declare  type r_cursor is REF CURSOR;  c_emp r_cursor;  er emp%rowtype;begin  open c_emp for select * from emp;  loop      fetch c_emp into er;      exit when c_emp%notfound;      dbms_output.put_line(er.ename || ' - ' || er.sal);  end loop;  close c_emp;end;
  Read more at http://www.devshed.com/c/a/Oracle/Working-with-REF-CURSOR-in-PL-SQL/#iIAso18fIwMfD18J.99
• http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

• CREATE OR REPLACE
  PROCEDURE get_emp_rs (p_deptno    IN  emp.deptno%TYPE,
                        p_recordset OUT SYS_REFCURSOR) 

  AS 

• BEGIN 

•   OPEN p_recordset FOR
      SELECT ename,
             empno,
             deptno
      FROM   emp
      WHERE  deptno = p_deptno
   ORDER BY ename;
  END GetEmpRS; 

• SET SERVEROUTPUT ON SIZE 1000000
  DECLARE
    l_cursor  SYS_REFCURSOR;
    l_ename   emp.ename%TYPE;
    l_empno   emp.empno%TYPE;
    l_deptno  emp.deptno%TYPE;
  BEGIN
    get_emp_rs (p_deptno    => 30,
                       p_recordset => l_cursor);
    LOOP 
      FETCH l_cursor
      INTO  l_ename, l_empno, l_deptno;
      EXIT WHEN l_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
    END LOOP;
    CLOSE l_cursor;
  END;
  /

• Complex recordsets
• Ref cursors in oracle plsql overview
• Oracle plsql ref cursors
• Steps to Use : DECLARE, OPEN, FETCH, CLOSE
• Opening a already open cursor throws an error.
• %ISOPEN
• %FOUND
• %NOTFOUND - returns false if a fetch statement returns at least one row
• %ROWCOUNT
• Ref Cursors
• Used when you need to dynamically reassign different queries to the same named cursor

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
  cnumber number;
  CURSOR c1
  IS
     SELECT course_number
     from courses_tbl
     where course_name = name_in;
BEGIN
  open c1;
  fetch c1 into cnumber;
  if c1%notfound then
     cnumber := 9999;
  end if;
  close c1;
RETURN cnumber;
END;

• Oracle/PLSQL: Cursor Attributes
  While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.

  VAttribute	Explanation
  %ISOPEN	- Returns TRUE if the cursor is open, FALSE if the cursor is closed.
  %FOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.- Returns NULL if cursor is open, but fetch has not been executed. - Returns TRUE if a successful fetch has been executed. - Returns FALSE if no row was returned.
  %NOTFOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.- Return NULL if cursor is open, but fetch has not been executed. - Returns FALSE if a successful fetch has been executed. - Returns TRUE if no row was returned.
  %ROWCOUNT	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.- Returns the number of rows fetched. - The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened.
• COLLECTIONS
• Scalar vs composite variable records
• Use of pl/sql table or associative arrays, nested tables, varrays
• Associative Arrays, PL/SQL Tables, Index by Tables are the same thing
• Nested Tables, VARRAYS
• Bulk Collect Save Exception Limit
• SET Operations
• MEMBER OF
• Write a program
• Links
• Collection enhancements in 11g release 2
• Using PL/SQL Collections and Records
• PL/SQL Collections
• PL/SQL 101: Working with Collections
• Collections
• Collections
• Oracle Collections
• Developpez Collections
• Collection Types
• Advanced PL-SQL
• Nested Tables
• PL-SQL Collections
• How to initialise a nested table collection which resides inside a plsql record
• Multidimensional Collections
• Table 5-1 Characteristics of PL/SQL Collection Types

  Collection Type	Number of Elements	Subscript Type	Dense or Sparse	Where Created	Can Be Object Type Attribute
  Associative array (or index-by table)	Unbounded	String or integer	Either	Only in PL/SQL block	No
  Nested table	Unbounded	Integer	Starts dense, can become sparse	Either in PL/SQL block or at schema level	Yes
  Variable-size array (varray)	Bounded	Integer	Always dense	Either in PL/SQL block or at schema level	Yes

  Unbounded means that, theoretically, there is no limit to the number of elements in the collection. Actually, there are limits, but they are very high—for details, see Referencing Collection Elements.
  Dense means that the collection has no gaps between elements—every element between the first and last element is defined and has a value (which can be NULL).
  Referencing : collection_name (subscript) 
  collection_name (subscript) := expression;

  • Pre-defined collection types in Oracle

    DBMS_DEBUG_VC2COLL

    http://www.williamrobertson.net/documents/collection-types.html

  
  

  • select * from table    (sys.ODCIVarchar2List('AAA','BBB','CCC')); COLUMN_VALUE ------------ AAA BBB CCC
• PERFORMANCE TUNING
• Check if the STATISTICS are uptodate
• Fastest Access Path is ACCESS BY ROWID
• NVL, NULL, NOT will NOT invoke index on the column
• Steps in execution of a query
• How Oracle Processes SQL Statements
• All Oracle SQL statements must be processed the first time that they execute (unless they are cached in the library cache). and SQL execution steps include:
  1. A syntax check - Are all keywords present "select . . . from", etc . .
  2. A semantic check against the dictionary - Are all table names spelled correctly, etc.
  3. The creation of the cost-based decision tree of possible plans
  4. The generation of the lowest cost execution plan
  5. Binding the execution plan - This is where the table--> tablespace --> datafile translation occurs.
  6. Executing the query and fetching the rows. 
  Parse Phase - During the parse phase, Oracle opens the statement handle, checks whether the statement is OK (both syntactically and whether the involved objects exist and are accessible) and creates an execution plan for executing this statement. Parse call does not return an error if the statement is not syntactically correct.
  Parsing can be a very expensive operation that takes a lot of resources to execute. Special problem are so called “hard parses” which happen when there is no previously parsed version of the SQL to reuse.
  Once the execution plan is created, it is stored in the library cache (part of theshared_pool_size) to facilitate re-execution.  There are two types of parses:
  • Hard parse - A new SQL statement must be parsed from scratch.  (Seehard parse ratio, comparing hard parses to executes).  If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-reentrant SQL that does not use host variables (see cursor_sharing=force).
     
  • Soft parse - A reentrant SQL statement where the only unique feature are host variables. (See soft parse ratio, comparing soft parses to executes).  The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully reentrant SQL that “parses SQL once and executes many times” (also see your setting for session_cached_cursors, as this effects the reentrancy of an SQL statement).   
  Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.  See the cursor_sharing parameter for an easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.
  • Bind Phase - Once the plan is syntactically created, Oracle gathers the parameters from the client program needed for the execution. It makes the addresses of the program variables “known” to Oracle.
  • Execute Phase - During the execute phase, Oracle executes the statement, reports any possible errors, and if everything is as it should be, forms the result set. Unless the SQL statement being executed is a query, this is the last step of the execution.
  • Define Phase - Define is analogous to binds, only “output oriented”. The OCI define makes addresses of the output variables “known” to the Oracle process in order to make it possible to the fetch call to know where to put the output variables. The define call is frequently skipped and is largely considered unnecessary because of the automatic variables allocation in PHP.
  • Fetch Phase - During the fetch phase, Oracle brings the rows of the result to the program and makes them accessible by the PHP interpreter. Once more, the define and fetch phases are relevant for queries only. The Oracle OCI interface and the PHP OCI8 module contain calls to facilitate each of those phases
• SQL exeution steps

• The Stages in Processing a SQL Statement

  
• Optimization
• A syntax check - Are all keywords present "select . . . from", etc . .
• A semantic check against the dictionary - Are all table names spelled correctly, etc.
• Object privileges are evaluated.
• The creation of the cost-based decision tree of possible plans
• The generation of the lowest cost execution plan
• Binding the execution plan - This is where the table--> tablespace --> data file translation occurs.
• Executing the query and fetching the rows.
• SQL TRACE vs TK PROF vs STATSPACK vs AWR
• SQL Trace
• AWR : Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).
• AWR
• SQL PROFILER (Trace + Timing + No of Execs) vs SQL TRACE (Just Trace)
• http://oracleflash.com/44/Write-fast-and-efficient-PLSQL---DBMS_PROFILER.html
• http://www.oradev.com/dbms_profiler.jsp
• Before DBMS_PROFILER was introduced tuning long PL/SQL programs was a great pain. Tuning PL/SQL was done by SQL TRACE and TKPROF. Code of thousands of lines was monitored via DBMS_UTILITY.GET_TIME to measure time consumed by individual statements.
  With the presence of DBMS_PROFILER this has become quite easy to monitor the performance of the PL/SQL programs and identifying the culprit statements consuming too much time while the run of the program.
• SOFT PARSE vs HARD PARSE
• Parse Phase - During the parse phase, Oracle opens the statement handle, checks whether the statement is OK (both syntactically and whether the involved objects exist and are accessible) and creates an execution plan for executing this statement. Parse call does not return an error if the statement is not syntactically correct.
• Parsing can be a very expensive operation that takes a lot of resources to execute. Special problem are so called “hard parses” which happen when there is no previously parsed version of the SQL to reuse.
• Once the execution plan is created, it is stored in the library cache (part of the shared_pool_size) to facilitate re-execution.  There are two types of parses:
• Hard parse - A new SQL statement must be parsed from scratch.  (See hard parse ratio, comparing hard parses to executes).  If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-re-entrant SQL that does not use host variables (see cursor_sharing=force).
• Soft parse - A re-entrant SQL statement where the only unique feature are host variables. (See soft parse ratio, comparing soft parses to executes).  The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully re-entrant SQL that “parses SQL once and executes many times” (also see your setting for session_cached_cursors, as this effects the reentrancy of an SQL statement).
• Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.  See the cursor_sharing parameter for an easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.
• Bind Phase - Once the plan is syntactically created, Oracle gathers the parameters from the client program needed for the execution. It makes the addresses of the program variables “known” to Oracle.
• Execute Phase - During the execute phase, Oracle executes the statement, reports any possible errors, and if everything is as it should be, forms the result set. Unless the SQL statement being executed is a query, this is the last step of the execution.
• Define Phase - Define is analogous to binds, only “output oriented”. The OCI define makes addresses of the output variables “known” to the Oracle process in order to make it possible to the fetch call to know where to put the output variables. The define call is frequently skipped and is largely considered unnecessary because of the automatic variables allocation in PHP.
  
• Fetch Phase - During the fetch phase, Oracle brings the rows of the result to the program and makes them accessible by the PHP interpreter. Once more, the define and fetch phases are relevant for queries only. The Oracle OCI interface and the PHP OCI8 module contain calls to facilitate each of those phases.
• DBMS_PROFILER VS DBMS_TRACE VS DBMS_SUPPORT VS DBMS_MONITOR
• http://www.oradev.com/dbms_profiler.jsp
• Before DBMS_PROFILER was introduced tuning long PL/SQL programs was a great pain. Tuning PL/SQL was done by SQL TRACE and TKPROF. Code of thousands of lines was monitored via DBMS_UTILITY.GET_TIME to measure time consumed by individual statements.

• With the presence of DBMS_PROFILER this has become quite easy to monitor the performance of the PL/SQL programs and identifying the culprit statements consuming too much time while the run of the program.

• HASH JOIN vs MERGE JOIN vs NESTED LOOPS
• FAST FULL SCAN vs SKIP SCAN
• Refer Hints section
• SET AUTOTRACE ON OPTIONS
• AUTOTRACE OPTIONS: -
  1) SET AUTOTRACE OFF - No AUTOTRACE report is generated.
  This is the default. Queries are run as normal.
  2) SET AUTOTRACE ON EXPLAIN - The query is run as normal,
  and the AUTOTRACE report shows only the optimizer
  execution path.
  3) SET AUTOTRACE ON STATISTICS - The query is run as normal,
  and the AUTOTRACE report shows only the SQL statement
  execution statistics.
  4) SET AUTOTRACE ON - The query execution takes place,
  and the AUTOTRACE report includes both the optimizer
  execution path and the SQL statement execution statistics.
  5) SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but
  suppresses the printing of the query output, if any.
  6) SET AUTOTRACE TRACEONLY STATISTICS - Like SET AUTOTRACE
  TRACEONLY, but suppresses the display of the query plan.
  It shows only the execution statistics.
  7) SET AUTOTRACE TRACEONLY EXPLAIN - Like SET AUTOTACE
  TRACEONLY, but suppresses the display of the execution
  statistics, showing only the query plan. This setting
  does not actually execute the query. It only parses
  and explains the query.
• SQL*LOADER DIRECT ACCESS PATH v/s CONVENTIONAL PATH
• Initial Loading Full data
• Lazy Loading
• Loader Modes
• Conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications.
  When SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources. This can slow the load significantly. Extra overhead is added as SQL statements are generated, passed to Oracle, and executed.
  The Oracle database looks for partially filled blocks and attempts to fill them on each insert. Although appropriate during normal use, this can slow bulk loads dramatically.
• Direct Path Loading : Instead of filling a bind array buffer and passing it to the Oracle database with a SQL INSERT statement, a direct path load uses the direct path API to pass the data to be loaded to the load engine in the server. The load engine builds a column array structure from the data passed to it.
  The direct path load engine uses the column array structure to format Oracle data blocks and build index keys. The newly formatted database blocks are written directly to the database (multiple blocks per I/O request using asynchronous writes if the host platform supports asynchronous I/O).
  Internally, multiple buffers are used for the formatted blocks. While one buffer is being filled, one or more buffers are being written if asynchronous I/O is available on the host platform. Overlapping computation with I/O increases load performance.
• PARTITION EXCHANGE
• TRANSPORTABLE TABLESPACES
• REWRITING OUTER JOINS USING INLINE VIEWS CORRELATED QUERIES
• FOR ALL and BULK COLLECT, INSERT, UPDATE, INTO COLLECTION
• http://psoug.org/reference/array_processing.html
• DECLARE
   CURSOR s_cur IS
   SELECT *
   FROM servers;
   TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
   s_array fetch_array;
  BEGIN
    OPEN s_cur;
    LOOP
      FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
      FORALL i IN 1..s_array.COUNT
      INSERT INTO servers2 VALUES s_array(i);
      EXIT WHEN s_cur%NOTFOUND;
    END LOOP;
    CLOSE s_cur;
    COMMIT;
  END;
• DECLARE
   TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
   INDEX BY BINARY_INTEGER;
   d_array myarray;
  BEGIN
    d_array(1) := 608;
    d_array(2) := 610;
    d_array(3) := 612;
    FORALL i IN d_array.FIRST .. d_array.LAST
    UPDATE servers2
    SET srvr_id = 0
    WHERE srvr_id = d_array(i);
    COMMIT;
  END;
  /
• Using INDICES OF and VALUESOF with Non-Consecutive Index Values
• FORALL i IN INDICES OF cust_tab
  INSERT INTO valid_orders
  (cust_name, amount)
    VALUES
    (cust_tab(i), amount_tab(i));
• FORALL i IN VALUES OF rejected_order_tab
    INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
• DECLARE
    -- collections to hold a set of customer names and amounts 
   SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
   TYPE cust_typ IS TABLE OF cust_name;
   cust_tab cust_typ;
   SUBTYPE order_amount IS valid_orders.amount%TYPE;
   TYPE amount_typ IS TABLE OF NUMBER;
   amount_tab amount_typ;
    -- collections to point into the CUST_TAB collection.
   TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
   big_order_tab index_pointer_t := index_pointer_t();
   rejected_order_tab index_pointer_t := index_pointer_t();
• PROCEDURE setup_data 
• IS 
  BEGIN
    -- Set up sample order data, with some invalid and 'big' orders
    cust_tab := cust_typ('Company1', 'Company2', 'Company3',
    'Company4', 'Company5');
    amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00,NULL);
  END setup_data;
  BEGIN
    setup_data;
    dbms_output.put_line('--- Original order data ---');
    FOR i IN 1..cust_tab.LAST
    LOOP
      dbms_output.put_line('Cust#' || i || ', '|| cust_tab(i) ||
      ': $'||amount_tab(i));
    END LOOP;
    -- Delete invalid orders (where amount is null or 0)
    FOR i IN 1..cust_tab.LAST
    LOOP
      IF amount_tab(i) is null or amount_tab(i) = 0 
•    THEN
        cust_tab.delete(i);
        amount_tab.delete(i);
      END IF;
    END LOOP;
    dbms_output.put_line('--Data with deleted invalid orders--');
    FOR i IN 1..cust_tab.LAST LOOP
      IF cust_tab.EXISTS(i) THEN
        dbms_output.put_line('Cust#' || i || ', ' || cust_tab(i) ||
        ': $'||amount_tab(i));
      END IF;
    END LOOP;
    -- Since the subscripts of our collections are not consecutive,
    -- we use use FORRALL...INDICES OF to iterate the subscripts
    FORALL i IN INDICES OF cust_tab
    INSERT INTO valid_orders
    (cust_name, amount)
    VALUES
    (cust_tab(i), amount_tab(i));
    -- Now let's process the order data differently extracting
    --  2 subsets and storing each subset in a different table. 
    setup_data; -- Reinitialize the CUST_TAB and AMOUNT_TAB collections
    FOR i IN cust_tab.FIRST .. cust_tab.LAST
    LOOP
      IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
        -- add a new element to the collection
        rejected_order_tab.EXTEND;
        -- record original collection subscript
        rejected_order_tab(rejected_order_tab.LAST) := i;
      END IF;
      IF amount_tab(i) > 2000 THEN
        -- Add a new element to the collection
        big_order_tab.EXTEND;
        -- record original collection subscript 
        big_order_tab(big_order_tab.LAST) := i;
      END IF;
    END LOOP;
    -- run one DML statement on one subset of elements,
    -- and another DML statement on a different subset. 
    FORALL i IN VALUES OF rejected_order_tab
    INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
    FORALL i IN VALUES OF big_order_tab
    INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
    COMMIT;
  END;
  /
• BULK BIND, BULK EXCEPTION, BULK ROWCOUNT
• FORALL index_name IN lower_boundary .. upper_boundary
• sql_statement
• SAVE EXCEPTIONS;
• FORALL index_name IN INDICES OF collection
• [BETWEEN lower_boundary AND upper_boundary]
• sql_statement
• SAVE EXCEPTIONS;
• FORALL index_name IN INDICES OF collection
• VALUES OF index_collection
• sql_statement
• SAVE EXCEPTIONS;
• P.T. FULL TABLE SCANS cost
• Diff between ESTIMATE STATISTICS AND COMPUTE STATISTICS
• Compute : Computes Statistics for whole Table(for all rows).
• Estimate : Computes Statistics on sample of rows over the table. 
  - Be default, its 1064 rows.
  - You can specify rows in numbers or in percentage of total rows. 
  - If the number or percentage of rows exceeds 50%, Statistics will be computed on all rows in Table.
• When is the Cost of the query in the explain plan NULL - When its RULE BASED OPT
• 1 Trillion records INSERT USER MERGE / PARTITION EXCHANGE / COPY / SQL LOADER / EXTERNAL TABLE / EXPDBIMPDB
• 10 Billion records INSERT ALL EXCEPT ONE CREATE TABLE
• Difference between DBMS_STATS and ANALYZE
• Analyze versus dbmsstats
• TUNING
• Analyze vs dbmsstats
• Cost based optimizer is preferred method for oracle optimizer. In order to make good use of the CBO, you need to create accurate statistics. Prior to oracle8i, we use ANALYZE command to gather statistics.
  
  DBMS_STATS package is introduced in oracle8i. Since Oracle8i, Oracle highly recommeds to use DBMS_STATS instead of ANALYZE command. This article is written in oracle10g. I am going to address below topics in this thread....
  
  1. Why oracle recommends to use DBMS_STATS package?
  2. What are the advantages of DBMS_STATS compared to ANALYZE?
  3. How do we use DBMS_STATS package to analyze the table?
  4. What are new features in each version for DBMS_STATS?
  
  Why oracle recommends to use DBMS_STATS since Oracle8i?
  
  1. Gathering statistics can be done in Parallel. This option is not available in ANALYZE command.
  
  2. It is used to collect the stale statistics. I discussed about collecting stale statistics in another topic. Please refer stale statistics to know more about collecting stale statistics.
  
  3. DBMS_STATS is a PL/SQL package. So it is easy to call. But ANALYZE does not.
  
  4. It is used to collect statistics for external tables. But ANALYZE does not.
  
  5. DBMS_STATS used to collect system statistics. But ANALYZE does not.
  
  6. Some time, ANALYZE does not produce accurate statistics. But DBMS_STATS does.
  
  7. We cannot use ANALYZE command to gather statistics for partition or sub partition level. But we can use DBMS_STATS to analyze any specific partition or sub partition. This is especially useful for partition table. We do not need to analyze the Historical data whenever we refresh the current partition.
  
  8. We can transfer statistics from one DB to another DB when we collected statistics through DBMS_STATS. But it can not be done when we use ANALYZE command to collect the statistics. Please refer statistics transfer to know more about transferring statistics.
• Example for collecting statistics on table: 
  
  DBMS_STATS.GATHER_TABLE_STATS(
  OWNNAME => 'TANDEB',
  TABNAME => 'CUSTOMER',
  PARTNAME => 'PART092009'
  GRANULARITY => 'PARTITION',
  ESTIMATE_PERCENT => 10,
  METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
  CASCADE => TRUE,
  NO_INVALIDATE => TRUE);
  
  Example for collecting statistics on Schema: 
  
  DBMS_STATS.GATHER_SCHEMA_STATS(
  OWNNAME => 'SCOMPPRD',
  ESTIMATE_PERCENT => 10,
  METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
  OPTIONS => 'GATHER',
  CASCADE => TRUE,
  NO_VALIDATE => TRUE);
  
  Example for collecting system statistics:
  
  DBMS_STATS.GATHER_SYSTEM_STATS(
  GATHERING_MODE => 'INTERVAL',
  INTERVAL => 10);
  
  Example for collecting database statistics: 
  
  DBMS_STATS.GATHER_DATABASE_STATS(
  ESTIMATE_PERCENT => 10,
  METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
  CASCADE => TRUE,
  NO_VALIDATE => TRUE,
  GATHER_SYS => FALSE)
• HINTS
• Join Order ORDERED vs LEADING
• /*+ LEADING */ Hint – specifies the set of tables to be used as the prefix in the execution plan.
  • The "leading" hint is ignored if the tables specified cannot be joined first in the order specified
  • If you specify two or more conflicting LEADING hints – all of them are ignored
  • The ORDERED hint overrides all LEADING hints
• /*+ ORDERED */ Hint – Oracle joins tables in the order in which they appear in the FROM clause
• The optimizer normally chooses the order in which to join the tables, but it's time-consuming and wrong if you have bad CBO stats (especially histograms)
• You may want to specify the "ordered" hint if you know something about the number of rows selected from each table that the optimizer does not
• Optimizer Hints in Oracle 11g and hint overkill
• Hints Ref
• Index scans
• Hints
• Hints
• Hints
• 9i Hints 
• 12g Hints 
• Hints on hints
• OraDev on Hints
• Query Tuning using push hints
• Predicate Pushing
• Predicates
• Push Predicates
• Predicate Pushing
• Hint push pred
• Tune union view using push pred
• Pushing Predicates from Oracle scratch pad
• Pushing Predicates for joins
• Basics of join predicate pushdown 
• Pushing parameters into views a-k-a predicate-pushing 
• Join predicate push-down optimizations 
• Predicate Pushing
• Hints of Acceptability
• INDEX_SS
• An index skip scan uses logical subindexes of a composite index. An index skip scan uses logical subindexes of a composite index. The index skip scan was introduced to allow Oracle to “skip” leading-edge predicates in a multi-column index. You can force an index skip scan with the /*+ index_ss */ hint. For example, consider the following concatenated index on a super-low cardinality column, following by a very selective column: 
• create index  sex_emp_id on emp (sex, emp_id);
  Prior to Oracle9i, this index could only be used with both sex and emp_id were present in the SQL query, or when the sex column was specified. The following query would not be able to use the concatenated index:
  

  
  select emp_id from emp where emp_id = 123;

  The Oracle 9i skip scan execution plan allows for our concatenated index to be used, even though sex is not specified in the SQL query WHERE clause. The index skip scan promises that there is no need to build a second index on the emp_id column, since it is technically redundant to the multi-column index. Oracle acknowledges that the index skip scan is not as fast as  direct index lookup, but states that the index skip scan is faster than a full-table scan.

  INDEX_FFS
  

  
  The INDEX_FFS hint causes a fast full index scan to be performed rather than a full table scan.

  
  

  A fast full scan is a full index scan in which the database reads all index blocks in no particular order. The database accesses the data in the index itself, without accessing the table.

  
  This is the optimal access method when all of the information required to answer a query is contained in indexed columns. Thus we will start the demo by first finding indexed columns and then building a query that uses only those columns. And, to make the demo as reliable as possible only those where the column is the first column in the index.

  
  INDEX_COMBINE
  
  The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes.
  
  INDEX_JOIN
  
  The INDEX_JOIN hint explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.
  
  APPEND - direct-path mode (enable parallel mode) 

  The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode.

  In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.
  
  NOAPPEND - conventional path mode (disable parallel mode)

  The NOAPPEND hint enables conventional INSERT by disabling parallel mode for the duration of the INSERT statement. (Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).

  CACHE
  
  The CACHE hint specifies that the blocks retrieved for the table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.

• Optimization Goals and Approaches (2)	Access Path Hints (17)	Other (20)	Join Operation (7)
  ALL_ROWS FIRST_ROWS RULE	CLUSTER FULL HASH INDEX NO_INDEX INDEX_ASC INDEX_DESC INDEX_COMBINE INDEX_JOIN INDEX_FFS INDEX_SS INDEX_SS_ASC INDEX_SS_DESC NATIVE_FULL_OUTER_JOIN NO_NATIVE_FULL_OUTER_JOIN NO_INDEX_FFS NO_INDEX_SS	APPEND NOAPPEND CACHE NOCACHE CURSOR_SHARING_EXACT DRIVING_SITE DYNAMIC_SAMPLING MODEL_MIN_ANALYSIS MONITOR NO_MONITOR OPT_PARAM PUSH_PRED NO_PUSH_PRED PUSH_SUBQ NO_PUSH_SUBQ PX_JOIN_FILTER NO_PX_JOIN_FILTER QB_NAME RESULT_CACHE NO_RESULT_CACHE	USE_HASH NO_USE_HASH USE_MERGE NO_USE_MERGE USE_NL USE_NL_WITH_INDEX NO_USE_NL
  Join Order (2)	Query Transformation (13)	XML (2)	Parallel Execution (5)
  ORDERED LEADING	FACT NO_FACT MERGE NO_MERGE NO_EXPAND USE_CONCAT REWRITE NO_REWRITE NOREWRITE* UNNEST NO_UNNEST STAR_TRANSFORMATION NO_STAR_TRANSFORMATION NO_QUERY_TRANSFORMATION	NO_XMLINDEX_REWRITE NO_XML_QUERY_REWRITE	PARALLEL NOPARALLEL* NO_PARALLEL PARALLEL_INDEX NO_PARALLEL_INDEX NOPARALLEL_INDEX* PQ_DISTRIBUTE

BIND Variables

• Oracle bind variables

11g Oracle's Buffer Cache

• Buffercache
• Why a 99 percent buffer cache ratio is not ok

BIND Variable PEEKING 11g

• Bind variable peeking drives me nuts
• Bind variable peeking in 11g
• Bind variable peeking execution plan inefficiency
• Bind Variable Peeking Tips
• Bind Variable Peeking by setting the _OPTIM_PEEK_USER_BINDS parameter

ADAPTIVE CURSOR SHARING IN 11G

• Oracle Database 11g Adaptive Cursor Sharing
• Adaptive cursor sharing in 11g
• Adaptive Cursor Sharing in Oracle Database 11g Release 1

• 11g Adaptive Cursor Sharing

SHARING, BIND Variable PEEKING and HISTOGRAMS

• Relationship between cursor sharing bind variable peeking and histograms
• Stabilize oracle 10gs bind peeking behaviour by cutting histograms

Links

• Tuning PL/SQL Applications for Performance
• Automatic SQL Tuning in Oracle Database 11g Release 1

Query to check long running queries

• select vlo.*,vs.* from v$session_longops vlo, v$sql vs where vlo.sql_hash_value = vs.hash_value and vlo.sql_address = vs.address and vs.child_number=0 and vlo.sofar/vlo.totalwork*100<>100

SCHEDULING

• DBMS_JOB
• DBMS_SCHEDULER

TRIGGERS

• 12 TYPES of triggers before, after, row level, statement level, system, non system
• Call Sequence of Execution of triggers
• AUTONOMOUS TRANSACTION  in triggers
• INSTEAD OF TRIGGER = trigger on view
• Create trigger to add user and source code to log table
• Call PROC in triggers to get over max trigger size of 100K
• How to avoid mutating trigger error change it to a statement level trigger, use view, use GTT
• We can have a max of 12 triggers on a table

PARTITIONING

• Partitions (how to specify)
• You cannot update partition key
• Horizontal vs Vertical partitioning, range, list, hash
• Sub partitions
• Global Indexes
• Local Indexes (Always create local indexes for partitioned tables)
• Global vs Local partitioned index
• Object partitioning in Oracle
• Partition by analytical function
• Partitioned tables and indexes
• Partition Pruning
• Partition pruning improves response time
• Partition pruning tips
• Beginning with Oracle 11g, partition pruning uses bloom filtering instead of subquery pruning.  A bloom filter essentially tests if an element is a member of a set or not. In deciding whether to use partition pruning, a bloom filter uses partition pruning whenever a partitioned object is detected in the SQL statement. This enhances the performance of partition pruning because bloom filtering does not require additional resources. Also, it is not evaluated as a cost based decision. Instead, bloom filtering is constantly active as well as automatically activated for any join with a partitioned object. This transparent enhancement improves the performance of partition pruning.
• Managing Partitioned Tables and Indexes in Oracle 10g
• ALTER TABLE Maintenance Operations for Table Partitions

  Maintenance Operation	Range	Hash	List	Composite: Range/Hash	Composite: Range/List
  Adding Partitions	ADD PARTITION	ADD PARTITION	ADD PARTITION	ADD PARTITION MODIFY PARTITION ... ADD SUBPARTITION	ADD PARTITION MODIFY PARTITION ... ADD SUBPARTITION
  Coalescing Partitions	n/a	COALESCE PARTITION	n/a	MODIFY PARTITION ... COALESCE SUBPARTITION	n/a
  Dropping Partitions	DROP PARTITION	n/a	DROP PARTITION	DROP PARTITION	DROP PARTITION DROP SUBPARTITION
  Exchanging Partitions	EXCHANGE PARTITION	EXCHANGE PARTITION	EXCHANGE PARTITION	EXCHANGE PARTITION EXCHANGE SUBPARTITION	EXCHANGE PARTITION EXCHANGE SUBPARTITION
  Merging Partitions	MERGE PARTITIONS	n/a	MERGE PARTITIONS	MERGE PARTITIONS	MERGE PARTITIONS MERGE SUBPARTITIONS
  Modifying Default Attributes	MODIFY DEFAULT ATTRIBUTES	MODIFY DEFAULT ATTRIBUTES	MODIFY DEFAULT ATTRIBUTES	MODIFY DEFAULT ATTRIBUTES MODIFY DEFAULT ATTRIBUTES FOR PARTITION	MODIFY DEFAULT ATTRIBUTES MODIFY DEFAULT ATTRIBUTES FOR PARTITION
  Modifying Real Attributes of Partitions	MODIFY PARTITION	MODIFY PARTITION	MODIFY PARTITION	MODIFY PARTITION MODIFY SUBPARTITION	MODIFY PARTITION MODIFY SUBPARTITION
  Modifying List Partitions: Adding Values	n/a	n/a	MODIFY PARTITION...ADD VALUES	n/a	MODIFY SUBPARTITION ... ADD VALUES
  Modifying List Partitions: Dropping Values	n/a	n/a	MODIFY PARTITION...DROP VALUES	n/a	MODIFY SUBPARTITION ... DROP VALUES
  Modifying a Subpartition Template	n/a	n/a	n/a	SET SUBPARTITION TEMPLATE	SET SUBPARTITION TEMPLATE
  Moving Partitions	MOVE PARTITION	MOVE PARTITION	MOVE PARTITION	MOVE SUBPARTITION	MOVE SUBPARTITION
  Renaming Partitions	RENAME PARTITION	RENAME PARTITION	RENAME PARTITION	RENAME PARTITION RENAME SUBPARTITION	RENAME PARTITION RENAME SUBPARTITION
  SplittingPartitions	SPLIT PARTITION	n/a	SPLIT PARTITION	SPLIT PARTITION	SPLIT PARTITION SPLIT SUBPARTITION
  Truncating Partitions	TRUNCATE PARTITION	TRUNCATE PARTITION	TRUNCATE PARTITION	TRUNCATE PARTITION TRUNCATE SUBPARTITION	TRUNCATE PARTITION TRUNCATE SUBPARTITION

EXCEPTION HANDLING

• Common Errors : http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm
• Exception Types
• Named like Oracle Predefined Error List
• TOO_MANY_ROWS
• NO_DATA_FOUND
• DUP_VAL_ON_INDEX
• VALUE_ERROR
• ZERO_DIVIDE
• Unnamed
• User Defined
• SQLCODE and SQLERRM
• BULK COLLECT ..... SAVE EXCEPTIONS in which table it is stored. Can you specify your own table ? No
• RAISE_APPLICATION_ERROR
• DBMS_UTILITY.FORMAT_ERROR_STACK
• Links
• Handling PL/SQL Errors

ANALYTICAL FUNCTIONS

• CONNECT BY START WITH ... PARTITION BY SYNTAX
• COLASE
• LEAD
• LAG
• CUBE vs ROLLUP
• Show rows in a single column
• http://www.oracle-base.com/articles/misc/analytic-functions.php

• Linear Regression Functions
  	20.1.Introduction( 1 )	20.14.Stats_Mw_Test( 4 )
  	20.2.REGR_AVGX( 2 )	20.15.STATS_ONE_WAY_ANOVA( 8 )
  	20.3.REGR_AVGY( 1 )	20.16.STATS_T_TEST_INDEP( 4 )
  	20.4.REGR_COUNT( 2 )	20.17.STATS_T_TEST_INDEPU( 4 )
  	20.5.REGR_INTERCEPT( 1 )	20.18.STATS_T_TEST_ONE( 4 )
  	20.6.REGR_R2( 1 )	20.19.STATS_T_TEST_PAIRED( 4 )
  	20.7.REGR_SLOPE( 1 )	20.20.STATS_WSR_TEST( 4 )
  	20.8.REGR_SXX( 1 )	20.21.STDDEV( 1 )
  	20.9.REGR_SXY( 1 )	20.22.STDDEV_POP( 1 )
  	20.10.REGR_SYY( 2 )	20.23.STDDEV_SAMP( 1 )
  	20.11.STATS_CROSSTAB( 7 )	20.24.VARIANCE( 1 )
  	20.12.STATS_F_TEST( 5 )	20.25.VAR_POP( 1 )
  	20.13.STATS_MODE( 1 )	20.26.VAR_SAMP( 1 )

   

  Statistical Functions
  	19.1.AVG( 1 )	19.5.COVAR_POP( 1 )
  	19.2.Corr( 1 )	19.6.COVAR_SAMP( 1 )
  	19.3.Corr_K( 3 )	19.7.Cume_Dist( 1 )
  	19.4.Corr_S( 3 )	19.8.MEDIAN( 1 )

Analytical Functions 16.1.Introduction( 6 ) 16.18.NTILE( 7 )  16.2.ROWNUM( 4 ) 16.19.Nulls First Last( 9 )  16.3.ROW_NUMBER( 5 ) 16.20.OVER( 11 )  16.4.RANK( 7 ) 16.21.PERCENTILE_CONT( 3 )  16.5.DENSE_RANK( 8 ) 16.22.PERCENTILE_DISC( 1 )  16.6.PERCENT_RANK( 4 ) 16.23.RANGE( 4 )  16.7.PARTITION_BY( 7 ) 16.24.ROLLUP( 12 )  16.8.CUBE( 8 ) 16.25.MODEL( 1 )  16.9.CUME_DIST( 4 ) 16.26.SPREADSHEET( 2 )  16.10.FIRST_VALUE( 1 ) 16.27.Top( 1 )  16.11.LAST_VALUE( 1 ) 16.28.UNBOUNDED( 2 )  16.12.Group by( 1 ) 16.29.Window Functions( 11 )  16.13.GROUPING( 12 ) 16.30.HAVING( 1 )  16.14.GROUPING_ID( 3 ) 16.31.Order( 1 )  16.15.First( 1 ) 16.32.Table Joins( 2 )  16.16.Last( 1 ) 16.33.Where( 1 )  16.17.LEAD LAG( 5 )
	System Packages 31.1.DBMS_ALERT( 7 )  31.22.dbms_rls( 5 )  31.2.dbms_application_info( 7 )  31.23.dbms_rowid( 11 )  31.3.DBMS_AQ( 1 )  31.24.dbms_session( 4 )  31.4.dbms_aqadm( 2 )  31.25.dbms_space( 2 )  31.5.dbms_crypto( 7 )  31.26.DBMS_SQL( 20 )  31.6.DBMS_DB_VERSION( 1 )  31.27.DBMS_STATS( 10 )  31.7.DBMS_DDL( 1 )  31.28.DBMS_TRANSACTION( 1 )  31.8.DBMS_DEBUG( 1 )  31.29.DBMS_TYPES( 1 )  31.9.dbms_fga( 1 )  31.30.dbms_utility( 13 )  31.10.dbms_java( 1 )  31.31.dbms_wm( 1 )  31.11.DBMS_JOB( 8 )  31.32.dbms_xmlschema( 1 )  31.12.DBMS_LOB( 9 )  31.33.File Read with UTL_FILE( 4 )  31.13.dbms_lock( 2 )  31.34.owa_util( 1 )  31.14.dbms_logmnr( 4 )  31.35.File Write with UTL_FILE( 4 )  31.15.dbms_metadata( 2 )  31.36.system_privilege_map( 1 )  31.16.dbms_obfuscation_toolkit( 3 )  31.37.HTP( 11 )  31.17.DBMS_OUTPUT( 11 )  31.38.UTL_FILE( 9 )  31.18.DBMS_PIPE( 11 )  31.39.utl_raw( 6 )  31.19.dbms_preprocessor( 2 )  31.40.utl_smtp( 2 )  31.20.DBMS_PROFILER( 7 )  31.41.utl_tcp( 1 )  31.21.DBMS_RANDOM( 2 )

TRANSPOSING DATA

• NORMALIZATION - UNION
• DENORMALIZATION - CASE
• LISTAGG

ORACLE DBMS_**** and Other UTILITIES

• Built in packages
• DBMS_TRACE, DBMS_PROFILER, DBMS_SUPPORT, DBMS_MONITOR
• WRAP command encrypts PL/SQL code.
• ADIDENT utility is used to find the version of any file
• DBMS_ALERT - Package DBMS_ALERT lets you use database triggers to alert an application when specific database values change. The alerts are transaction based and asynchronous (that is, they operate independently of any timing mechanism). For example, a company might use this package to update the value of its investment portfolio as new stock and bond quotes arrive.
• DBMS_PIPE - Package DBMS_PIPE allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures PACK_MESSAGE and SEND_MESSAGE to pack a message into a pipe, then send it to another session in the same instance or to a waiting application such as a UNIX program.
  At the other end of the pipe, you can use the procedures RECEIVE_MESSAGE and UNPACK_MESSAGE to receive and unpack (read) the message. Named pipes are useful in many ways. For example, you can write a C program to collect data, then send it through pipes to stored procedures in an Oracle database.
• UTL_FILE -Package UTL_FILE lets PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations.
  When you want to read or write a text file, you call the function FOPEN, which returns a file handle for use in subsequent procedure calls. For example, the procedure PUT_LINE writes a text string and line terminator to an open file, and the procedure GET_LINE reads a line of text from an open file into an output buffer.

GRANTS AND PRIVILEGES

• Grant granter rights (WITH GRANT OPTION)
• Roles help grant privileges to a group of users

CONSTRAINTS (alter table enable /disable constraint)

• DISABLE CONSTRAINT
• DISABLE CONSTRAINT NO CHECK
• DISABLE ALL CONSTRAINT
• ENABLE NOVALIDATE
• We CANNOT change not DIFFERABLE CONSTRAINT

COMPILATION

• When will an object become invalid 
• Conditional Compilation in 11g

Audit

• Select d.name,l.sessions_current,s.value,l.sessions_highwater from v$database d, v$sysstats s, v$license l  

Debugging and code maintainability :

• Add RETURN or EXIT till the point you want to run
• Prefer to use GTT over Collections
• Need to find missing records in join use outer join in ANSI syntax

Impact Analysis and Dependencies

• all_dependencies or dba_dependencies
• Table for Indirect Dependencies

DBLINK what is a remote database a node

Shutdown / Startup Options

• Normal
• Immediate
• Regular

Standards

• Database Object Naming Rules : Max Limit is 30 Bytes/ Chars except DB Name which is 8 Bytes and DB Links which are 128 Bytes

Puzzles/Tricks

• Num to word
• to_char(to_date(col,'j'),'jsp')
• Convert multiple rows to a single column
• SELECT SUBSTR (SYS_CONNECT_BY_PATH (NAME , ','), 2) FRUITS_LIST
  FROM (SELECT NAME , ROW_NUMBER () OVER (ORDER BY NAME ) RN,
  COUNT (*) OVER () CNT
  FROM FRUITS)
  WHERE RN = CNT
  START WITH RN = 1
  CONNECT BY RN = PRIOR RN + 1;
• Oracle tips tricks
• select split('123,45,6,7',1,',') from dual

Unix and Oracle

• Common Unix Commands & Usage
• ksh shell script 'helloworld' after 25 secs

Additional Reference :

• Oracle 10g interview questions and answers
• Quick tricks with oracle database
• http://careerride.com/test.aspx?type=Oracle
• http://beginner-sql-tutorial.com/oracle-functions.htm
• http://www.oracle-dba-online.com/sql/oracle_sql_tutorial.htm
• http://docs.oracle.com/cd/E11882_01/server.112/e26088.pdf
• http://www.careerride.com/Oracle-Interview-Questions.aspx
• http://techpreparation.com/oracle-interview-questions-answers1.htm
• http://www.databasejournal.com/features/oracle/article.php/10893_3112661_2/Oracle-Technical-Interview-Questions-Answered---Part1.htm
• http://www.databasejournal.com/features/oracle/article.php/3114381/Oracle-Technical-Interview-Questions-Answered---Part2.htm
• http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql

Links to popular blogs

• Oracle SQL Join Factorization Transformation (JFT) Tips