43 lines
1.4 KiB
SQL
43 lines
1.4 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Oracle Internal Schemas
|
|
* Versions : 9.2 or higher
|
|
* Parameters : NONE
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 10-Mar-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
SELECT DISTINCT name
|
|
FROM
|
|
(SELECT u.name
|
|
FROM registry$ r
|
|
, user$ u
|
|
WHERE r.status IN (1,3,5)
|
|
AND r.namespace = 'SERVER'
|
|
AND r.schema# =u.user#
|
|
&&_IF_ORA_10gR2_OR_HIGHER UNION
|
|
&&_IF_ORA_10gR2_OR_HIGHER SELECT u.name -- get additional component schemas
|
|
&&_IF_ORA_10gR2_OR_HIGHER FROM registry$ r
|
|
&&_IF_ORA_10gR2_OR_HIGHER , registry$schemas s
|
|
&&_IF_ORA_10gR2_OR_HIGHER , user$ u
|
|
&&_IF_ORA_10gR2_OR_HIGHER WHERE r.status IN (1,3,5)
|
|
&&_IF_ORA_10gR2_OR_HIGHER AND r.namespace = 'SERVER'
|
|
&&_IF_ORA_10gR2_OR_HIGHER AND r.cid =s.cid
|
|
&&_IF_ORA_10gR2_OR_HIGHER AND s.schema# =u.user#
|
|
)
|
|
ORDER BY name
|
|
;
|
|
|
|
|
|
@@footer |