比如user_objects这个数据字典视图，里面可能就包含很多的信息。
首先可以得到，它是一个视图，然后在public上创建了对应的同义词，但是在不用用户使用的使用，查出的结果会不相同，这个其实是根据类似环境变量的一套东西来实现的。
where l.owner# = userenv('SCHEMAID')，它会锁定当前的schema。
OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_NAME OBJECT_TYPE
------------------------------ ---------- -------------- ------------------------------ -------------------
SYS 3305 USER_OBJECTS VIEW
PUBLIC 3306 USER_OBJECTS SYNONYM
synonym_details
OWNER SYNONYM_NAME
------------------------------ ------------------------------
PUBLIC USER_OBJECTS
view_details
VIEW_NAME TEXT
------------------------------ --------------------------------------------------------------------------------
USER_OBJECTS 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 '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, 'EDITION', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
100, 'FILE WATCHER', 101, 'DESTINATION',
'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'),
o.namespace,
o.defining_edition
from sys."_CURRENT_EDITION_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, 8, 9))))
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
and bitand(o.flags, 128) = 0
union all
select l.name, NULL, to_number(null), to_number(null),
'DATABASE LINK',
l.ctime, to_date(null), NULL, 'VALID', 'N', 'N', 'N', NULL, NULL
from sys.link$ l
where l.owner# = userenv('SCHEMAID')
实现的脚本如下：
sqlplus -s / as sysdba COL OBJECT_NAME FORMAT A30
COL DB_LINK FORMAT A20
COL OWNER FORMAT A30
COL TABLE_OWNER FORMAT A30
COL TABLE_NAME FORMAT A30
SET LINESIZE 200
SET PAGES 100
SET LONG 999999
prompt object_details
SELECT OWNER,OBJECT_ID,DATA_OBJECT_ID,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME=UPPER('$1');
prompt synonym_details
SELECT OWNER,SYNONYM_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME=upper('$1');
prompt view_details
SELECT VIEW_NAME,TEXT FROM DBA_VIEWS WHERE VIEW_NAME=upper('$1');
EOF
exit
