Schema Management Approaches:

Different database developers/database administrators follow different approaches for migrating schemas from one version to another.

In DDL approach, ALTER script is used to modify the structures and creating new objects etc. and an UPDATE script is used to update/re-set the data. There is no destination schema as changes are applied directly to the Source Schema itself and that Schema is upgraded from one version to the next.

While in the other approach ETLM (Extract, Transform, Load and Modify) script is used to migrate from the Source Schema to the Destination Schema.

In ETLM approach the configuration tables are populated in the destination schema and indexes are dropped for the tables in it. The tables are made to be in nologging mode. Then direct path insert statement is used in parallel mode to load the data into the tables. Once your data is loaded then indexes are created in nologging and parallel mode.

DDL Approach:

Most people prefer DDL approach over ETLM approach as ETLM approach requires more space and more development time to put together the conversion script and the testing effort.

Suppose you chose to follow the DDL approach. Consider a scenario where your table contains more than 100 million records and you want to add a new NOT NULL constraint column to that table. 

This used to be a cumbersome task in Oracle releases prior to 11g where the flow would be like first you issue alter statement, then column was added to table, then records were updated with default value and at last the column was marked as NOT NULL. Based on the size of table a large amount of undo and redo operations would occur and lots of time will be spent on it. 

BIG_TABLE:

Oracle 11g has made the life of database developers/database administrators quite easy by eliminating the burdensome DDL approach. What you just have to do is to issue a command as follows

ALTER TABLE BIG_TABLE ADD myNewColumn varchar2(10)  DEFAULT ‘myTestValue' NOT NULL;

The beauty of above command is that it does not issue an update to the existing records in the table. If new records are added to the table then they will get the default value of myTestValue. However if you perform a query that selects older data as well then Oracle will derive the value out of the data dictionary and present it. 

This is useful in the conversion, migration and upgrade projects where one uses DDL approach over the ETLM approach as issuing an alter command will no longer add the penalty of redo and undo generation. 

DDL_LOCK_TIMEOUT:

DDL_LOCK_TIMEOUT is another exciting schema management feature in Oracle 11g. In Oracle releases prior to Oracle 11g you would get ORA-00054 error if you execute the above alter command where somebody is holding a lock on that table. Thus you can not hold exclusive lock on the table for completing alter command. The result was that we kept on trying or find and terminate the sessions that are holding locks. 

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Again Oracle 11g has blessed us with the powerful DDL_LOCK_TIMEOUT setting. ORA-00054 is similar to SELECT FOR UPDATE WAIT N feature whereby if some session can not get going due to a lock as in above case then it will try the DDL operation for the number of seconds specified by DDL_LOCK_TIMEOUT setting. 

SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 30;

You can also set DDL_LOCK_TIMEOUT at the system level by issuing below command. It is recommended to set it at the system level as in that case all the sessions will get same setting. You can override DDL_LOCK_TIMEOUT setting at the session level.

SQL> ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 30; Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Database Tuning Guide for Third Party Applications
   Top Free Tools to prevent SQL Injections!!
   Oracle 11g Enhanced Optimizer Statistics Maintenance!!
   Efficiently Tracking Oracle Long-Running Queries!!