How to reuse a column marked "unused" in Oracle?

I have a table with multiple columns, for example, ID, Nombre, Edadand Pais. Of these, I have marked column Edad as unused with the following sentence:

alter table Persona set unused column AGE;

Now, I want to use column Edad again, how can I do it in Oracle (10g)?

Also, what's better, delete a column, or set it as unused ( unused )?

 19
Author: jachguate, 2015-11-02

1 answers

A column marked unused cannot be reused. The only possible action on this column is to remove it from the table.

What can be done is to add a new column with the same name, even without having deleted the previous one.

My free translation of the documentation would be:

The ALTER TABLE statement...DROP UNUSED COLUMNS is the only action allowed on columns unused. This physically removes the columns of the table and frees up disk space.

The ALTER TABLE statement that follows specifies the optional CHECKPOINT clause. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Performing checkpoints reduces the amount of undo log that accumulates during the column erase operation to avoid potential space depletion of undo available.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

And that other (emphasis is mine):

Mark columns as unused (unused)

If you are concerned about the amount of time it may take to remove data from a column of all 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 data from it or free up disk space used by this column. Without however, a column that has been marked as unused is not displayed in queries or data dictionary views, and its name is removed in such a way that a new column can reuse that name. All constraints , indexes and statistics defined on the column are also removed.

 9
Author: jachguate, 2015-11-02 16:46:25