Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Dropping Table Columns

Oracle Tips by Burleson

Prior to Oracle8i, if you created a table with a column you later found you didn’t need, you had to rebuild the entire table to get rid of it. Not so in Oracle8i and Oracle. Finally, after years of users requesting it, Oracle has added the drop column capability to the ALTER TABLE command. The new ALTER TABLE drop column clause has the following options:

SET UNUSED [COLUMN column_name]|[(column_name(s))]

DROP [COLUMN column_name]|[(column_name(s))]
    [CHECKPOINT integer]


The DROP clause will drop the column(s), dependent constraints, and triggers specified in the command, even if those items also are used with other columns. So be careful to fully understand the implications of using the DROP clause before you use it.

The SET UNUSED clause allows you to deactivate the column without dropping it. This allows a preliminary test of whether the column is really able to be dropped without damaging existing data. It also allows you to cascade the action to dependent constraints.

The INVALIDATE option allows you to invalidate all directly and indirectly dependent objects such as procedures, functions, and so on.

The DROP clause allows either a drop of an active column or columns or just a drop of columns previously marked as unused. As with the UNUSED clause, constraints can be cascaded (values are deleted) and objects invalidated. The DROP clause also allows you to force a checkpoint. Note that a drop of any column in a table with columns marked as UNUSED will result in a drop of all UNUSED columns, as well as the explicitly stated column.

Reclaiming a Column Marked as UNUSED

Oops! You marked the wrong column as UNUSED and now you find that you can’t go back…or can you? During the testing of this feature, I found a method to reclaim a column marked as UNUSED. Once you DROP a column that has been marked as UNUSED, it is gone for good, but up to the point where it is actually dropped, it can be reclaimed. This is how the technique works (I suggest practicing before doing the reclaim on a production table):

The col$ has obj#, col#, name and property fields, which tie into the columns for each table where obj# will correspond to the obj# in the tab$ table. When a column is marked unused, the col# is set to 0, the name is set to a system-generated name, and the property is set to 32800 from its initial value of 0 (for a normal, nontype column). In the tab$ table, the number of columns is decremented by one in the cols column. The obj$ table stores the link between the object name and owner and the obj#; you then use that obj# to pull relevant data from the tab$ and col$ tables.

In order to reverse the unused state, reset the col# value to its original value, reset the name column to the proper name, and reset the property to 0. In the tab$ table, reset the cols column to the appropriate value.

You will have to flush the shared pool, or even restart to flush the object caches and dictionary caches, to see the column again. To test this process, perform the following steps:

1.      Create a small table with two to three columns

2.      Look at the entries for obj$, tab$, and col$ for this table, noting the values for COLS in tab$ and COL#, INTCOL#, PROPERTY, and NAME in col$ (from SYS or INTERNAL user).

3.      Add data to the table, and commit.

4.      Select from the table to confirm the data and columns.

5.      Use ALTER TABLE SET UNUSED to mark a column unused.

6.      Select from the table to confirm the column is unavailable.

7.      Log in as SYS or INTERNAL, and check tab$ and col$ as before.

8.      Update tab$ and col$ to before the ALTER conditions, and commit.

9.      Flush the shared pool to get rid of the postcondition SELECT statement parse.

Issue a SELECT against the table to confirm the column reappeared.

Note: Sometimes you may need to shut down and restart before the column will reappear.

That should do it. Of course, if you actually drop the column, you can't reclaim it. And remember, dropping any column in a table will result in loss of all columns marked UNUSED, whether mentioned in the DROP or not.

Table Statistics and Validity: The ANALYZE Command  

To help a DBA determine whether a table’s data and indexes have integrity, and to calculate the statistics used by the cost-based optimizer, all versions of Oracle since version 7 provide the ANALYZE command, which is used to analyze the structure of a table and its indexes. The schema object to be analyzed must be in your own schema or you must have the ANALYZE ANY system privilege.

If you want to list chained rows of a table or cluster into a list table, the list table must be in your own schema and you must have INSERT privilege on the list table or the INSERT ANY TABLE system privilege.

If you want to validate a partitioned table, you must have the INSERT privilege on the table, into which you list analyzed rowids, or you must have the INSERT ANY TABLE system privilege.

Note: The Oracle syntax for ANALYZE is contained in the SQL reference manual on the Oracle technet website.       

Some options for the ANALYZE command require a table. This table is named using the INTO clause. Usually, this specifies a table into which Oracle lists the migrated and chained rows. If you omit schema, Oracle assumes the list table is in your own schema. If you omit this clause altogether, Oracle assumes that the table is named CHAINED_ROWS. The script used to create this table is UTLCHAIN.SQL. The list table must be on your local database.

The structure under Oracle for the table is:

CREATE TABLE chained_rows (
owner_name          varchar2(30),
table_name          varchar2(30),
cluster_name        varchar2(30),
partition_name      varchar2(30),
head_rowid          rowid,
analyze_timestamp     date);

To analyze index-only tables, you must create a separate chained-rows table for each index-only table created to accommodate the primary key storage of index-only tables. Use the SQL scripts DBMSIOTC.SQL and PRVTIOTC.PLB to define the DBMS_IOT package. The tables themselves are built with a call to the BUILD_ CHAIN_ROWS_TABLE(owner VARCHAR2, iot_name VARCAHR2, chainrow_table_ name VARCHAR2) procedure. Execution of this procedure creates an IOT_CHAINED_ ROWS table for an index-only table. The IOT_CHAINED_ROWS table has the structure:

CREATE TABLE iot_chained_rows (
owner_name               varchar2(30),
table_name               varchar2(30),
cluster_name             varchar2(30),
partition_name           varchar2(30),
head_rowid               rowid,
timestamp                date,
test1           varchar2(6) <- This is the primary key
);                           from the table being analyzed

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter