VIEW: SYS.KU$_TAB_PART_VIEW

Source

View Text - Preformatted

select tp.obj#, value(o),
         ts.name, ts.blocksize,
         (select value(s) from ku$_storage_view s
          where tp.file#  = s.file_num
          and   tp.block# = s.block_num
          and   tp.ts#    = s.ts_num),
         (select value(s) from ku$_deferred_stg_view s
          where s.obj_num = tp.obj#),
         tp.dataobj#, tp.bo#,
         dbms_metadata.get_partn(1,tp.bo#,tp.part#),
         tp.hiboundlen,
         sys.dbms_metadata_util.long2varchar(tp.hiboundlen,
                                        'SYS.TABPART$',
                                        'HIBOUNDVAL',
                                        tp.rowid),
         cast(multiset(select * from ku$_lobfrag_view lf
                        where lf.part_obj_num=tp.obj#
                        order by lf.intcol_num
                      ) as ku$_lobfrag_list_t
             ),
         (select value(ntp) from ku$_ntpart_parent_view ntp
          where ntp.obj_num = tp.bo# and
                ntp.part_num=(dbms_metadata.get_partn(1,tp.bo#,tp.part#))),
         cast( multiset(select * from ku$_ilm_policy_view p
                        where p.obj_num = tp.obj#
                        order by p.policy_num
                        ) as ku$_ilm_policy_list_t
              ),
         tp.pctfree$, tp.pctused$, tp.initrans,
         tp.maxtrans, tp.flags,
         to_char(tp.analyzetime,'YYYY/MM/DD HH24:MI:SS'),
         tp.samplesize, tp.rowcnt,
         tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln, tp.spare1,
         tp.spare2, tp.spare3,
         bhiboundval,
         tp.part#   -- <<< be carefull! this is 'physical' partition number
  from ku$_schemaobj_view o, tabpart$ tp, ts$ ts
  where tp.obj# = o.obj_num
        AND tp.ts# = ts.ts#

View Text - HTML Formatted

SELECT TP.OBJ#
, VALUE(O)
, TS.NAME
, TS.BLOCKSIZE
, (SELECT VALUE(S)
FROM KU$_STORAGE_VIEW S
WHERE TP.FILE# = S.FILE_NUM
AND TP.BLOCK# = S.BLOCK_NUM
AND TP.TS# = S.TS_NUM)
, (SELECT VALUE(S)
FROM KU$_DEFERRED_STG_VIEW S
WHERE S.OBJ_NUM = TP.OBJ#)
, TP.DATAOBJ#
, TP.BO#
, DBMS_METADATA.GET_PARTN(1
, TP.BO#
, TP.PART#)
, TP.HIBOUNDLEN
, SYS.DBMS_METADATA_UTIL.LONG2VARCHAR(TP.HIBOUNDLEN
, 'SYS.TABPART$'
, 'HIBOUNDVAL'
, TP.ROWID)
, CAST(MULTISET(SELECT *
FROM KU$_LOBFRAG_VIEW LF
WHERE LF.PART_OBJ_NUM=TP.OBJ# ORDER BY LF.INTCOL_NUM ) AS KU$_LOBFRAG_LIST_T )
, (SELECT VALUE(NTP)
FROM KU$_NTPART_PARENT_VIEW NTP
WHERE NTP.OBJ_NUM = TP.BO# AND NTP.PART_NUM=(DBMS_METADATA.GET_PARTN(1
, TP.BO#
, TP.PART#)))
, CAST( MULTISET(SELECT *
FROM KU$_ILM_POLICY_VIEW P
WHERE P.OBJ_NUM = TP.OBJ# ORDER BY P.POLICY_NUM ) AS KU$_ILM_POLICY_LIST_T )
, TP.PCTFREE$
, TP.PCTUSED$
, TP.INITRANS
, TP.MAXTRANS
, TP.FLAGS
, TO_CHAR(TP.ANALYZETIME
, 'YYYY/MM/DD HH24:MI:SS')
, TP.SAMPLESIZE
, TP.ROWCNT
, TP.BLKCNT
, TP.EMPCNT
, TP.AVGSPC
, TP.CHNCNT
, TP.AVGRLN
, TP.SPARE1
, TP.SPARE2
, TP.SPARE3
, BHIBOUNDVAL
, TP.PART# -- <<< BE CAREFULL! THIS IS 'PHYSICAL' PARTITION NUMBER
FROM KU$_SCHEMAOBJ_VIEW O
, TABPART$ TP
, TS$ TS
WHERE TP.OBJ# = O.OBJ_NUM
AND TP.TS# = TS.TS#