All geek questions in one place

How can you determine which columns are not used in ALL_TAB_COLS?

When you request the ALL_TAB_COLS view on Oracle 9i, it displays the columns labeled with both UNUSED and active columns. There does not seem to be a field that explicitly indicates whether the column is UNUSED or some kind that I can join that lists the unused columns in the table. How can I easily find out which unused columns, so I can filter them out from ALL_TAB_COLS?

+3
source share
2 answers

Try using ALL_TAB_COLUMNS instead of ALL_TAB_COLS. In Oracle 11.2, I found that unused columns appear in ALL_TAB_COLS (although renamed), but not in ALL_TAB_COLUMNS.

I created the table as follows:

create table t1 (c1 varchar2(30), c2 varchar2(30);

Then install 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
+2
source

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

, , COL $.PROPERTY 32800 ( 2 ^ 5 2 ^ 15), UNUSED. 2 ^ 5 , , , 2 ^ 15 UNUSED. ALL_TAB_COLS , , , .

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

, SELECT ANY DICTIONARY, . , UNUSED_COLUMN , .

+1

Source: https://habr.com/ru/post/1747968/

More articles:


All Articles