How can you tell which columns are unused in ALL_TAB_COLS?

Question

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?

Answer 1

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);

Then set c2 unused:

alter table t1 set unused column c2;

Then I see:

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

Answer 2

The only filter in the definition of ALL_TAB_COLUMNS is "where hidden_column = 'NO'", so it seems that UNUSED columns are flagged in the HIDDEN_COLUMN field.

Looking further into the data definition views, it looks like COL$.PROPERTY gets set to 32800 (bits 2^5 and 2^15) when the column becomes UNUSED. 2^5 is used to mark hidden columns, so it seems likely 2^15 is UNUSED. You could create a custom version of ALL_TAB_COLS based on that which should work for what you need, such as this.

CREATE OR REPLACE FORCE VIEW all_tab_cols_rev (owner,
                                               table_name,
                                               column_name,
                                               data_type,
                                               data_type_mod,
                                               data_type_owner,
                                               data_length,
                                               data_precision,
                                               data_scale,
                                               nullable,
                                               column_id,
                                               default_length,
                                               data_default,
                                               num_distinct,
                                               low_value,
                                               high_value,
                                               density,
                                               num_nulls,
                                               num_buckets,
                                               last_analyzed,
                                               sample_size,
                                               character_set_name,
                                               char_col_decl_length,
                                               global_stats,
                                               user_stats,
                                               avg_col_len,
                                               char_length,
                                               char_used,
                                               v80_fmt_image,
                                               data_upgraded,
                                               hidden_column,
                                               virtual_column,
                                               segment_column_id,
                                               internal_column_id,
                                               histogram,
                                               qualified_col_name,
                                               unused_column)
