Home » RDBMS Server » Server Administration » UnUsed column in the database
UnUsed column in the database [message #667635] Tue, 09 January 2018 05:40 Go to next message
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 #667637 is a reply to message #667635] Tue, 09 January 2018 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle version?

Re: UnUsed column in the database [message #667638 is a reply to message #667635] Tue, 09 January 2018 06:11 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member


Oracle Version :Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Re: UnUsed column in the database [message #667640 is a reply to message #667638] Tue, 09 January 2018 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.
You see the previous name of the columns in the UNDO_SQL ("NAME" = 'COL', "NAME" = 'COL2').
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 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
saipradyumn wrote on Tue, 09 January 2018 11:40
Is 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 #667649 is a reply to message #667647] Tue, 09 January 2018 08:30 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Find the previous version of the table definition in source control and have a look at what they were called.
Re: UnUsed column in the database [message #667651 is a reply to message #667635] Tue, 09 January 2018 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is there any way to identify those column names to create it back ?
Do you have previous export of the table(s)?
Re: UnUsed column in the database [message #667652 is a reply to message #667640] Tue, 09 January 2018 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.
Re: UnUsed column in the database [message #667653 is a reply to message #667652] Tue, 09 January 2018 09:29 Go to previous message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

Regarding the Export of tables need to check with DBA.
or else SVN Code for the table structure

Really thank a lot for your input

Thanks
SaiPradyumn
Previous Topic: Recovery of deleted table
Next Topic: DB parameter change
Goto Forum:
  


Current Time: Fri Mar 29 06:09:36 CDT 2024