Recursive sub-query factoring is available in Oracle 11g R2.

Partitioning is an extra cost option and only available for the Enterprise Edition.

• Never use DDL or DML against tables created by dbms_aqadm.create_queue_table. Only use dbms_aqadm and dbms_aq to work with these tables. Oracle may make several supporting tables, indexes, etc that you will not be aware of. Manually running DDL or DML against the table may lead you to a scenario where Oracle Support will need you to drop and recreate the table and queues to resolve the situation.

• It's strongly recommended you do not use dbms_aq.forever for a wait option. This has caused issues in the past as Oracle may start scheduling an excessive number of worker jobs to work the queues that are unnecessary (see Oracle Doc ID 2001165.1).

• It's recommended you do not set the AQ_TM_PROCESSES parameter in version 10.1 and later. Especially avoid setting this to zero since this will disable the QMON background job that is necessary to maintain the queues. You can reset this value to the Oracle default using the following command and restarting the database. alter system reset aq_tm_processes scope=spfile sid='*';

Typical use cases for autonomous transaction are.

1. For building any kind of logging framework like the error logging framework explained in the above example.
2. For auditing DML operations in triggers on tables irrespective of the final status of the transaction (COMMIT or ROLLBACK).

Since they are unnamed, anonymous blocks cannot be referenced by other program units.

level clause is responsible for generating N number of dummy records based on some specific condition.

DUAL table has one column DUMMY, defined to be VARCHAR2(1) and only one row with a value x.

DUAL table is automatically created in SYS schema when database is created. You can access it from any schema.

You can not change DUAL table.

You can use DUAL table to call any function from SQL statement. It is useful because it has only one row and oracle optimizer knows everything about it.

NULL can't appear in columns restricted by a PRIMARY KEY or a NOT NULL constraint. (Exception is a new constraint with NOVALIDATE clause)

Some important remarks:

1. Never use string concatenation to add values to query, use parameters instead. This is wrong:

   execute immediate 'select value from my_table where id = ' || 
        id_valiable into result_variable;
   

   And this is right:

   execute immediate 'select value from my_table where id = :P '
       using id_valiable into result_variable;
   

   There are two reasons for this. The first is the security. String concatenation allows to make SQL injection. In the query below, if a variable will contain value 1 or 1 = 1, the UPDATE statement will update all lines in the table:

   execute immediate 'update my_table set value = ''I have bad news for you'' where id = ' || id;
   

   The second reason is performance. Oracle will parse query without parameters every time when it executes, while query with parameter will be parsed only once in the session.

2. Note, that when the database engine executes a DDL statement, it executes implicit commit before.