select '2', '1', 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#)),
decode(bitand(t.property , 8192), 8192, o.name, null),
cast(multiset(select value(tcv) from sys.ku$_tab_col_view tcv
where tcv.obj_num = o.obj#)
as ku$_tab_col_list_t),
value(tosv),
cast(multiset(select value(psv)
from sys.ku$_ptab_stats_view psv
where psv.bobj_num = o.obj#)
as ku$_ptab_stats_list_t)
from sys.obj$ o, sys.tab$ t, ku$_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 '2'
, '1'
, 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#))
,
DECODE(BITAND(T.PROPERTY
, 8192)
, 8192
, O.NAME
, NULL)
,
CAST(MULTISET(SELECT VALUE(TCV)
FROM SYS.KU$_TAB_COL_VIEW TCV
WHERE TCV.OBJ_NUM = O.OBJ#)
AS KU$_TAB_COL_LIST_T)
,
VALUE(TOSV)
,
CAST(MULTISET(SELECT VALUE(PSV)
FROM SYS.KU$_PTAB_STATS_VIEW PSV
WHERE PSV.BOBJ_NUM = O.OBJ#)
AS KU$_PTAB_STATS_LIST_T)
FROM SYS.OBJ$ O
, SYS.TAB$ T
, KU$_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' ))
|
|
|