VIEW: SYS.ALL_EXTERNAL_LOCATIONS

Source

View Text - Preformatted

select u.name, o.name, xl.name, 'SYS', nvl(xl.dir, xt.default_dir)
from sys.external_location$ xl, sys.user$ u, sys.obj$ o, sys.external_tab$ xt
where o.owner# = u.user#
  and o.obj#   = xl.obj#
  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
, XL.NAME
, 'SYS'
, NVL(XL.DIR
, XT.DEFAULT_DIR) FROM SYS.EXTERNAL_LOCATION$ XL
, SYS.USER$ U
, SYS.OBJ$ O
, SYS.EXTERNAL_TAB$ XT WHERE O.OWNER# = U.USER#
AND O.OBJ# = XL.OBJ#
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 */) ) )