select t.obj#,dbms_metadata.get_partn(1,tp.bo#,tp.part#),
cast(multiset(select
obj_num,
part_num,
intcol_num,
ntab_num,
schema_obj,
col,
property,
flags,
hnt
from ku$_ntpart_view ntp
where ntp.part_num=dbms_metadata.get_partn(1,tp.bo#,tp.part#) and
ntp.obj_num in (
select obj# from ntab$ nt
start with nt.obj#=t.obj#
connect by prior nt.ntab#=nt.obj#)
) as ku$_ntpart_list_t
)
from tab$ t, tabpart$ tp
where tp.bo# = t.obj# and
bitand(t.property,32+4) = 32+4 -- has nested tables, and is partitioned
SELECT T.OBJ#
, DBMS_METADATA.GET_PARTN(1
, TP.BO#
, TP.PART#)
,
CAST(MULTISET(SELECT
OBJ_NUM
,
PART_NUM
,
INTCOL_NUM
,
NTAB_NUM
,
SCHEMA_OBJ
,
COL
,
PROPERTY
,
FLAGS
,
HNT
FROM KU$_NTPART_VIEW NTP
WHERE NTP.PART_NUM=DBMS_METADATA.GET_PARTN(1
, TP.BO#
, TP.PART#) AND
NTP.OBJ_NUM IN (
SELECT OBJ#
FROM NTAB$ NT
START WITH NT.OBJ#=T.OBJ#
CONNECT BY PRIOR NT.NTAB#=NT.OBJ#)
) AS KU$_NTPART_LIST_T
)
FROM TAB$ T
, TABPART$ TP
WHERE TP.BO# = T.OBJ# AND
BITAND(T.PROPERTY
, 32+4) = 32+4 -- HAS NESTED TABLES
,
AND IS PARTITIONED
|
|
|