VIEW: SYS.LOGMNR_TAB_COLS_SUPPORT

Source

View Text - Preformatted

select o.logmnr_uid, o.obj#, o.owner#, u.name, o.name,
       c.name, c.type#,
       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 system.logmnr_obj$ o
                                 where o.logmnr_uid = c.logmnr_uid and
                                 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 system.logmnr_obj$ o
                                 where o.logmnr_uid = c.logmnr_uid and
                                 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 system.logmnr_obj$ o
                                 where o.logmnr_uid = c.logmnr_uid and
                                 o.obj#=ac.synobj#), ot.name),
                       122, nvl2(ac.synobj#,
                                (select o.name from system.logmnr_obj$ o
                                 where o.logmnr_uid = c.logmnr_uid and
                                 o.obj#=ac.synobj#), ot.name),
                       123, nvl2(ac.synobj#,
                                (select o.name from system.logmnr_obj$ o
                                 where o.logmnr_uid = c.logmnr_uid and
                                 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 system.logmnr_user$ u, system.logmnr_obj$ o
               where o.owner#=u.user# and o.obj#=ac.synobj#
                 and u.logmnr_uid = c.logmnr_uid
                 and o.logmnr_uid = c.logmnr_uid),
             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#),
       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(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(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#,
       decode(bitand(c.property, 1024), 1024,
              (select decode(bitand(cl.property, 1), 1, rc.name, cl.name)
               from system.logmnr_col$ cl, system.logmnr_attrcol$ rc
               where cl.intcol# = c.intcol#-1
               and cl.logmnr_uid = c.logmnr_uid
               and rc.logmnr_uid = c.logmnr_uid
               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 system.logmnr_attrcol$ tc
                      where tc.logmnr_uid = c.logmnr_uid and
                            c.obj# = tc.obj# and c.intcol# = tc.intcol#)))
from system.logmnr_col$ c, system.logmnr_obj$ o, system.logmnr_user$ u,
     system.logmnr_coltype$ ac, system.logmnr_obj$ ot, system.logmnr_user$ ut
where o.obj# = c.obj#
  and o.owner# = u.user#
  and o.logmnr_uid = c.logmnr_uid
  and o.logmnr_uid = u.logmnr_uid
  and c.logmnr_uid = ac.logmnr_uid(+)
  and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
  and ac.logmnr_uid = ot.logmnr_uid(+)
  and ac.toid = ot.oid$(+)
  and ot.type#(+) = 13
  and ot.logmnr_uid = ut.logmnr_uid(+)
  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 system.logmnr_tab$ t
                    where o.logmnr_uid = t.logmnr_uid
                      and t.obj# = o.obj#
                      and (bitand(t.property, 512) = 512 or
                           bitand(t.property, 8192) = 8192))))

View Text - HTML Formatted

SELECT O.LOGMNR_UID
, O.OBJ#
, O.OWNER#
, U.NAME
, O.NAME
, C.NAME
, C.TYPE#
, 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 SYSTEM.LOGMNR_OBJ$ O
WHERE O.LOGMNR_UID = C.LOGMNR_UID AND 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 SYSTEM.LOGMNR_OBJ$ O
WHERE O.LOGMNR_UID = C.LOGMNR_UID AND 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 SYSTEM.LOGMNR_OBJ$ O
WHERE O.LOGMNR_UID = C.LOGMNR_UID AND O.OBJ#=AC.SYNOBJ#)
, OT.NAME)
, 122
, NVL2(AC.SYNOBJ#
, (SELECT O.NAME
FROM SYSTEM.LOGMNR_OBJ$ O
WHERE O.LOGMNR_UID = C.LOGMNR_UID AND O.OBJ#=AC.SYNOBJ#)
, OT.NAME)
, 123
, NVL2(AC.SYNOBJ#
, (SELECT O.NAME
FROM SYSTEM.LOGMNR_OBJ$ O
WHERE O.LOGMNR_UID = C.LOGMNR_UID AND 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 SYSTEM.LOGMNR_USER$ U
, SYSTEM.LOGMNR_OBJ$ O
WHERE O.OWNER#=U.USER#
AND O.OBJ#=AC.SYNOBJ#
AND U.LOGMNR_UID = C.LOGMNR_UID
AND O.LOGMNR_UID = C.LOGMNR_UID)
, 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#)
, 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(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(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#
, DECODE(BITAND(C.PROPERTY
, 1024)
, 1024
, (SELECT DECODE(BITAND(CL.PROPERTY
, 1)
, 1
, RC.NAME
, CL.NAME)
FROM SYSTEM.LOGMNR_COL$ CL
, SYSTEM.LOGMNR_ATTRCOL$ RC
WHERE CL.INTCOL# = C.INTCOL#-1
AND CL.LOGMNR_UID = C.LOGMNR_UID
AND RC.LOGMNR_UID = C.LOGMNR_UID
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 SYSTEM.LOGMNR_ATTRCOL$ TC
WHERE TC.LOGMNR_UID = C.LOGMNR_UID AND C.OBJ# = TC.OBJ#
AND C.INTCOL# = TC.INTCOL#))) FROM SYSTEM.LOGMNR_COL$ C
, SYSTEM.LOGMNR_OBJ$ O
, SYSTEM.LOGMNR_USER$ U
, SYSTEM.LOGMNR_COLTYPE$ AC
, SYSTEM.LOGMNR_OBJ$ OT
, SYSTEM.LOGMNR_USER$ UT WHERE O.OBJ# = C.OBJ#
AND O.OWNER# = U.USER#
AND O.LOGMNR_UID = C.LOGMNR_UID
AND O.LOGMNR_UID = U.LOGMNR_UID
AND C.LOGMNR_UID = AC.LOGMNR_UID(+)
AND C.OBJ# = AC.OBJ#(+)
AND C.INTCOL# = AC.INTCOL#(+)
AND AC.LOGMNR_UID = OT.LOGMNR_UID(+)
AND AC.TOID = OT.OID$(+)
AND OT.TYPE#(+) = 13
AND OT.LOGMNR_UID = UT.LOGMNR_UID(+)
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 SYSTEM.LOGMNR_TAB$ T
WHERE O.LOGMNR_UID = T.LOGMNR_UID
AND T.OBJ# = O.OBJ#
AND (BITAND(T.PROPERTY
, 512) = 512 OR BITAND(T.PROPERTY
, 8192) = 8192))))