select u.name, o.name, 'SYS', xt.type$, 'SYS', xt.default_dir,
decode(xt.reject_limit, 2147483647, 'UNLIMITED', xt.reject_limit),
decode(xt.par_type, 1, 'BLOB', 2, 'CLOB', 'UNKNOWN'),
case when xt.par_type = 2 then xt.param_clob else NULL end,
decode(xt.property, 2, 'REFERENCED', 1, 'ALL', 'UNKNOWN')
from sys.external_tab$ xt, sys.obj$ o, sys.user$ u
where o.owner# = u.user#
and o.obj# = xt.obj#
and ( o.owner# = userenv('SCHEMAID')
or o.obj# in
( select oa.obj# from sys.objauth$ oa
where grantee# in (select kzsrorol from x$kzsro)
)
or /* user has system privileges */
exists ( select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-397/* READ ANY TABLE */)
)
)
SELECT U.NAME
, O.NAME
, 'SYS'
, XT.TYPE$
, 'SYS'
, XT.DEFAULT_DIR
,
DECODE(XT.REJECT_LIMIT
, 2147483647
, 'UNLIMITED'
, XT.REJECT_LIMIT)
,
DECODE(XT.PAR_TYPE
, 1
, 'BLOB'
, 2
, 'CLOB'
, 'UNKNOWN')
,
CASE WHEN XT.PAR_TYPE = 2 THEN XT.PARAM_CLOB ELSE NULL END
,
DECODE(XT.PROPERTY
, 2
, 'REFERENCED'
, 1
, 'ALL'
, 'UNKNOWN')
FROM SYS.EXTERNAL_TAB$ XT
, SYS.OBJ$ O
, SYS.USER$ U
WHERE O.OWNER# = U.USER#
AND O.OBJ# = XT.OBJ#
AND ( O.OWNER# = USERENV('SCHEMAID')
OR O.OBJ# IN
( SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
)
OR /* USER HAS SYSTEM PRIVILEGES */
EXISTS ( SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-45 /* LOCK ANY TABLE */
,
-47 /* SELECT ANY TABLE */
,
-397/* READ ANY TABLE */)
)
)
|
|
|