VIEW: SYS.KU$_IOTABLE_DATA_VIEW

Source

View Text - Preformatted

select '1','2',
         t.obj#, o.dataobj_num,
         o.name,
         NULL,
         0,
         bitand(t.property, 4294967295),
         trunc(t.property / power(2, 32)),
         t.trigflag,
         dbms_metadata_util.get_xmltype_fmts(t.obj#),
         decode((select 1 from dual where
                 (exists (select q.obj# from sys.opqtype$ q
                          where q.obj#=t.obj#
                          and q.type=1                        /* xmltype col */
                          and bitand(q.flags,2+64)!=0))),       /* CSX or SB */
                1,'Y','N'),
         decode((select count(*)                      /* outofline xml table */
                    from sys.opqtype$ q
                    where q.obj# = t.obj# and
                          bitand(q.flags, 32) = 32 ),
                1,'Y','N'),
         'N',                   /* IOT can't contain long or long raw column */
         decode((select count(*) from sys.type$ ty, sys.coltype$ ct
                 where ty.toid=ct.toid and ty.version#=ct.version#
                 and ct.obj#=t.obj#
                 /* 0x00008000 =   32768 = contains varray attribute */
                 /* 0x00100000 = 1048576 = has embedded non final type */
                 and bitand(ty.properties,1081344)=1081344),
                 0,'N','Y'),
         decode((select count(*) from sys.refcon$ rf, sys.col$ c
                 where c.obj#=rf.obj# and c.intcol#=rf.intcol#
                 and c.obj#=t.obj#
                 and bitand(rf.reftyp,1)=0),            /* ref is non-scoped */
                 0,'N','Y'),
         (select sys.dbms_metadata_util.has_tstz_cols(t.obj#) from dual),
         value(o),
         ts.name, ts.ts#, ts.blocksize,
         (select dbms_metadata_util.block_estimate(t.obj#,4) from dual),
         value(o),
         -- if this is a secondary table, get domidx obj and ancestor obj
         decode(bitand(o.flags, 16), 16,
           (select value(oo) from ku$_schemaobj_view oo, secobj$ s
              where o.obj_num=s.secobj#
                and oo.obj_num=s.obj#
                and rownum < 2),
           null),
         decode(bitand(o.flags, 16), 16,
           (select value(oo) from ku$_schemaobj_view oo, ind$ i, secobj$ s
              where o.obj_num=s.secobj#
                and i.obj#=s.obj#
                and oo.obj_num=i.bo#
                and rownum < 2),
           null),
         um.unload_method,
         um.et_parallel,
         (select count(*) from rls$ r
          where r.obj#=t.obj# and r.enable_flag=1 and bitand(r.stmt_type,1)=1),
         0
  from  ku$_schemaobjnum_view o, ku$_unload_method_view um, tab$ t, ind$ i,
        ts$ ts
  where t.obj# = o.obj_num
        AND t.obj# = um.obj_num
        and bitand(t.property, 64+512) = 64  -- IOT but not overflow
        and bitand(t.property, 32+8192) = 0     /* but not partitioned    */
                                                /* or nested table        */
        and t.pctused$ = i.obj#          -- For IOTs, pctused has index obj#
        AND i.ts# = ts.ts#
        AND (bitand(o.flags,16)!=16
             OR sys.dbms_metadata.oktoexp_2ndary_table(o.obj_num)=1)
        AND (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (o.owner_num, 0) OR
                EXISTS ( SELECT * FROM sys.session_roles
                        WHERE role='SELECT_CATALOG_ROLE' ))

View Text - HTML Formatted

SELECT '1'
, '2'
, T.OBJ#
, O.DATAOBJ_NUM
, O.NAME
, NULL
, 0
, BITAND(T.PROPERTY
, 4294967295)
, TRUNC(T.PROPERTY / POWER(2
, 32))
, T.TRIGFLAG
, DBMS_METADATA_UTIL.GET_XMLTYPE_FMTS(T.OBJ#)
, DECODE((SELECT 1
FROM DUAL WHERE (EXISTS (SELECT Q.OBJ#
FROM SYS.OPQTYPE$ Q
WHERE Q.OBJ#=T.OBJ#
AND Q.TYPE=1 /* XMLTYPE COL */
AND BITAND(Q.FLAGS
, 2+64)!=0)))
, /* CSX OR SB */ 1
, 'Y'
, 'N')
, DECODE((SELECT COUNT(*) /* OUTOFLINE XML TABLE */
FROM SYS.OPQTYPE$ Q
WHERE Q.OBJ# = T.OBJ# AND BITAND(Q.FLAGS
, 32) = 32 )
, 1
, 'Y'
, 'N')
, 'N'
, /* IOT CAN'T CONTAIN LONG OR LONG RAW COLUMN */ DECODE((SELECT COUNT(*)
FROM SYS.TYPE$ TY
, SYS.COLTYPE$ CT
WHERE TY.TOID=CT.TOID
AND TY.VERSION#=CT.VERSION#
AND CT.OBJ#=T.OBJ# /* 0X00008000 = 32768 = CONTAINS VARRAY ATTRIBUTE */ /* 0X00100000 = 1048576 = HAS EMBEDDED NON FINAL TYPE */
AND BITAND(TY.PROPERTIES
, 1081344)=1081344)
, 0
, 'N'
, 'Y')
, DECODE((SELECT COUNT(*)
FROM SYS.REFCON$ RF
, SYS.COL$ C
WHERE C.OBJ#=RF.OBJ#
AND C.INTCOL#=RF.INTCOL#
AND C.OBJ#=T.OBJ#
AND BITAND(RF.REFTYP
, 1)=0)
, /* REF IS NON-SCOPED */ 0
, 'N'
, 'Y')
, (SELECT SYS.DBMS_METADATA_UTIL.HAS_TSTZ_COLS(T.OBJ#)
FROM DUAL)
, VALUE(O)
, TS.NAME
, TS.TS#
, TS.BLOCKSIZE
, (SELECT DBMS_METADATA_UTIL.BLOCK_ESTIMATE(T.OBJ#
, 4)
FROM DUAL)
, VALUE(O)
, -- IF THIS IS A SECONDARY TABLE
, GET DOMIDX OBJ
AND ANCESTOR OBJ DECODE(BITAND(O.FLAGS
, 16)
, 16
, (SELECT VALUE(OO)
FROM KU$_SCHEMAOBJ_VIEW OO
, SECOBJ$ S
WHERE O.OBJ_NUM=S.SECOBJ#
AND OO.OBJ_NUM=S.OBJ#
AND ROWNUM < 2)
, NULL)
, DECODE(BITAND(O.FLAGS
, 16)
, 16
, (SELECT VALUE(OO)
FROM KU$_SCHEMAOBJ_VIEW OO
, IND$ I
, SECOBJ$ S
WHERE O.OBJ_NUM=S.SECOBJ#
AND I.OBJ#=S.OBJ#
AND OO.OBJ_NUM=I.BO#
AND ROWNUM < 2)
, NULL)
, UM.UNLOAD_METHOD
, UM.ET_PARALLEL
, (SELECT COUNT(*)
FROM RLS$ R
WHERE R.OBJ#=T.OBJ#
AND R.ENABLE_FLAG=1
AND BITAND(R.STMT_TYPE
, 1)=1)
, 0
FROM KU$_SCHEMAOBJNUM_VIEW O
, KU$_UNLOAD_METHOD_VIEW UM
, TAB$ T
, IND$ I
, TS$ TS
WHERE T.OBJ# = O.OBJ_NUM
AND T.OBJ# = UM.OBJ_NUM
AND BITAND(T.PROPERTY
, 64+512) = 64 -- IOT BUT NOT OVERFLOW
AND BITAND(T.PROPERTY
, 32+8192) = 0 /* BUT NOT PARTITIONED */ /* OR NESTED TABLE */
AND T.PCTUSED$ = I.OBJ# -- FOR IOTS
, PCTUSED HAS INDEX OBJ#
AND I.TS# = TS.TS#
AND (BITAND(O.FLAGS
, 16)!=16 OR SYS.DBMS_METADATA.OKTOEXP_2NDARY_TABLE(O.OBJ_NUM)=1)
AND (SYS_CONTEXT('USERENV'
, 'CURRENT_USERID') IN (O.OWNER_NUM
, 0) OR EXISTS ( SELECT *
FROM SYS.SESSION_ROLES
WHERE ROLE='SELECT_CATALOG_ROLE' ))