AS
   SELECT u.NAME,
          o.NAME,
          c.NAME,
          DECODE (c.type#,
                  1, DECODE (c.CHARSETFORM, 2, 'NVARCHAR2', 'VARCHAR2'),
                  2, DECODE (c.scale, NULL, DECODE (c.precision#, NULL, 'NUMBER', 'FLOAT'), 'NUMBER'),
                  8, 'LONG',
                  9, DECODE (c.CHARSETFORM, 2, 'NCHAR VARYING', 'VARCHAR'),
                  12, 'DATE',
                  23, 'RAW',
                  24, 'LONG RAW',
                  58, NVL2 (ac.synobj#, (SELECT o.NAME
                                           FROM obj$ o
                                          WHERE o.obj# = ac.synobj#), ot.NAME),
                  69, 'ROWID',
                  96, DECODE (c.CHARSETFORM, 2, 'NCHAR', 'CHAR'),
                  100, 'BINARY_FLOAT',
                  101, 'BINARY_DOUBLE',
                  105, 'MLSLABEL',
                  106, 'MLSLABEL',
                  111, NVL2 (ac.synobj#, (SELECT o.NAME
                                            FROM obj$ o
                                           WHERE o.obj# = ac.synobj#), ot.NAME),
                  112, DECODE (c.CHARSETFORM, 2, 'NCLOB', 'CLOB'),
                  113, 'BLOB',
                  114, 'BFILE',
                  115, 'CFILE',
                  121, NVL2 (ac.synobj#, (SELECT o.NAME
                                            FROM obj$ o
                                           WHERE o.obj# = ac.synobj#), ot.NAME),
                  122, NVL2 (ac.synobj#, (SELECT o.NAME
                                            FROM obj$ o
                                           WHERE o.obj# = ac.synobj#), ot.NAME),
                  123, NVL2 (ac.synobj#, (SELECT o.NAME
                                            FROM obj$ o
                                           WHERE o.obj# = ac.synobj#), ot.NAME),
                  178, 'TIME(' || c.scale || ')',
                  179, 'TIME(' || c.scale || ')' || ' WITH TIME ZONE',
                  180, 'TIMESTAMP(' || c.scale || ')',
                  181, 'TIMESTAMP(' || c.scale || ')' || ' WITH TIME ZONE',
                  231, 'TIMESTAMP(' || c.scale || ')' || ' WITH LOCAL TIME ZONE',
                  182, 'INTERVAL YEAR(' || c.precision# || ') TO MONTH',
                  183, 'INTERVAL DAY(' || c.precision# || ') TO SECOND(' || c.scale || ')',
                  208, 'UROWID',
                  'UNDEFINED'),
          DECODE (c.type#, 111, 'REF'),
          NVL2 (ac.synobj#, (SELECT u.NAME
                               FROM user$ u, obj$ o
                              WHERE o.owner# = u.user#
                                AND o.obj# = ac.synobj#), ut.NAME),
          c.LENGTH,
          c.precision#,
          c.scale,
          DECODE (SIGN (c.null$), -1, 'D', 0, 'Y', 'N'),
          DECODE (c.col#, 0, TO_NUMBER (NULL), c.col#),
          c.deflength,
          c.default$,
          h.distcnt,
          h.lowval,
          h.hival,
          h.density,
          h.null_cnt,
          CASE
             WHEN NVL (h.distcnt, 0) = 0
                THEN h.distcnt
             WHEN h.row_cnt = 0
                THEN 1
             WHEN (   h.bucket_cnt > 255
                   OR (    h.bucket_cnt > h.distcnt
                       AND h.row_cnt = h.distcnt
                       AND h.density * h.bucket_cnt <= 1) )
                THEN h.row_cnt
             ELSE h.bucket_cnt
          END,
          h.timestamp#,
          h.sample_size,
          DECODE (c.CHARSETFORM,
                  1, 'CHAR_CS',
                  2, 'NCHAR_CS',
                  3, NLS_CHARSET_NAME (c.CHARSETID),
                  4, 'ARG:' || c.CHARSETID),
          DECODE (c.CHARSETID, 0, TO_NUMBER (NULL), NLS_CHARSET_DECL_LEN (c.LENGTH, c.CHARSETID) ),
          DECODE (BITAND (h.spare2, 2), 2, 'YES', 'NO'),
          DECODE (BITAND (h.spare2, 1), 1, 'YES', 'NO'),
          h.avgcln,
          c.spare3,
          DECODE (c.type#,
                  1, DECODE (BITAND (c.property, 8388608), 0, 'B', 'C'),
                  96, DECODE (BITAND (c.property, 8388608), 0, 'B', 'C'),
                  NULL),
          DECODE (BITAND (ac.flags, 128), 128, 'YES', 'NO'),
          DECODE (o.status,
                  1, DECODE (BITAND (ac.flags, 256), 256, 'NO', 'YES'),
                  DECODE (BITAND (ac.flags, 2),
                          2, 'NO',
                          DECODE (BITAND (ac.flags, 4), 4, 'NO', DECODE (BITAND (ac.flags, 8), 8, 'NO', 'N/A') ) ) ),
          DECODE (c.property, 0, 'NO', DECODE (BITAND (c.property, 32), 32, 'YES', 'NO') ),
          DECODE (c.property, 0, 'NO', DECODE (BITAND (c.property, 8), 8, 'YES', 'NO') ),
          DECODE (c.segcol#, 0, TO_NUMBER (NULL), c.segcol#),
          c.intcol#,
          CASE
             WHEN NVL (h.row_cnt, 0) = 0
                THEN 'NONE'
             WHEN (   h.bucket_cnt > 255
                   OR (    h.bucket_cnt > h.distcnt
                       AND h.row_cnt = h.distcnt
                       AND h.density * h.bucket_cnt <= 1) )
                THEN 'FREQUENCY'
             ELSE 'HEIGHT BALANCED'
          END,
          DECODE (BITAND (c.property, 1024),
                  1024, (SELECT DECODE (BITAND (cl.property, 1), 1, rc.NAME, cl.NAME)
                           FROM SYS.col$ cl, attrcol$ rc
                          WHERE cl.intcol# = c.intcol# - 1
                            AND cl.obj# = c.obj#
                            AND c.obj# = rc.obj#(+)
                            AND cl.intcol# = rc.intcol#(+)),
                  DECODE (BITAND (c.property, 1), 0, c.NAME, (SELECT tc.NAME
                                                                FROM SYS.attrcol$ tc
                                                               WHERE c.obj# = tc.obj#
                                                                 AND c.intcol# = tc.intcol#) ) ),
          DECODE (c.property, 0, 'NO', DECODE (BITAND (c.property, 32768), 32768, 'YES', 'NO') )
     FROM SYS.col$ c, SYS.obj$ o, SYS.hist_head$ h, SYS.user$ u, SYS.coltype$ ac, SYS.obj$ ot, SYS.user$ ut
    WHERE o.obj# = c.obj#
      AND o.owner# = u.user#
      AND c.obj# = h.obj#(+)
      AND c.intcol# = h.intcol#(+)
      AND c.obj# = ac.obj#(+)
      AND c.intcol# = ac.intcol#(+)
      AND ac.toid = ot.oid$(+)
      AND ot.type#(+) = 13
      AND ot.owner# = ut.user#(+)
      AND (   o.type# IN (3, 4)   /* cluster, view */
           OR (    o.type# = 2   /* tables, excluding iot - overflow and nested tables */
               AND NOT EXISTS (
                         SELECT NULL
                           FROM SYS.tab$ t
                          WHERE t.obj# = o.obj#
                            AND (   BITAND (t.property, 512) = 512
                                 OR BITAND (t.property, 8192) = 8192) ) ) )
      AND (   o.owner# = USERENV ('SCHEMAID')
           OR o.obj# IN (SELECT obj#
                           FROM SYS.objauth$
                          WHERE grantee# IN (SELECT kzsrorol
                                               FROM x$kzsro) )
           OR   /* user has system privileges */
              EXISTS (
                 SELECT NULL
                   FROM v$enabledprivs
                  WHERE priv_number IN
                           (-45 /* LOCK ANY TABLE */,
                            -47 /* SELECT ANY TABLE */,
                            -48 /* INSERT ANY TABLE */,
                            -49 /* UPDATE ANY TABLE */,
                            -50 /* DELETE ANY TABLE */) ) );

I'd put the view in a separate, locked schema that has the SELECT ANY DICTIONARY privilege, then create a public synonym for it. That way, all of your users would be able to see the UNUSED_COLUMN column for only the tables that they have permissions on.