VIEW: SYS.KU$_ACPTABLE_VIEW

Source

View Text - Preformatted

select '2','6',
         t.obj#,
         value(o),
         -- if this is a secondary table, get base 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),
         (select value(po) from ku$_schemaobj_view po
           where
            po.obj_num=sys.dbms_metadata_util.ref_par_parent(t.obj#)),
         cast(multiset(select value(og) from  ku$_objgrant_view og, ku$_schemaobj_view po
                 where og.base_obj.obj_num = sys.dbms_metadata_util.ref_par_parent(t.obj#) and
                       po.obj_num = og.base_obj.obj_num and
                       og.privname = 'REFERENCES' and
                       og.base_obj.name = po.name
                 order by og.wgo desc)
                 as ku$_objgrant_list_t),
         (select value(s) from ku$_storage_view s
          where t.file# = s.file_num
          and t.block#  = s.block_num
          and t.ts#     = s.ts_num),
         (select value(s) from ku$_deferred_stg_view s
          where s.obj_num = t.obj#),
         ts.name, ts.blocksize,
         t.dataobj#, t.bobj#, t.tab#, t.cols,
         t.clucols,
         (select value(cl) from ku$_tabcluster_view cl
          where cl.obj_num = t.obj#),
         (select value(fb) from ku$_fba_view fb where fb.obj_num = t.obj#),
         (select value(fb) from ku$_fba_period_view fb
                           where fb.obj_num = t.obj#),
         (select value(cz) from ku$_clst_view cz where cz.obj_num = t.obj#),
         cast( multiset(select * from ku$_ilm_policy_view p
                        where p.obj_num = t.obj#
                        order by p.policy_num
                        ) as ku$_ilm_policy_list_t
              ),
         t.pctfree$, t.pctused$, t.initrans, t.maxtrans, t.flags,
         replace(t.audit$,chr(0),'-'), t.rowcnt, t.blkcnt, t.empcnt,
         t.avgspc, t.chncnt, t.avgrln, t.avgspc_flb, t.flbcnt,
         to_char(t.analyzetime,'YYYY/MM/DD HH24:MI:SS'),
         t.samplesize, t.degree, t.instances, t.intcols, t.kernelcols,
         bitand(t.property, 4294967295),
         trunc(t.property / power(2, 32)),
         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'),
         (select sys.dbms_metadata_util.has_tstz_cols(t.obj#) from dual),
         case when (exists (select q.obj# from sys.opqtype$ q
                          where q.obj#=t.obj#
                          and q.type=1))                      /* xmltype col */
              then dbms_metadata_util.get_xmlcolset(o.obj_num)
              else NULL end,
         case when (exists (select q.obj# from sys.opqtype$ q
                          where q.obj#=t.obj#
                          and q.type=1))                      /* xmltype col */
              then dbms_metadata_util.get_xmlhierarchy(o.owner_name,o.name)
              else NULL end,
         t.trigflag,
         t.spare1, t.spare2, t.spare3, t.spare4, t.spare5,
         to_char(t.spare6,'YYYY/MM/DD HH24:MI:SS'),
         decode(bitand(t.trigflag, 65536), 65536,
           (select e.encalg from sys.enc$ e where e.obj#=t.obj#),
           null),
         decode(bitand(t.trigflag, 65536), 65536,
           (select e.intalg from sys.enc$ e where e.obj#=t.obj#),
           null),
         cast( multiset(select * from ku$_pcolumn_view c
                        where c.obj_num = t.obj#
                        order by c.col_num, c.intcol_num
                        ) as ku$_pcolumn_list_t
              ),
         cast( multiset(select * from ku$_im_colsel_view imc
                        where imc.obj_num = t.obj#
                       ) as ku$_im_colsel_list_t
             ),
         (select value(nt) from ku$_nt_parent_view nt
          where nt.obj_num = t.obj#),
         cast( multiset(select * from ku$_constraint0_view con
                        where con.obj_num = t.obj#
                        and con.contype not in (7,11)
                       ) as ku$_constraint0_list_t
             ),
         cast( multiset(select * from ku$_constraint1_view con
                        where con.obj_num = t.obj#
                       ) as ku$_constraint1_list_t
             ),
         cast( multiset(select * from ku$_constraint2_view con
                        where con.obj_num = t.obj#
                       ) as ku$_constraint2_list_t
             ),
         cast( multiset(select * from ku$_pkref_constraint_view con
                        where con.obj_num = t.obj#
                       ) as ku$_pkref_constraint_list_t
             ),
         (select value(po) from ku$_tab_partobj_view po
          where t.obj# = po.obj_num),
         (select value(etv) from ku$_exttab_view etv
                        where etv.obj_num = o.obj_num),
         (select value(otv) from ku$_cube_tab_view otv
                        where otv.obj_num = o.obj_num),
         sys.dbms_metadata_util.ref_par_level(t.obj#)
  from ku$_schemaobj_view o, tab$ t, ts$ ts
  where t.obj# = o.obj_num
        AND t.ts# = ts.ts#
        AND bitand(t.property, 32+64+128+256+512) = 32
                                                /* partitioned (32)       */
                                                /* but not IOT            */
        /* mutually exclusive with ku$_phtable and ku$_pfhtable */
        AND exists( select * from partobj$ po
                    where po.obj# = t.obj# and po.parttype = 5)
        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 '2'
, '6'
, T.OBJ#
, VALUE(O)
, -- IF THIS IS A SECONDARY TABLE
, GET BASE 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)
, (SELECT VALUE(PO)
FROM KU$_SCHEMAOBJ_VIEW PO WHERE PO.OBJ_NUM=SYS.DBMS_METADATA_UTIL.REF_PAR_PARENT(T.OBJ#))
, CAST(MULTISET(SELECT VALUE(OG)
FROM KU$_OBJGRANT_VIEW OG
, KU$_SCHEMAOBJ_VIEW PO
WHERE OG.BASE_OBJ.OBJ_NUM = SYS.DBMS_METADATA_UTIL.REF_PAR_PARENT(T.OBJ#) AND PO.OBJ_NUM = OG.BASE_OBJ.OBJ_NUM AND OG.PRIVNAME = 'REFERENCES' AND OG.BASE_OBJ.NAME = PO.NAME ORDER BY OG.WGO DESC) AS KU$_OBJGRANT_LIST_T)
, (SELECT VALUE(S)
FROM KU$_STORAGE_VIEW S
WHERE T.FILE# = S.FILE_NUM
AND T.BLOCK# = S.BLOCK_NUM
AND T.TS# = S.TS_NUM)
, (SELECT VALUE(S)
FROM KU$_DEFERRED_STG_VIEW S
WHERE S.OBJ_NUM = T.OBJ#)
, TS.NAME
, TS.BLOCKSIZE
, T.DATAOBJ#
, T.BOBJ#
, T.TAB#
, T.COLS
, T.CLUCOLS
, (SELECT VALUE(CL)
FROM KU$_TABCLUSTER_VIEW CL
WHERE CL.OBJ_NUM = T.OBJ#)
, (SELECT VALUE(FB)
FROM KU$_FBA_VIEW FB
WHERE FB.OBJ_NUM = T.OBJ#)
, (SELECT VALUE(FB)
FROM KU$_FBA_PERIOD_VIEW FB
WHERE FB.OBJ_NUM = T.OBJ#)
, (SELECT VALUE(CZ)
FROM KU$_CLST_VIEW CZ
WHERE CZ.OBJ_NUM = T.OBJ#)
, CAST( MULTISET(SELECT *
FROM KU$_ILM_POLICY_VIEW P
WHERE P.OBJ_NUM = T.OBJ# ORDER BY P.POLICY_NUM ) AS KU$_ILM_POLICY_LIST_T )
, T.PCTFREE$
, T.PCTUSED$
, T.INITRANS
, T.MAXTRANS
, T.FLAGS
, REPLACE(T.AUDIT$
, CHR(0)
, '-')
, T.ROWCNT
, T.BLKCNT
, T.EMPCNT
, T.AVGSPC
, T.CHNCNT
, T.AVGRLN
, T.AVGSPC_FLB
, T.FLBCNT
, TO_CHAR(T.ANALYZETIME
, 'YYYY/MM/DD HH24:MI:SS')
, T.SAMPLESIZE
, T.DEGREE
, T.INSTANCES
, T.INTCOLS
, T.KERNELCOLS
, BITAND(T.PROPERTY
, 4294967295)
, TRUNC(T.PROPERTY / POWER(2
, 32))
, 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')
, (SELECT SYS.DBMS_METADATA_UTIL.HAS_TSTZ_COLS(T.OBJ#)
FROM DUAL)
, CASE WHEN (EXISTS (SELECT Q.OBJ#
FROM SYS.OPQTYPE$ Q
WHERE Q.OBJ#=T.OBJ#
AND Q.TYPE=1)) /* XMLTYPE COL */ THEN DBMS_METADATA_UTIL.GET_XMLCOLSET(O.OBJ_NUM) ELSE NULL END
, CASE WHEN (EXISTS (SELECT Q.OBJ#
FROM SYS.OPQTYPE$ Q
WHERE Q.OBJ#=T.OBJ#
AND Q.TYPE=1)) /* XMLTYPE COL */ THEN DBMS_METADATA_UTIL.GET_XMLHIERARCHY(O.OWNER_NAME
, O.NAME) ELSE NULL END
, T.TRIGFLAG
, T.SPARE1
, T.SPARE2
, T.SPARE3
, T.SPARE4
, T.SPARE5
, TO_CHAR(T.SPARE6
, 'YYYY/MM/DD HH24:MI:SS')
, DECODE(BITAND(T.TRIGFLAG
, 65536)
, 65536
, (SELECT E.ENCALG
FROM SYS.ENC$ E
WHERE E.OBJ#=T.OBJ#)
, NULL)
, DECODE(BITAND(T.TRIGFLAG
, 65536)
, 65536
, (SELECT E.INTALG
FROM SYS.ENC$ E
WHERE E.OBJ#=T.OBJ#)
, NULL)
, CAST( MULTISET(SELECT *
FROM KU$_PCOLUMN_VIEW C
WHERE C.OBJ_NUM = T.OBJ# ORDER BY C.COL_NUM
, C.INTCOL_NUM ) AS KU$_PCOLUMN_LIST_T )
, CAST( MULTISET(SELECT *
FROM KU$_IM_COLSEL_VIEW IMC
WHERE IMC.OBJ_NUM = T.OBJ# ) AS KU$_IM_COLSEL_LIST_T )
, (SELECT VALUE(NT)
FROM KU$_NT_PARENT_VIEW NT
WHERE NT.OBJ_NUM = T.OBJ#)
, CAST( MULTISET(SELECT *
FROM KU$_CONSTRAINT0_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ#
AND CON.CONTYPE NOT IN (7
, 11) ) AS KU$_CONSTRAINT0_LIST_T )
, CAST( MULTISET(SELECT *
FROM KU$_CONSTRAINT1_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ# ) AS KU$_CONSTRAINT1_LIST_T )
, CAST( MULTISET(SELECT *
FROM KU$_CONSTRAINT2_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ# ) AS KU$_CONSTRAINT2_LIST_T )
, CAST( MULTISET(SELECT *
FROM KU$_PKREF_CONSTRAINT_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ# ) AS KU$_PKREF_CONSTRAINT_LIST_T )
, (SELECT VALUE(PO)
FROM KU$_TAB_PARTOBJ_VIEW PO
WHERE T.OBJ# = PO.OBJ_NUM)
, (SELECT VALUE(ETV)
FROM KU$_EXTTAB_VIEW ETV
WHERE ETV.OBJ_NUM = O.OBJ_NUM)
, (SELECT VALUE(OTV)
FROM KU$_CUBE_TAB_VIEW OTV
WHERE OTV.OBJ_NUM = O.OBJ_NUM)
, SYS.DBMS_METADATA_UTIL.REF_PAR_LEVEL(T.OBJ#)
FROM KU$_SCHEMAOBJ_VIEW O
, TAB$ T
, TS$ TS
WHERE T.OBJ# = O.OBJ_NUM
AND T.TS# = TS.TS#
AND BITAND(T.PROPERTY
, 32+64+128+256+512) = 32 /* PARTITIONED (32) */ /* BUT NOT IOT */ /* MUTUALLY EXCLUSIVE WITH KU$_PHTABLE
AND KU$_PFHTABLE */
AND EXISTS( SELECT *
FROM PARTOBJ$ PO
WHERE PO.OBJ# = T.OBJ#
AND PO.PARTTYPE = 5)
AND (SYS_CONTEXT('USERENV'
, 'CURRENT_USERID') IN (O.OWNER_NUM
, 0) OR EXISTS ( SELECT *
FROM SYS.SESSION_ROLES
WHERE ROLE='SELECT_CATALOG_ROLE' ))