Home » RDBMS Server » Server Administration » how to find unused columns (11.2.0.1 windows xp)
how to find unused columns [message #530554] Wed, 09 November 2011 01:53 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
is there a way to find out which unused columns in oracle?
Re: how to find unused columns [message #530557 is a reply to message #530554] Wed, 09 November 2011 02:12 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you call "unused columns"? Empty columns? Columns not used in any SELECT statement someone ever produced on your database? Something different?
Re: how to find unused columns [message #530559 is a reply to message #530557] Wed, 09 November 2011 02:35 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
There is also
ALTER TABLE SET UNUSED ('column name');
which has the effect of making the column disappear, though it will still occupy space until you
ALTER TABLE...DROP UNUSED COLUMNS;
Tables with such columns are listed in DBA_UNUSED_COL_TABS

[update: you never said "thank you" in your topic regarding partition exchange.]

[Updated on: Wed, 09 November 2011 02:36]

Report message to a moderator

Re: how to find unused columns [message #530574 is a reply to message #530559] Wed, 09 November 2011 03:12 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
The DBA_UNUSED_COL_TABS do not list which column have been unused,i want to know where the column id2 is stored.
 
SQL> Create Table tb_unused_coulum
  2  (
  3    id1 Number,
  4    id2 Number
  5  )
  6  /

Table created.

SQL> ALTER TABLE tb_unused_coulum SET UNUSED COLUMN id2;

Table altered.

SQL> Select * From DBA_UNUSED_COL_TABS;

OWNER                          TABLE_NAME                          COUNT
------------------------------ ------------------------------ ----------
HXL                            TB_UNUSED_COULUM                        1

Re: how to find unused columns [message #530583 is a reply to message #530574] Wed, 09 November 2011 03:22 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
It is no fun helping people who don't say "thank you". Perhaps someone else will help, or you could try looking at a few views yourself.
Re: how to find unused columns [message #530589 is a reply to message #530583] Wed, 09 November 2011 03:45 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
John,thank you your reply!
Re: how to find unused columns [message #530609 is a reply to message #530589] Wed, 09 November 2011 05:49 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Weee, that information is somewhat difficult to find. I didn't manage to do that simply by Googling, so I took John's advice, searching for a few views myself.

Connected to
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
I performed the first step simply by copy/pasting Andy Huang's CREATE / ALTER TABLE statements.

Then, connected as a privileged user, I ran
SQL> select * from tab where tname like '%UNUS%';

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ALL_UNUSED_COL_TABS            VIEW
DBA_UNUSED_COL_TABS            VIEW
EXU8COL_TEMP_TTS_UNUSED_COL    VIEW
EXU8COL_TTS_UNUSED_COL         VIEW
EXU9COO_TTS_UNUSED_COL         VIEW
EXU9TAB_UNUSED_COLS            VIEW
LOADER_SKIP_UNUSABLE_INDEXES   VIEW
USER_UNUSED_COL_TABS           VIEW
V_$UNUSABLE_BACKUPFILE_DETAILS VIEW

9 rows selected.

Now it is just a matter of typing to select from them all, finding out the one that suits your needs. For example:
SQL> select towner, tname, name
  2  from exu8col_tts_unused_col
  3  where towner = 'SCOTT'
  4    and tname = 'TEST';

TOWNER                         TNAME                          NAME
------------------------------ ------------------------------ ------------------------------
SCOTT                          TEST                           SYS_C00002_11110912:31:10$
SCOTT                          TEST                           ID1

The first record looks like a constraint (or something like that), and the second one identifies a column we set UNUSED. There's bunch of other columns in that view, but I don't know what they mean so trying to identify ID1 column itself is kind of a mystery to me. Honestly, I never even heard of these "EXU..." views before. I did review their scripts, but I'm not any smarter now.

Obviously, my knowledge is too low to tell whether this is the right way to do that or not, but I don't know any better, sorry.
Re: how to find unused columns [message #530612 is a reply to message #530609] Wed, 09 November 2011 06:06 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Nice one, LF! I didn't know about that view. I was looking at user_tab_columns:

select column_name,hidden_column from user_tab_cols;

this appears to show that marking a column unused in fact renames and hides it. If it weren't renamed, I guess you couldn't re-use the name for a new column. Your view seems to map it back to the original name, somehow. The name %TTS% implies something to do with transportable tablespaces, so I suppose unused columns are part of the metadata that gets transferred with the tablespace datafiles.

Andy, you see how much fun Oracle is? When in doubt, keep trying to reverse engineer it. (If you have the time....)

Re: how to find unused columns [message #530615 is a reply to message #530609] Wed, 09 November 2011 06:12 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Previous Topic: control file reuse
Next Topic: problem in creating view
Goto Forum:
  


Current Time: Thu Apr 17 02:47:56 CDT 2014

Total time taken to generate the page: 0.09268 seconds