view unused columns

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 8 Nov 2002 11:07:33 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702E600EE@lnewton.leeds.lfs.co.uk>

Hi Steve (again),

I did a quick test and set a table to have a couple of unused columns. The dba_unused_col_tabs wasn't all that much help. Doing a desc on the table only shows the used columns. Looking in xxx_tab_columns shows this line in the where clause :

        AND bitand(c.property, 32) = 0 /* not hidden column */

So I copied the source and commented out that line (see below) and the results are as follows for my test table :

DBADMIN	TEST	A	NUMBER	
DBADMIN	TEST	SYS_C00002_02110810:57:56$	VARCHAR2	
DBADMIN	TEST	SYS_C00003_02110810:49:16$	LONG

So what were columns B and C have become SYS_Cnnnnn_blah where the
'nnnnn' part is the original column position and the rest appears to be
the date & time in YYMMDD_hh:mi:ss when I unused the columns.

Cheers,
Norman.

PS. Oracle 8174.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------


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',
                       69, 'ROWID',
                       96, DECODE(c.charsetform, 2, 'NCHAR', 'CHAR'),
                       105, 'MLSLABEL',
                       106, 'MLSLABEL',
                       111, ot.NAME,
                       112, DECODE(c.charsetform, 2, 'NCLOB', 'CLOB'),
                       113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                       121, ot.NAME,
                       122, ot.NAME,
                       123, 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'), ut.NAME,
       c.LENGTH, c.PRECISION#, c.scale,
       DECODE(SIGN(c.NULL$),-1,'D', 0, 'Y', 'N'), c.col#, c.deflength,
       c.DEFAULT$, h.distcnt, h.lowval, h.hival, h.density, h.null_cnt,
       DECODE(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1), 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
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.NAME = 'TEST'
AND   o.obj# = c.obj#

  AND o.owner# = u.USER#
  AND c.obj# = h.obj#(+) AND c.intcol# = h.intcol#(+)   --AND bitand(c.property, 32) = 0 /* not hidden column */   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))));

Received on Fri Nov 08 2002 - 05:07:33 CST