Skip to Main Content
  • Questions
  • Can you recover a column marked as UNUSED?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ramon.

Asked: August 15, 2012 - 7:56 pm UTC

Last updated: October 17, 2016 - 1:14 am UTC

Version: 11.2.0.2

Viewed 10K+ times! This question is

You Asked

We have a situation where somebody marked one too many columns as UNUSED by mistake. We do not intend to drop this additional column. I was searching around for a solution to this but I have not found one yet... The question is simple: is there a way to mark this unused column as used again?

From what I understand, when you mark a column as UNUSED, all the data is still there, it's only the definition of the table that changes in the dictionary, correct (though I'm sure this is a very simplistic view)? If that's the case, I find it hard to believe that there wouldn't be an easy way (or rather, a way) to mark that column as present yet again?

Thanks in advance for all your input!

and Tom said...

No.


The data might be there, the data might not be there. Once you drop it - we don't need to maintain stuff for future rows - or even existing rows.

In the millisecond after you drop the column - in theory we could "get it back", however as soon as change is introduced - we cannot. Therefore, you cannot.

You would be looking at doing a tablespace point in time recovery on an auxiliary instance and either

a) create table t as select primary key, column to recover from your table - and export/import that table and update the join

b) export the table and import it. putting it back the way it was before the DDL operation.



If you find anything on the internet that shows how to update the SYS tables - please, I'm begging you, IGNORE IT - it will corrupt your database, not might, but will. The write ups I've seen update a single table - but the set unused command modifies DOZENS of tables - you will definitely break that table forever.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ramon E., August 17, 2012 - 1:30 pm UTC

Thank you, we ended up restoring from a backup to a temp table and then re-initializing the data from there.

Reason behind unused column.

vaibhav johari, October 15, 2016 - 6:27 am UTC

I want to know the reason, that why we can't use again the column that we already set unused in any table.

I want to know the basic reason behind this.
Connor McDonald
October 17, 2016 - 1:14 am UTC

It does not really make practical sense to allow it. Consider the following trivial example

SQL> create table t ( x int not null, y int not null, z int not null);

Table created.

SQL> insert into t values (1,2,3);

1 row created.

SQL> alter table t set unused column z;

Table altered.

SQL> insert into t values (4,5);

1 row created.


Now I've got a row in there with a null value for what *originally* was my column Z. At which point, resurrecting Z means we're not *really* bringing the column back, because we'd be bringing it back with a alternate definition (ie, nullable). And of course, if in the interim, I've done

"alter table T add Z date"

then the resurrection would have to be as a new column name anyway.

So whilst its perhaps *possible* that the functionality could be done, the need perhaps is minimal. If its important to you, you can always log an enhancement request.