-- Procedure, Package 등의 소스 조회
SELECT *
FROM USER_SOURCE
WHERE NAME = 'SOURCE_NAME'
SELECT *
FROM USER_SOURCE
WHERE NAME = 'SOURCE_NAME'
-- Table, View 등의 DB Object 조회
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_TYPE LIKE '%TABLE%'
ORDER BY OBJECT_TYPE, OBJECT_NAME
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_TYPE LIKE '%TABLE%'
ORDER BY OBJECT_TYPE, OBJECT_NAME
-- USER_SOURCE
CREATE OR REPLACE VIEW sys.user_source AS
SELECT o.name,
decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',
'UNDEFINED'),
s.line, s.source
FROM sys.obj$ o, sys.source$ s
WHERE o.obj# = s.obj#
AND ( o.type# in (7, 8, 9, 11, 12, 14) OR
( o.type# = 13 AND o.subname is null))
AND o.owner# = userenv('SCHEMAID')
union ALL
SELECT o.name, 'JAVA SOURCE', s.joxftlno, s.joxftsrc
FROM sys.obj$ o, x$joxfs s
WHERE o.obj# = s.joxftobn
AND o.type# = 28
AND o.owner# = userenv('SCHEMAID')
CREATE OR REPLACE VIEW sys.user_source AS
SELECT o.name,
decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',
'UNDEFINED'),
s.line, s.source
FROM sys.obj$ o, sys.source$ s
WHERE o.obj# = s.obj#
AND ( o.type# in (7, 8, 9, 11, 12, 14) OR
( o.type# = 13 AND o.subname is null))
AND o.owner# = userenv('SCHEMAID')
union ALL
SELECT o.name, 'JAVA SOURCE', s.joxftlno, s.joxftsrc
FROM sys.obj$ o, x$joxfs s
WHERE o.obj# = s.joxftobn
AND o.type# = 28
AND o.owner# = userenv('SCHEMAID')
-- USER_OBJECTS
CREATE OR REPLACE VIEW SYS.USER_OBJECTS AS
SELECT o.name, o.subname, o.obj#, o.dataobj#,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, NVL((SELECT DISTINCT 'REWRITE EQUIVALENCE'
FROM sum$ s
WHERE s.obj#=o.obj#
AND bitand(s.xpflags, 8388608) = 8388608),
'MATERIALIZED VIEW'),
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP',
'UNDEFINED'),
o.ctime, o.mtime,
to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
decode(o.STATUS, 0, 'N/A', 1, 'VALID', 'INVALID'),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N')
FROM sys.obj$ o
WHERE o.owner# = userenv('SCHEMAID')
AND o.linkname IS NULL
AND (o.type# not in (1 /* INDEX - handled below */,
10 /* NON-EXISTENT */)
OR
(o.type# = 1 and 1 = (select 1
FROM sys.ind$ i
WHERE i.obj# = o.obj#
AND i.type# in (1, 2, 3, 4, 6, 7, 9))))
AND o.name != '_NEXT_OBJECT'
AND o.name != '_default_auditing_options_'
union ALL
SELECT l.name, NULL, to_number(NULL), to_number(NULL),
'DATABASE LINK',
l.ctime, to_date(NULL), NULL, 'VALID', 'N', 'N', 'N'
FROM sys.link$ l
WHERE l.owner# = userenv('SCHEMAID')
CREATE OR REPLACE VIEW SYS.USER_OBJECTS AS
SELECT o.name, o.subname, o.obj#, o.dataobj#,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, NVL((SELECT DISTINCT 'REWRITE EQUIVALENCE'
FROM sum$ s
WHERE s.obj#=o.obj#
AND bitand(s.xpflags, 8388608) = 8388608),
'MATERIALIZED VIEW'),
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP',
'UNDEFINED'),
o.ctime, o.mtime,
to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
decode(o.STATUS, 0, 'N/A', 1, 'VALID', 'INVALID'),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N')
FROM sys.obj$ o
WHERE o.owner# = userenv('SCHEMAID')
AND o.linkname IS NULL
AND (o.type# not in (1 /* INDEX - handled below */,
10 /* NON-EXISTENT */)
OR
(o.type# = 1 and 1 = (select 1
FROM sys.ind$ i
WHERE i.obj# = o.obj#
AND i.type# in (1, 2, 3, 4, 6, 7, 9))))
AND o.name != '_NEXT_OBJECT'
AND o.name != '_default_auditing_options_'
union ALL
SELECT l.name, NULL, to_number(NULL), to_number(NULL),
'DATABASE LINK',
l.ctime, to_date(NULL), NULL, 'VALID', 'N', 'N', 'N'
FROM sys.link$ l
WHERE l.owner# = userenv('SCHEMAID')
댓글을 달아 주세요