Oracle Unused Columns – New Feature since 8i

If you ever find yourself in a situation where you need to drop columns from a very large table, you will know that dropping a column acquires a DML lock on the table. Consider that you are dropping a column from a wide table roughly 10gb in size on a critical table on a core application. This will require several hours downtime if done in the traditional way.

However, by using a feature called unused columns, the downtime can be negligible and no downtime is required, and the work which would have required the downtime can be completed in a matter of seconds, regardless of how big the table is.

If using traditional syntax, the following would be done to drop 2 columns:

--Traditional method
ALTER TABLE TABLEA DROP COLUMN ("COLUMN_A","COLUMN_B");

This would lock the table while the table was dropped, lets say duration of 1 hour.

The change is here, by using Oracle unused column. You set the columns to be unused, they don’t get displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.

ALTER TABLE TABLEA SET UNUSED (
"COLUMN_A",
"COLUMN_B");

So the core of the work to remove the column from ‘live’ has been done. According to the user, the column is no longer there, it can no longer be accessed or seen in views etc. The only task which remains is to reclaim the space.

To be run server as this will take few hours. Also while this is being run no DDL can be done on table, i.e. add/delete columns. Checkpoint 500,000 cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

ALTER TABLE TABLEA DROP UNUSED COLUMNS CHECKPOINT 500000;

To find out which columns are currently set to unused in a database, run:

select * from DBA_UNUSED_COL_TABS;

OWNER               TABLE_NAME       COUNT
------------------- ---------------- ----------
SCHEMA_A            TABLEA           1
SCHEMA_A            TABLED           1
SCHEMA_B            TABLEB           22
SCHEMA_B            TABLEC           16

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: