您如何确定ALL_TAB_COLS中未使用的列? [英] How can you tell which columns are unused in ALL_TAB_COLS?
问题描述
在Oracle 9i上查询ALL_TAB_COLS视图时,它将列出标记为UNUSED的列以及活动"表列.似乎没有一个字段明确说明某个列是未使用的列,还是我可以加入的任何视图都列出了表中未使用的列的字段.如何轻松找出未使用的列,以便将它们从ALL_TAB_COLS中过滤出来?
When you query the ALL_TAB_COLS view on Oracle 9i, it lists columns marked as UNUSED as well as the 'active' table columns. There doesn't seem to be a field that explicitly says whether a column is UNUSED, or any view I can join to that lists the unused columns in a table. How can I easily find out which are the unused columns, so I can filter them out of ALL_TAB_COLS?
推荐答案
尝试使用ALL_TAB_COLUMNS而不是ALL_TAB_COLS.在Oracle 11.2中,我发现未使用的列显示在ALL_TAB_COLS中(尽管已重命名),但未出现在ALL_TAB_COLUMNS中.
Try using ALL_TAB_COLUMNS instead of ALL_TAB_COLS. In Oracle 11.2 I find that unused columns appear in ALL_TAB_COLS (though renamed) but not in ALL_TAB_COLUMNS.
我创建了一个像这样的表:
I created a table like this:
create table t1 (c1 varchar2(30), c2 varchar2(30);
然后将c2设置为未使用:
Then set c2 unused:
alter table t1 set unused column c2;
然后我看到了:
select column_name from all_tab_cols where owner='ME' and table_name='T1';
COLUMN_NAME
-----------
C1
SYS_C00002_10060107:25:40$
select column_name from all_tab_columns where owner='ME' and table_name='T1';
COLUMN_NAME
-----------
C1
这篇关于您如何确定ALL_TAB_COLS中未使用的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!