| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Reversing Unused Setting on a Column
Here you go, in Mike's own words...
First, this is UNSUPPORTED only use it in emergencies!
Getting Back a Column Marked as UNUSED
Oops…marked the wrong column as UNUSED and now you find that you can’t go back…or can you? During the testing of this feature I found a method to get back a column marked as UNUSED. Once you DROP a column that has been marked as UNUSED it is gone for good, but up to the point where it is actually dropped, it can be reclaimed. This is how the technique works, I suggest practicing before doing the reclaim on a production table.
The col$ has obj#, col#, name and property fields which tie into the columns for each table where obj# will correspond to the obj# in the tab$ table. When a column is marked unused the col# is set to zero, the name is set to a system generated name and the property is set to 32800 from its initial value of 0 (for a normal, non-type column). In the tab$ table the number of columns is decremented by one in the cols column. The obj$ table stores the link between the object name and owner and the obj#, you then use that obj# to pull relevant data from the tab$ and col$ tables.
In order to reverse the unused state, reset the col# value to its original value, reset the name column to the proper name and reset the property to 0. In the tab$ table reset the cols column to the appropriate value.
You will have to flush the shared pool or even restart to flush the object caches and dictionary caches to see the column again.
To test this process perform the following steps: 1. Create a small table with two to three columns 2. Look at the entries for obj$, tab$ and col$ for this table noting the values for COLS in tab$ and COL#, INTCOL#, PROPERTY and NAME in col$ (from SYS or INTERNAL user).
3. Add data to the table and commit. 4. Select from the table to confirm the data and columns. 5. Use ALTER TABLE SET UNUSED to mark a column unused. 6. Select from the table to confirm the column unavailable. 7. Log in as SYS or INTERNAL and check tab$ and col$ as before. 8. Update tab$ and col$ to before the ALTER conditions and commit. 9. Flush the shared pool to get rid of the post condition select statementparse.
Issue a select against the table to confirm the column came back.
(Note: sometimes you may need to shutdown and restart before the column will come back)
That should do it...of course if you actually drop the column you can't get it back. And remember,
dropping any column in a table will result in loss of all columns marked UNUSED whether mentioned in the DROP or not.
-----Original Message-----
Sent: 08 January 2002 16:45
To: Multiple recipients of list ORACLE-L
Kirti:
Thanks, but I don't have access to Metalink.
Ken
-----Original Message-----
Sent: Tuesday, January 08, 2002 10:20 AM
To: Multiple recipients of list ORACLE-L Subject: RE: Reversing Unused Setting on a Column
Yes, it is possible. But not supported by Oracle. There is post by Mike Ault on Metalink Forum describing how to do this. Pl search Metalink for it. BTW, his post is followed by Oracle's post reminding the readers that it is a non supported operation (on data dictionary tables owned by SYS).
-----Original Message-----
Sent: Tuesday, January 08, 2002 9:36 AM
To: Multiple recipients of list ORACLE-L
I have heard that you can revere the UNUSED setting on a column. Anyone know how to do this?
Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient Systems, Inc.
Minneapolis, MN
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ken Janusz
INET: ken.janusz_at_sufsys.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: James McCann
INET: james_at_openet-telecom.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 08 2002 - 12:12:32 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |