How to reuse the Unused columns again in oracle DB

oracle10g sql-drop
Mohan picture Mohan · Dec 1, 2012 · Viewed 21k times · Source

I have a table with 4 column, ID, NAME,AGE and COUNTRY. For some time being purpose i have set my AGE column as unused by below command

alter table Personal set unused column AGE;

Now i want to use the same column AGE again, How to do this in Oracle(10g).

and dropping a column and setting a column to Unused which is best option. Pls guide me.

Answer

jachguate picture jachguate · Dec 1, 2012

You cannot reuse a unused column. The only possible action on the column is to remove it from the table.

But you can add a new column with the same name, even without removing the unused column.

From the documentation

The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

And this other (emphasis mine):

Marking Columns Unused

If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement. This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not 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.

Related questions

How to find the privileges and roles granted to a user in Oracle?

I am using Linux, Oracle10g. I have created one user called test. and granted create session and select any dictionary permission to the same user. i also granted sysdba and sysoper roles to the same users. Now i want …

Read More
Checking oracle sid and database name

I want to check SID and current database name. I am using following query for checking oracle SID select instance from v$thread; but table or view does not exist error is coming. I am using following query for checking …

Read More
CASE .. WHEN expression in Oracle SQL

I have the table with 1 column and has following data Status a1 i t a2 a3 I want to display the following result in my select query Status| STATUSTEXT a1 | Active i | Inactive t | Terminated a2 | Active a3 | Active One …

Read More