| UnUsed column in the database [message #667635] |
Tue, 09 January 2018 05:40  |
 |
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi All,
One of our development team made some of the column as UNUSED. But we need those columns.
From the help of the below query i am able to get the counts of those column and table name also.
select * from USER_UNUSED_COL_TABS;
As per my knowledge, once it is made as UNUSED we can't get it back.
The only way is to drop the unused columns, and re create with the same name
ALTER TABLE TEST_TABLE DROP UNUSED COLUMNS;
Is there any way to identify those column names to create it back ?
|
|
|
|
|
|
|
|
| Re: UnUsed column in the database [message #667640 is a reply to message #667638] |
Tue, 09 January 2018 06:34  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
The column name no more exists in the dictionary.
You can either get it from a previous backup: restore part of the database before the alter table (SYSTEM, SYSAUX, UNDO, TEMP and tablespace containing the table).
Or, if you know about when the column was set unused, flashback the table SYS.COL$ knowing that to set the column as unused Oracle updates the associated row in this table.
For example:
SQL> alter table t set unused column col;
Table altered.
SQL> alter table t set unused column col2;
Table altered.
SQL> select undo_sql from FLASHBACK_TRANSACTION_QUERY
2 where table_owner='SYS' and table_name='COL$'
3 and undo_sql like 'update%'
4 and trunc(COMMIT_TIMESTAMP) = trunc(sysdate)
5 order by commit_scn;
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
update "SYS"."COL$" set "COL#" = '2', "SEGCOL#" = '2', "SEGCOLLENGTH" = '22', "NAME" = 'COL', "TYPE#" = '2', "LENGTH" =
'22', "FIXEDSTORAGE" = '0', "PRECISION#" = NULL, "SCALE" = NULL, "NULL$" = '0', "DEFLENGTH" = NULL, "PROPERTY" = '0', "C
HARSETID" = '0', "CHARSETFORM" = '0', "SPARE1" = '0', "SPARE2" = '0', "SPARE3" = '0' where ROWID = 'AAAAACAABAAAZEYAAH';
------------------------------------------------------------------------------------------------------------------------
update "SYS"."COL$" set "COL#" = '2', "SEGCOL#" = '3', "SEGCOLLENGTH" = '22', "NAME" = 'COL2', "TYPE#" = '2', "LENGTH" =
'22', "FIXEDSTORAGE" = '0', "PRECISION#" = NULL, "SCALE" = NULL, "NULL$" = '0', "DEFLENGTH" = NULL, "PROPERTY" = '0', "
CHARSETID" = '0', "CHARSETFORM" = '0', "SPARE1" = '0', "SPARE2" = '0', "SPARE3" = '0' where ROWID = 'AAAAACAABAAAZEYAAI'
;
------------------------------------------------------------------------------------------------------------------------
2 rows selected.
To know which table, you can use the rowid in these statements:
SQL> select u.name owner, o.name table_name
2 from sys.user$ u, sys.obj$ o
3 where u.user# = o.owner#
4 and o.obj# in
5 (select obj# from sys.col$ where rowid in ('AAAAACAABAAAZEYAAH','AAAAACAABAAAZEYAAI'))
6 /
OWNER TABLE_NAME
------------------------------ ------------------------------
MICHEL T
|
|
|
|
| Re: UnUsed column in the database [message #667647 is a reply to message #667635] |
Tue, 09 January 2018 07:10 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
saipradyumn wrote on Tue, 09 January 2018 11:40Is there any way to identify those column names to create it back ?
Find the code that is trying to use it and work the name out from there.
|
|
|
|
|
|
|
|
| Re: UnUsed column in the database [message #667652 is a reply to message #667640] |
Tue, 09 January 2018 08:49 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
If your database is in ARCHIVELOG mode (and it should) and you know about when the modification have been made, you can retrieve the statement using Log Miner:
SQL> begin
2 sys.dbms_logmnr.add_logfile (logfilename=>'E:\ORACLE\BASES\MIKB2\RL_G1_1.RDO',
3 options=>sys.dbms_logmnr.NEW);
4 sys.dbms_logmnr.add_logfile (logfilename=>'E:\ORACLE\BASES\MIKB2\RL_G2_1.RDO',
5 options=>sys.dbms_logmnr.ADDFILE);
6 sys.dbms_logmnr.add_logfile (logfilename=>'E:\ORACLE\BASES\MIKB2\RL_G3_1.RDO',
7 options=>sys.dbms_logmnr.ADDFILE);
8 sys.dbms_logmnr.add_logfile (logfilename=>'E:\ORACLE\BASES\MIKB2\RL_G4_1.RDO',
9 options=>sys.dbms_logmnr.ADDFILE);
10 sys.dbms_logmnr.start_logmnr (
11 startTime => trunc(sysdate),
12 endTime => sysdate,
13 options => sys.dbms_logmnr.skip_corruption
14 + sys.dbms_logmnr.committed_data_only
15 + sys.dbms_logmnr.dict_from_online_catalog
16 );
17 end;
18 /
PL/SQL procedure successfully completed.
SQL> col seg_owner format a10
SQL> col sql_redo format a80
SQL> select seg_owner, sql_redo
2 from v$logmnr_contents
3 where ( operation like 'ALTER%' or operation = 'DDL' )
4 and lower(sql_redo) like '%set unused%'
5 /
SEG_OWNER SQL_REDO
---------- --------------------------------------------------------------------------------
MICHEL alter table t set unused column col;
MICHEL alter table t set unused column col2;
2 rows selected.
SQL> exec sys.dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed. |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
