通過shell指令碼得到資料字典的資訊

在平時的工作中，可能需要查詢一些資料字典的資訊，比如資料字典對應的基表資訊，可以得到更多資料庫內部的一些詳細資訊。
比如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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1256265/，如需轉載，請註明出處，否則將追究法律責任。