Systems Engineering and RDBMS

Two new management features in Oracle 11g

Posted by decipherinfosys on January 23, 2008

When doing migration of the schema from one version to the other, there are different modus operandi that are adopted by different DBA’s/Database Developers. You can read more on two common approaches in one our previous posts over here.  In the event of a DBA/developer chosing to use the DDLs, say that you have added a new NOT NULL constraint column to an existing table in the new release and that column has a default value associated to it.  And suppose that that table has over 100 million records in it.  Prior to Oracle 11g, when you issue such an alter statement, it would need to add the column, update the existing records with the default value and mark the column as NOT NULL.  The time taken to do so would be huge given the size of the table and given the fact that a large amount of undo and redo will be generated.  This is no longer an issue in Oracle 11g.  In 11g, when you issue such a command, say:

Alter table BIG_TABLE add New_Column varchar2(10)  default ‘test’ not null;

This statement will not issue an update to the existing records in the table.  New records will have their values set to the default value of “test” and when a query comes along that selects an older record, Oracle then derives that value out of the data dictionary and presents it.  So, bottom line is that you will no longer incur the penalty of redo and undo generation when issuing such alter commands.  This should help greatly in the conversion/migration/upgrade projects where one uses the DDL approach over the ETLM approach.

Another neat feature for schema management that has been introduced in 11g is the DDL_LOCK_TIMEOUT option.  This is something that the development DBAs/DB Developers will really appreciate.  Prior to Oracle 11g, if say you are executing the same alter statement as was shown above and if someone is holding a lock on that table, then you will get the following error:

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

What this means is that you cannot get an exclusive lock on the table in order to complete the alter command.  The only option prior to Oracle 11g was to keep on trying or to find the sessions holding the locks and terminate them.  In 11g, one can use the ddl_lock_timeout session/system setting.  Example:

SQL> Alter session set ddl_lock_timeout = 30;

Now, when this particular session encounters a lock that prevents it from getting the exclusive lock, instead of timing out, it will try the DDL operation for 30 seconds (similar to “select for update wait N” feature).  This can also be issued at the system level:

SQL> Alter system set ddl_lock_timeout = 30;

Setting it at the system level would be a better choice so that all the sessions get the same setting.  You can of course, override it at the session level.

Sorry, the comment form is closed at this time.

%d bloggers like this: