select '1', '0', o.obj#,
-- if this is a nested table, get parent table, otherwise get table.
decode(bitand(t.property , 8192), 8192,
(select value(oo) from ku$_schemaobj_view oo
where oo.obj_num = dbms_metadata_util.get_anc(o.obj#)),
(select value(sov) from ku$_schemaobj_view sov
where sov.obj_num = o.obj#)),
value(tosv),
cast(multiset(select value(psv)
from sys.ku$_10_1_ptab_stats_view psv
where psv.bobj_num = o.obj#)
as ku$_10_1_ptab_stats_list_t)
from sys.obj$ o, sys.tab$ t, ku$_10_1_tab_only_stats_view tosv
where tosv.obj_num = o.obj# and
o.obj# = t.obj# and
o.type# = 2 and
-- Bug 8794227: Get the table stats info when stats deleted
-- but locked based on trigflag value.
BITAND(t.property, 512) = 0 AND -- NOT AN IOT MAPPING TABLE
(BITAND(t.flags, 16) != 0 or
bitand(t.trigflag,67108864) != 0)
and (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (o.owner#, 0) OR
EXISTS ( SELECT * FROM sys.session_roles
WHERE role='SELECT_CATALOG_ROLE' ))
SELECT '1'
, '0'
, O.OBJ#
,
-- IF THIS IS A NESTED TABLE
, GET PARENT TABLE
, OTHERWISE GET TABLE.
DECODE(BITAND(T.PROPERTY
, 8192)
, 8192
,
(SELECT VALUE(OO)
FROM KU$_SCHEMAOBJ_VIEW OO
WHERE OO.OBJ_NUM = DBMS_METADATA_UTIL.GET_ANC(O.OBJ#))
,
(SELECT VALUE(SOV)
FROM KU$_SCHEMAOBJ_VIEW SOV
WHERE SOV.OBJ_NUM = O.OBJ#))
,
VALUE(TOSV)
,
CAST(MULTISET(SELECT VALUE(PSV)
FROM SYS.KU$_10_1_PTAB_STATS_VIEW PSV
WHERE PSV.BOBJ_NUM = O.OBJ#)
AS KU$_10_1_PTAB_STATS_LIST_T)
FROM SYS.OBJ$ O
, SYS.TAB$ T
, KU$_10_1_TAB_ONLY_STATS_VIEW TOSV
WHERE TOSV.OBJ_NUM = O.OBJ# AND
O.OBJ# = T.OBJ# AND
O.TYPE# = 2 AND
-- BUG 8794227: GET THE TABLE STATS INFO WHEN STATS DELETED
-- BUT LOCKED BASED ON TRIGFLAG VALUE.
BITAND(T.PROPERTY
, 512) = 0
AND -- NOT AN IOT MAPPING TABLE
(BITAND(T.FLAGS
, 16) != 0 OR
BITAND(T.TRIGFLAG
, 67108864) != 0)
AND (SYS_CONTEXT('USERENV'
, 'CURRENT_USERID') IN (O.OWNER#
, 0) OR
EXISTS ( SELECT *
FROM SYS.SESSION_ROLES
WHERE ROLE='SELECT_CATALOG_ROLE' ))
|
|
|