Renaming a Column- Tables and Constraints

There are a couple of reasons to rename a column.

Sometimes, requirements change, and you may want to modify the column name to better reflect what the column is used for.

If you are planning to drop a column, it does not hurt to rename the column first to better determine whether any users or applications are accessing it.

Use the ALTER TABLE … RENAME statement to rename a column:

SQL> alter table inv rename column inv_count to inv_amt;

Dropping a Column

Tables sometimes end up having columns that are never used. This may be because the initial requirements changed or were inaccurate. If you have a table that contains an unused column, you should consider dropping it. If you leave an unused column in a table, you may run into issues with future DBAs not knowing what the column is used for, and the column can potentially consume space unnecessarily.

Before you drop a column, I recommend that you first rename it. Doing so gives you an opportunity to determine whether any users or applications are using the column. After you are confident the column is not being used, first make a backup of the table, using Data Pump export, and then drop the column. These strategies provide you with options if you drop a column and then subsequently realize that it is needed.

To drop a column, use the ALTER TABLE … DROP statement:

SQL> alter table inv drop (inv_desc);

Be aware that the DROP operation may take some time if the table from which you are removing the column contains a large amount of data. This time lag may result in the delay of transactions while the table is being modified (because the ALTER TABLE statement locks the table). In scenarios such as this, you may want to first mark the column unused and then later drop it, when you have a maintenance window:

SQL> alter table inv set unused (inv_desc);

When you mark a column unused, it no longer shows up in the table description. The SET UNUSED clause does not incur the overhead associated with dropping the column. This technique allows you to quickly stop the column from being seen or used by SQL queries or applications. Any query that attempts to access an unused column receives the following error:

ORA-00904: … invalid identifier

You can later drop any unused columns when you’ve scheduled some downtime for the application. Use the DROP UNUSED clause to remove any columns marked UNUSED.

SQL> alter table inv drop unused columns;