-
Hello,
We have a production database in which some columns in table A have been set to "UNUSED" i.e:
ALTER TABLE A SET UNUSED (COLx);
I just realized that a wrong column has been set to unused.
Is there an command to set this column back to a used state ?
I've already tried
ALTER TABLE A SET USED (COLx); it does not work.
Please help.
If there isn't such command, my other alternative is to restore this table from backup.
Thank you
-
-
I checked docs and looked at some other references and couldn't find anything about it yet. Setting columns as unused is a precursor to dropping them so I suppose it follows the rule that DDL can't be 'rolled back', but you would think it's just a flag until the column is actually dropped.
If you have a plan ready and it's really urgent to get the columns back online, your best bet is probably to restore the table.
If you can wait a little maybe someone else has a better answer, or you can open up a TAR. You might open a TAR either way just for future reference.
-
I kinda skimmed it the first time I looked through, but here's the definitive answer
Note: Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns per table. However, as with all DDL statements, you cannot roll back the results of this clause. That is, you cannot issue SET USED counterpart to retrieve a column that you have SET UNUSED.
Also, if you mark a column of datatype LONG as UNUSED, you cannot add another LONG column to the table until you actually drop the unused LONG column.
See Also: "CREATE TABLE" for more information on the 1000 column limit.
-
Thanks alot for your help pwoneill,
I was in a position where I could not wait any longer therefore I restored that table from backup.
I will go ahead and open a TAR with oracle and update my posting as I hear from them.
Thanks again for the help.
Robel
-
pwoneill,
Sorry, I must have overlooked your second reply.
I guess there is s documentation about this. And it looks like there is no way to reverse this transaction.
My advise would be : Only use this command on a column if you are about to drop this column (specialy usefull on very large column). When you set a column to unused before droping it, a redo log and rb segments will not be generated therefor speeding the column drop.
