| Getting Back a Column Marked as UNUSED [message #63014] |
Thu, 02 September 2004 03:20 |
Puneet sachar
Messages: 62
Registered: October 2003
|
Member |
|
|
Hi ... Try this
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$ table (you must use the SYS user to see this) has the 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 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 statement parse.
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.
Your feedbacks are welcome !!
Puneet Sachar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
