eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: SYS.USER_TAB_COLS_V$

Source

View Text - Preformatted


select 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 "_BASE_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,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.lowval
            else null
       end,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.hival
            else null
       end,
       h.density, h.null_cnt,
       case when nvl(h.distcnt,0) = 0 then h.distcnt
            -- no histogram
            when h.row_cnt = 0 then 1
            -- hybrid histogram
	    when exists(select 1 from sys.histgrm$ hg
                        where c.obj# = hg.obj# and c.intcol# = hg.intcol#
                          and hg.ep_repeat_count > 0 and rownum < 2) then h.row_cnt
            -- top-frequency histogram
            when bitand(h.spare2, 64) > 0
              then h.row_cnt
            -- frequency histogram
            when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or
                  (h.bucket_cnt >= h.distcnt and h.density*h.bucket_cnt < 1))
                then h.row_cnt
            -- height-balanced histogram
            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'),
       decode(bitand(h.spare2, 8), 8, 'INCREMENTAL ', '') ||
         decode(bitand(h.spare2, 128), 128, 'HIST_FOR_INCREM_STATS ', '') ||
         decode(bitand(h.spare2, 256), 256, 'HISTOGRAM_ONLY ', '') ||
         decode(bitand(h.spare2, 512), 512, 'STATS_ON_LOAD ', ''),
       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#,
       -- warning! If you update stats related info, make sure to also update
       -- GTT session private stats in cdoptim.sql
       case when nvl(h.row_cnt,0) = 0 then 'NONE'
            when exists(select 1 from sys.histgrm$ hg
                        where c.obj# = hg.obj# and c.intcol# = hg.intcol#
                          and hg.ep_repeat_count > 0 and rownum < 2) then 'HYBRID'
            when bitand(h.spare2, 64) > 0
              then 'TOP-FREQUENCY'
            when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or
                  (h.bucket_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(bitand(c.property, 17179869184), 17179869184, 'YES',
              decode(bitand(c.property, 32), 32, 'NO', 'YES')),
       decode(bitand(c.property, 68719476736), 68719476736, 'YES', 'NO'),
       decode(bitand(c.property, 137438953472 + 274877906944),
                     137438953472, 'YES', 274877906944, 'YES', 'NO'),
  
       case when c.evaledition# is null then null
         else (select name from obj$ where obj# = c.evaledition#) end,
       case when c.unusablebefore# is null then null
         else (select name from obj$ where obj# = c.unusablebefore#) end,
       case when c.unusablebeginning# is null then null
         else (select name from obj$ where obj# = c.unusablebeginning#) end
  
from sys.col$ c, sys."_CURRENT_EDITION_OBJ" o, sys.hist_head$ h,
     sys.coltype$ ac, sys.obj$ ot, sys."_BASE_USER" ut, sys.tab$ t
where o.obj# = c.obj#
  and o.obj# = t.obj#(+)
  and bitand(o.flags, 128) = 0
  and o.owner# = userenv('SCHEMAID')
  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))))

View Text - HTML Formatted

SELECT 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 "_BASE_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
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.LOWVAL ELSE NULL END
, CASE WHEN SYS_OP_DV_CHECK(O.NAME
, O.OWNER#) = 1 THEN H.HIVAL ELSE NULL END
, H.DENSITY
, H.NULL_CNT
, CASE WHEN NVL(H.DISTCNT
, 0) = 0 THEN H.DISTCNT -- NO HISTOGRAM WHEN H.ROW_CNT = 0 THEN 1 -- HYBRID HISTOGRAM WHEN EXISTS(SELECT 1
FROM SYS.HISTGRM$ HG
WHERE C.OBJ# = HG.OBJ#
AND C.INTCOL# = HG.INTCOL#
AND HG.EP_REPEAT_COUNT > 0
AND ROWNUM < 2) THEN H.ROW_CNT -- TOP-FREQUENCY HISTOGRAM WHEN BITAND(H.SPARE2
, 64) > 0 THEN H.ROW_CNT -- FREQUENCY HISTOGRAM WHEN (BITAND(H.SPARE2
, 32) > 0 OR H.BUCKET_CNT > 2049 OR (H.BUCKET_CNT >= H.DISTCNT
AND H.DENSITY*H.BUCKET_CNT < 1)) THEN H.ROW_CNT -- HEIGHT-BALANCED HISTOGRAM 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')
, DECODE(BITAND(H.SPARE2
, 8)
, 8
, 'INCREMENTAL '
, '') || DECODE(BITAND(H.SPARE2
, 128)
, 128
, 'HIST_FOR_INCREM_STATS '
, '') || DECODE(BITAND(H.SPARE2
, 256)
, 256
, 'HISTOGRAM_ONLY '
, '') || DECODE(BITAND(H.SPARE2
, 512)
, 512
, 'STATS_ON_LOAD '
, '')
, 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#
, -- WARNING! IF YOU UPDATE STATS RELATED INFO
, MAKE SURE TO ALSO UPDATE -- GTT SESSION PRIVATE STATS IN CDOPTIM.SQL CASE WHEN NVL(H.ROW_CNT
, 0) = 0 THEN 'NONE' WHEN EXISTS(SELECT 1
FROM SYS.HISTGRM$ HG
WHERE C.OBJ# = HG.OBJ#
AND C.INTCOL# = HG.INTCOL#
AND HG.EP_REPEAT_COUNT > 0
AND ROWNUM < 2) THEN 'HYBRID' WHEN BITAND(H.SPARE2
, 64) > 0 THEN 'TOP-FREQUENCY' WHEN (BITAND(H.SPARE2
, 32) > 0 OR H.BUCKET_CNT > 2049 OR (H.BUCKET_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(BITAND(C.PROPERTY
, 17179869184)
, 17179869184
, 'YES'
, DECODE(BITAND(C.PROPERTY
, 32)
, 32
, 'NO'
, 'YES'))
, DECODE(BITAND(C.PROPERTY
, 68719476736)
, 68719476736
, 'YES'
, 'NO')
, DECODE(BITAND(C.PROPERTY
, 137438953472 + 274877906944)
, 137438953472
, 'YES'
, 274877906944
, 'YES'
, 'NO')
, CASE WHEN C.EVALEDITION# IS NULL THEN NULL ELSE (SELECT NAME
FROM OBJ$
WHERE OBJ# = C.EVALEDITION#) END
, CASE WHEN C.UNUSABLEBEFORE# IS NULL THEN NULL ELSE (SELECT NAME
FROM OBJ$
WHERE OBJ# = C.UNUSABLEBEFORE#) END
, CASE WHEN C.UNUSABLEBEGINNING# IS NULL THEN NULL ELSE (SELECT NAME
FROM OBJ$
WHERE OBJ# = C.UNUSABLEBEGINNING#) END FROM SYS.COL$ C
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.HIST_HEAD$ H
, SYS.COLTYPE$ AC
, SYS.OBJ$ OT
, SYS."_BASE_USER" UT
, SYS.TAB$ T WHERE O.OBJ# = C.OBJ#
AND O.OBJ# = T.OBJ#(+)
AND BITAND(O.FLAGS
, 128) = 0
AND O.OWNER# = USERENV('SCHEMAID')
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))))