Oracle setting a column unused and then creating a new column of same name

Question

So i have a script which is creating a column named ID.

alter table table_name add (ID NUMBER(2))

Now as part of rollback script, this column is being set to UNUSED since its practically impossible to delete this column

alter table table_name set unused column ID;

Now, since we have same delta script which earlier was creating this column, if we run the delta again to "add" this column would it be okay? What approach should be followed to tackle this issue?

I remember that setting a column unused would delete it from dictionry and add 1 byte null value to the column but adding the same column with same name, would it be fine?

Solution

It's easy enough to check this:

SQL> create table t23 (
  2  id number
  3  , col1 varchar2(10)
  4  );

Table created.

SQL> alter table t23
  2      set unused column id;

Table altered.

SQL> desc t23
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(10)

SQL> alter table t23 
  2      add (id number)   
  3  /

Table altered.

SQL> desc t23
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(10)
 ID                                                 NUMBER

SQL> 

Of course, my example is not quite the same as your scenario but basically you can repeat the SET UNUSED and ADD cycle as many times as you like. However I would recommend that at some point you do run a cleanup:

SQL> alter table t23 drop unused columns
  2  /

Table altered.

SQL> 

You can do this in slow time.