DBA Data[Home] [Help]

VIEW: SYS.ALL_EXTERNAL_TABLES

Source

View Text - Preformatted

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 */)
                 )
      )
View Text - HTML Formatted

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 */) ) )