Add column in 11g

DDL Wait Option

Jill the DBA at Acme Retailers is trying to alter the table called SALES to add a column, TAX_CODE. It's pretty routine stuff; she issues the following SQL statement:

SQL> alter table sales add (tax_code varchar2(10));

But instead of getting something like "Table altered", she gets:

alter table sales add (tax_code varchar2(10))
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The error message says it all: the table is being used right now, probably by a transaction, so getting an exclusive lock on the table may be next to impossible. Of course, the rows of the table are not locked forever. When sessions perform. commit the locks on those rows are released, but before that unlock period gets very far, other sessions may update some other rows of the table—and thus the slice of time to get the exclusive lock on the table vanishes. In a typical business environment, the window for locking the table exclusively does open periodically, but the DBA may not be able to perform. the alter command exactly at that time.

Of course, Jill can just keep on typing the same command over and over again until she gets an exclusive lock—or goes nuts, whichever comes first.

In Oracle Database 11g, Jill has a better option: the DDL Wait option. She issues:

SQL> alter session set ddl_lock_timeout = 10;
 
Session altered.

Now, when a DDL statement in the session does not get the exclusive lock, it will not error out. Instead, it will wait for 10 seconds. In that 10 seconds, it continually re-tries the DDL operation until it's successful or the time expires, whichever comes first. When she issues:

SQL> alter table sales add (tax_code varchar2(10));

the statement hangs and does not error out. So, instead of Jill trying repeatedly to get the elusive fraction of time when the exclusive lock is available, she outsources repeated trials to Oracle Database 11 g, somewhat like a telephone programmed to re-try a busy number.

Now, Jill likes this feature so much that she shares it with all the other DBAs. As everyone faces the same issue when altering a table during busy system time, they all find this new feature very helpful. So Jill wonders, can this behavior. be default so that they don't need to issue the ALTER SESSION statement every time?

Yes, it can. If you issue ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10, the sessions automatically waits for that time period during DDL operations. Just like any other ALTER SYSTEM statement, this can be overridden by an ALTER SESSION statement.

Adding Columns with a Default Value

Although happy with this feature alone, Jill ponders another issue somewhat related to the first one. She wants to add the column TAX_CODE but it has to be NOT NULL. Obviously when she adds a not null column to a non-empty table, she has to also specify a default value, 'XX'. So she writes the following SQL:

alter table sales add tax_code varchar2(20) default 'XX' not null;

But she stops there. The table SALES is huge, about 400 million rows. She knows that when she issues the statement, Oracle will add the column alright but will update the value 'XX' in all rows before returning control back to her. Updating 400 million rows will not only take a very long time, it will also fill up the undo segments, generate a large amount of redo, and create massive performance overhead. So Jill has to ask for a "quiet period"—an outage—to make this change. But is there a better approach in Oracle Database 11 g?

There is. The above statement will not issue an update to all the records of the table. Well, that's not a problem for new records where the value of the column will be automatically set to 'XX', but when the user selects this column for an existing record, that will return NULL, right?

Wrong, actually. When a user selects the column for an existing record, Oracle gets the fact about the default value from the data dictionary and returns it to the user. So, you kill two birds with one stone: you can define a new column as not null and with a default value and still not incur any penalty for redo and undo generation. Nice.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13790829/viewspace-628670/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13790829/viewspace-628670/