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.

VIEW: SYS.KU$_PIOTABLE_VIEW

Source

View Text - Preformatted


select '2','5',
         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),
         null,
         (select value(s) from ku$_storage_view s
          where i.file# = s.file_num
          and i.block#  = s.block_num
          and i.ts#     = s.ts_num),
         (select value(s) from ku$_deferred_stg_view s
          where s.obj_num = i.obj#),
         ts.name, ts.blocksize,
         i.dataobj#, t.bobj#, t.tab#, t.cols,
         (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#),
         t.clucols, i.pctfree$, i.initrans, i.maxtrans,
         mod(i.pctthres$,256), i.spare2, 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),
         (select c.name from col$ c
                 where c.obj# = t.obj#
                 and   c.col# = i.trunccnt and i.trunccnt != 0
                 and   bitand(c.property,1)=0),
         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(ov) from ku$_ov_table_view ov
          where ov.bobj_num = t.obj#),
         (select value (po) from ku$_iot_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),
         0
  from ku$_schemaobj_view o, tab$ t, ind$ i, ts$ ts
  where t.obj# = o.obj_num
        and t.pctused$ = i.obj#          -- For IOTs, pctused has index obj#
        and bitand(t.property, 32+64+512)  = 32+64  -- PIOT but not overflow
        AND i.ts# = ts.ts#
        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'
, '5'
, 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)
, NULL
, (SELECT VALUE(S)
FROM KU$_STORAGE_VIEW S
WHERE I.FILE# = S.FILE_NUM
AND I.BLOCK# = S.BLOCK_NUM
AND I.TS# = S.TS_NUM)
, (SELECT VALUE(S)
FROM KU$_DEFERRED_STG_VIEW S
WHERE S.OBJ_NUM = I.OBJ#)
, TS.NAME
, TS.BLOCKSIZE
, I.DATAOBJ#
, T.BOBJ#
, T.TAB#
, T.COLS
, (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#)
, T.CLUCOLS
, I.PCTFREE$
, I.INITRANS
, I.MAXTRANS
, MOD(I.PCTTHRES$
, 256)
, I.SPARE2
, 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)
, (SELECT C.NAME
FROM COL$ C
WHERE C.OBJ# = T.OBJ#
AND C.COL# = I.TRUNCCNT
AND I.TRUNCCNT != 0
AND BITAND(C.PROPERTY
, 1)=0)
, 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(OV)
FROM KU$_OV_TABLE_VIEW OV
WHERE OV.BOBJ_NUM = T.OBJ#)
, (SELECT VALUE (PO)
FROM KU$_IOT_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)
, 0
FROM KU$_SCHEMAOBJ_VIEW O
, TAB$ T
, IND$ I
, TS$ TS
WHERE T.OBJ# = O.OBJ_NUM
AND T.PCTUSED$ = I.OBJ# -- FOR IOTS
, PCTUSED HAS INDEX OBJ#
AND BITAND(T.PROPERTY
, 32+64+512) = 32+64 -- PIOT BUT NOT OVERFLOW
AND I.TS# = TS.TS#
AND (SYS_CONTEXT('USERENV'
, 'CURRENT_USERID') IN (O.OWNER_NUM
, 0) OR EXISTS ( SELECT *
FROM SYS.SESSION_ROLES
WHERE ROLE='SELECT_CATALOG_ROLE' ))