select '1','2',
ip.obj#,
(select o1.obj#
from obj$ o1
where o1.name=bo.name
and o1.subname=o.subname
and o1.owner#=o.owner_num
and o1.type# in (19,34)), /* see note above */
o.subname,
NULL,
po.parttype,
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', /* partitioned table cannot have column with LONG datatype */
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(ip.obj#,5) from dual),
value(bo),
-- if this is a secondary table, get domidx obj and ancestor obj
decode(bitand(bo.flags, 16), 16,
(select value(oo) from ku$_schemaobj_view oo, secobj$ s
where bo.obj_num=s.secobj#
and oo.obj_num=s.obj#
and rownum < 2),
null),
decode(bitand(bo.flags, 16), 16,
(select value(oo) from ku$_schemaobj_view oo, ind$ i, secobj$ s
where bo.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 -- note: piot cannot be ref partitioned
from ku$_schemaobj_view o, ku$_schemaobjnum_view bo,
ku$_unload_method_view um, tab$ t,
ind$ i, indpart$ ip, ts$ ts, partobj$ po
where ip.obj# = o.obj_num
AND o.type_num = 20 -- index partition
AND bo.obj_num = po.obj#
AND ip.bo#=i.obj#
AND i.type#=4 -- iot index
AND i.bo#=t.obj#
AND t.obj# = um.obj_num
AND ip.ts# = ts.ts#
AND bo.obj_num=i.bo#
AND (bitand(bo.flags,16)!=16
OR sys.dbms_metadata.oktoexp_2ndary_table(bo.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' ))
SELECT '1'
, '2'
,
IP.OBJ#
,
(SELECT O1.OBJ#
FROM OBJ$ O1
WHERE O1.NAME=BO.NAME
AND O1.SUBNAME=O.SUBNAME
AND O1.OWNER#=O.OWNER_NUM
AND O1.TYPE# IN (19
, 34))
, /* SEE NOTE ABOVE */
O.SUBNAME
,
NULL
,
PO.PARTTYPE
,
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'
, /* PARTITIONED TABLE CANNOT HAVE COLUMN WITH LONG DATATYPE */
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(IP.OBJ#
, 5)
FROM DUAL)
,
VALUE(BO)
,
-- IF THIS IS A SECONDARY TABLE
, GET DOMIDX OBJ
AND ANCESTOR OBJ
DECODE(BITAND(BO.FLAGS
, 16)
, 16
,
(SELECT VALUE(OO)
FROM KU$_SCHEMAOBJ_VIEW OO
, SECOBJ$ S
WHERE BO.OBJ_NUM=S.SECOBJ#
AND OO.OBJ_NUM=S.OBJ#
AND ROWNUM < 2)
,
NULL)
,
DECODE(BITAND(BO.FLAGS
, 16)
, 16
,
(SELECT VALUE(OO)
FROM KU$_SCHEMAOBJ_VIEW OO
, IND$ I
, SECOBJ$ S
WHERE BO.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 -- NOTE: PIOT CANNOT BE REF PARTITIONED
FROM KU$_SCHEMAOBJ_VIEW O
, KU$_SCHEMAOBJNUM_VIEW BO
,
KU$_UNLOAD_METHOD_VIEW UM
, TAB$ T
,
IND$ I
, INDPART$ IP
, TS$ TS
, PARTOBJ$ PO
WHERE IP.OBJ# = O.OBJ_NUM
AND O.TYPE_NUM = 20 -- INDEX PARTITION
AND BO.OBJ_NUM = PO.OBJ#
AND IP.BO#=I.OBJ#
AND I.TYPE#=4 -- IOT INDEX
AND I.BO#=T.OBJ#
AND T.OBJ# = UM.OBJ_NUM
AND IP.TS# = TS.TS#
AND BO.OBJ_NUM=I.BO#
AND (BITAND(BO.FLAGS
, 16)!=16
OR SYS.DBMS_METADATA.OKTOEXP_2NDARY_TABLE(BO.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' ))
|
|
|