|
|
|
|
| Re: how to find unused columns [message #530559 is a reply to message #530557] |
Wed, 09 November 2011 02:35   |
John Watson
Messages: 8962
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 |
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 #530609 is a reply to message #530589] |
Wed, 09 November 2011 05:49 |
 |
Littlefoot
Messages: 21823
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
Then, connected as a privileged user, I ranSQL> 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 |
John Watson
Messages: 8962
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....)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
