實例講解如何查找某個對象的定義情況

2024-07-21 02:42:02

字體：大中小

來源：轉載

供稿：網友

在實際的工作中，我們經常需要查找某個對象的定義的情況，本文中我們將針對不同類型的對象分別進行講解：

一、V$視圖和X$視圖

普通的用戶不能訪問V$視圖：

SQL> conn lunar/lunar@test1已連接。SQL> select * from user_sys_PRivs;USERNAME       PRIVILEGE   ADMIN_OPTION---------- -------------- ------------SQL> select * from user_role_privs;USERNAME      GRANTED_ROLE   ADMIN_OPTION DEFAULT_ROLE OS_GRANTED----------- --------------- ------------ ------------ ----------LUNAR       CONNECT      NO           YES          NOLUNAR       RESOURCE     NO           YES          NOPUBLIC      PLUSTRACE    NO           YES          NOSQL> select count(*) from v$fixed_table;select count(*) from v$fixed_tableORA-00942: 表或視圖不存在必須授權：SQL> conn /@test1 as sysdba已連接。SQL> grant select on v_$fixed_table to lunar;授權成功。SQL> conn lunar/lunar@test1已連接。SQL>

我們可以發現，得到授權的普通用戶仍然只能訪問V$開頭的視圖，而不能直接訪問V_$開頭的視圖，因為實際上V$視圖是V_$視圖的公有同義詞（PUBLIC SYNONYM）要想訪問V_$必須帶上SYS.V_$。

例如:

SQL> select count(*) from v$fixed_table;  COUNT(*)----------       912SQL> select count(*) from v_$fixed_table;select count(*) from v_$fixed_tableORA-00942: 表或視圖不存在SQL> select count(*) from sys.v_$fixed_table;  COUNT(*)----------       912SQL>

與此同時，也可以授予用戶SELECT any table權限，這樣這個用戶就可以訪問所有的V$視圖：

SQL> grant select any table to lunar;授權成功。SQL> select * from user_role_privs;USERNAME         GRANTED_ROLE       ADMIN_OPTION DEFAULT_ROLE OS_GRANTED------------ --------------------- ------------ ------------ ----------LUNAR            CONNECT             NO           YES          NOLUNAR            RESOURCE            NO           YES          NOPUBLIC           PLUSTRACE           NO           YES          NOSQL> select * from user_sys_privs;USERNAME         PRIVILEGE           ADMIN_OPTION------------ ----------------------- ------------LUNAR            SELECT ANY TABLE       NOSQL> select count(*) from v$fixed_table;  COUNT(*)----------       912SQL> select * from v$fixed_table where rownum<2;NAME       OBJECT_ID TYPE   TABLE_NUM---------- ---------- ----- ----------X$KQFTA    4294950912 TABLE     0SQL> select * from v_$fixed_table where rownum<2;select * from v_$fixed_table where rownum<2ORA-00942: 表或視圖不存在SQL> select * from sys.v_$fixed_table where rownum<2;NAME       OBJECT_ID  TYPE   TABLE_NUM---------- ---------- ----- ----------X$KQFTA    4294950912 TABLE      0SQL>

通過查詢V$FIXED_TABLE視圖，我們可以看到大部分V$視圖和一些X$視圖（還有一些Oracle未公開的視圖不在其中）。

有人要問，那么這些V$視圖又是有什么組成的呢？

通過查詢V$FIXED_VIEW_DEFINITION視圖，我們可以看到這些V$視圖的創建語句：

SQL> conn /@test1 as sysdba已連接。SQL> grant select any table to lunar;授權成功。SQL> conn lunar/lunar@test1已連接。SQL>SQL> set heading off echo off long 50000 pages 10000SQL> select * from v$fixed_view_definition where view_name='V$FIXED_TABLE';V$FIXED_TABLE                  select  NAME , OBJECT_ID , TYPE , TABLE_NUM from GV$FIXED_TABLE where inst_id = USERENV('Instance')SQL> select  NAME , OBJECT_ID , TYPE , TABLE_NUM from GV$FIXED_TABLE where inst_id = USERENV('Instance')

那么這個GV$FIXED_TABLE視圖的定義又是怎樣的呢？

SQL> select * from v$fixed_view_definition where view_name='GV$FIXED_TABLE';GV$FIXED_TABLE                 select inst_id,kqftanam, kqftaobj, 'TABLE', indx from x$kqfta union all select inst_id,kqfvinam, kqfviobj, 'VIEW', 65537 from x$kqfvi union all select inst_id,kqfdtnam, kqfdtobj, 'TABLE', 65537 from x$kqfdtSQL> select inst_id,kqftanam, kqftaobj, 'TABLE', indx from x$kqfta union all select inst_id,kqfvinam, kqfviobj, 'VIEW', 65537 from x$kqfvi union all select inst_id,kqfdtnam, kqfdtobj, 'TABLE', 65537 from x$kqfdt

此時我們就找到了創建一個V$視圖的最低層的信息，即一個V$視圖是由哪些X$表構成的。

要找到底層X$表的索引信息，可以查詢v$indexed_fixed_column：

SQL> desc v$indexed_fixed_columnName            Type         Nullable Default Comments --------------- ------------ -------- ------- -------- TABLE_NAME      VARCHAR2(30) Y                         INDEX_NUMBER    NUMBER       Y                         COLUMN_NAME     VARCHAR2(30) Y                         COLUMN_POSITION NUMBER       Y                         SQL> 例如：SQL> select * from v$indexed_fixed_column where table_name='X$KQFTA';TABLE_NAME          INDEX_NUMBER COLUMN_NAME  COLUMN_POSITION------------------ ------------ -------------- ---------------X$KQFTA               1 ADDR                    0X$KQFTA               2 INDX                    0SQL>

一般情況下，V$視圖和GV$視圖的定義是一樣的，只是GV$視圖中包含的實例id的信息，常用于OPS或者RAC的系統中，也有少數幾個V$視圖和GV$視圖的定義是有區別的，比如GV$PX_PROCESS和V$PX_PROCESS：

SQL> select * from v$fixed_view_definition where view_name='GV$PX_PROCESS';GV$PX_PROCESS                  select a.inst_id, a.kxfpdpnam,  decode(bitand(a.kxfpdpflg, 16), 0, 'INUSE', 'AVAILABLE'),  b.pid, a.kxfpdpspid, c.sid, c.serial#  from x$kxfpdp a, V$PROCESS b, V$session c  where bitand(kxfpdpflg, 8) != 0 and  a.kxfpdpspid = b.SPID anda.kxfpdpspid = c.PROCESS(+)SQL>select a.inst_id, a.kxfpdpnam,   decode(bitand(a.kxfpdpflg, 16), 0, 'IN USE', 'AVAILABLE'),   b.pid, a.kxfpdpspid, c.sid, c.serial#  from x$kxfpdp a, V$PROCESS b, V$SESSION c  where bitand(kxfpdpflg, 8) != 0 and  a.kxfpdpspid = b.SPID and  a.kxfpdpspid = c.PROCESS(+) SQL> select * from v$fixed_view_definition where view_name='V$PX_PROCESS';V$PX_PROCESS                   select  SERVER_NAME, STATUS, PID, SPID, SID, SERIAL#  from GV$PX_PROCESS where inst_id = USERENV('Instance')SQL>select  SERVER_NAME, STATUS, PID, SPID, SID, SERIAL#  from GV$PX_PROCESS where inst_id = USERENV('Instance')

二、數據字典的組成

下面我們再來將一個數據字典表的定義呢？

SQL> desc dba_viewsName             Type          Nullable  Default Comments                                                    ---------------- ------------ -------- ------- -------OWNER            VARCHAR2(30)           Owner of the view                                           VIEW_NAME        VARCHAR2(30)           Name of the view                                            TEXT_LENGTH      NUMBER         Y        Length of the view text                                     TEXT             LONG           Y        View text                                                   TYPE_TEXT_LENGTH NUMBER         Y        Length of the type clause of the object view                TYPE_TEXT        VARCHAR2(4000) Y        Type clause of the object view                              OID_TEXT_LENGTH  NUMBER         Y        Length of the WITH OBJECT OID clause of the object view     OID_TEXT         VARCHAR2(4000) Y        WITH OBJECT OID clause of the object view                   VIEW_TYPE_OWNER  VARCHAR2(30)   Y        Owner of the type of the view if the view is an object view VIEW_TYPE        VARCHAR2(30)   Y        Type of the view if the view is an object view              SUPERVIEW_NAME   VARCHAR2(30)   Y        Name of the superview, if view is a subview                 SQL> SQL> set heading off echo off long 1000000000 pages 10000SQL> select text from dba_views where view_name ='DBA_USERS';select u.name, u.user#, u.passWord,m.status,decode(u.astatus, 4, u.ltime,5, u.ltime,6, u.ltime,8, u.ltime,9, u.ltime,10, u.ltime, to_date(NULL)),decode(u.astatus,1, u.exptime,2, u.exptime,5, u.exptime,6, u.exptime,9, u.exptime,10, u.exptime,decode(u.ptime, '', to_date(NULL),decode(pr.limit#, 2147483647, to_date(NULL),decode(pr.limit#, 0,decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +dp.limit#/86400),u.ptime + pr.limit#/86400)))),dts.name, tts.name, u.ctime, p.name, u.defschclass, u.ext_usernamefrom sys.user$ u, sys.ts$ dts, sys.ts$ tts, sys.profname$ p,sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dpwhere u.datats# = dts.ts#and u.resource$ = p.profile#and u.tempts# = tts.ts#and u.astatus = m.status#and u.type# = 1and u.resource$ = pr.profile#and dp.profile# = 0and dp.type#=1and dp.resource#=1and pr.type# = 1and pr.resource# = 1SQL>

三、如何查找用戶自定義的某個表的定義？

在Oracle 9i前，我們可以使用下面的方法：

SQL> select substr(table_name,1,20) tabname,  2  substr(column_name,1,20)column_name,  3  rtrim(data_type)||'('||data_length||')' from dba_tab_columns  4  where owner='&username'  5  /TABNAME                              COLUMN_NAME    RTRIM(DATA_TYPE)||'('||DATA_LE--------------------------- ---------------------   -------BONUS                                    ENAME                                    VARCHAR2(10)BONUS                                    JOB                                      VARCHAR2(9)BONUS                                    SAL         NUMBER(22)BONUS                                    COMM        NUMBER(22)DEPT                                     DEPTNO      NUMBER(22)DEPT                                     DNAME                                    VARCHAR2(14)DEPT                                     LOC                                      VARCHAR2(13)DUMMY                                    DUMMY       NUMBER(22)EMP                                      EMPNO       NUMBER(22)EMP                                      ENAME                                    VARCHAR2(10)EMP                                      JOB                                      VARCHAR2(9)EMP                                      MGR         NUMBER(22)EMP                                      HIREDATE    DATE(7)EMP                                      SAL         NUMBER(22)EMP                                      COMM        NUMBER(22)EMP                                      DEPTNO      NUMBER(22)SALGRADE                                 GRADE       NUMBER(22)SALGRADE                                 LOSAL       NUMBER(22)SALGRADE                                 HISAL       NUMBER(22)19 rows selectedSQL>

從Oracle 9i開始，我們可以使用dbms_metadata.get_ddl來找到對象的定義。

例如：

SQL> @C:/TEMP/get_obj_sql.sqlSQL> set heading off echo off pages 10000 long 90000輸入 object_type 的值:  TABLE輸入 object_name 的值:  EMP輸入 object_owner 的值:  LUNAR原值    1: select dbms_metadata.get_ddl(upper('&OBJECT_TYPE'),upper('&OBJECT_NAME'),upper('&OBJECT_OWNER')) from dual新值    1: select dbms_metadata.get_ddl(upper('TABLE'),upper('EMP'),upper('LUNAR')) from dual  CREATE TABLE "LUNAR"."EMP"   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,        "ENAME" VARCHAR2(10),        "JOB" VARCHAR2(9),        "MGR" NUMBER(4,0),        "HIREDATE" DATE,        "SAL" NUMBER(7,2),        "COMM" NUMBER(7,2),        "DEPTNO" NUMBER(2,0)   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "SYSTEM" SQL>

大家需要注意，這個查詢是需要臨時表空間的，所以如果臨時表空間不夠查詢就會有問題了：

SQL> @C:/TEMP/get_obj_sql.sqlSQL> set heading off echo off pages 10000 long 90000輸入 object_type 的值:  TABLE輸入 object_name 的值:  DEPT輸入 object_owner 的值:  LUNAR原值    1: select dbms_metadata.get_ddl(upper('&OBJECT_TYPE'),upper('&OBJECT_NAME'),upper('&OBJECT_OWNER')) from dual新值    1: select dbms_metadata.get_ddl(upper('TABLE'),upper('DEPT'),upper('LUNAR')) from dualERROR:ORA-25153: 臨時表空間為空ORA-06512: 在"SYS.DBMS_LOB", line 424ORA-06512: 在"SYS.DBMS_METADATA", line 557ORA-06512: 在"SYS.DBMS_METADATA", line 1221ORA-06512: 在line 1 未選定行SQL>SQL> SELECT NAME FROM V$TEMPFILE;未選定行SQL> SELECT NAME FROM V$TABLESPACE;NAME------------------------------SYSTEMUNDOTBS1TEMPINDXUSERSSQL> ALTER TEMPORARY TABLESPACE TEMP ADD TEMPFILE 'D:/oracle92/oradata/test1/TEMP01.DBF' SIZE 10M;ALTER TEMPORARY TABLESPACE TEMP ADD TEMPFILE 'D:/oracle92/oradata/test1/TEMP01.DBF' SIZE 10M      *ERROR 位于第 1 行:ORA-00940: 無效的 ALTER 命令SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:/oracle92/oradata/test1/TEMP01.DBF' SIZE 10M;表空間已更改。SQL> @C:/TEMP/get_obj_sql.sqlSQL> set heading off echo off pages 10000 long 90000輸入 object_type 的值:  TABLE輸入 object_name 的值:  EMP輸入 object_owner 的值:  LUNAR原值    1: select dbms_metadata.get_ddl(upper('&OBJECT_TYPE'),upper('&OBJECT_NAME'),upper('&OBJECT_OWNER')) from dual新值    1: select dbms_metadata.get_ddl(upper('TABLE'),upper('EMP'),upper('LUNAR')) from dual  CREATE TABLE "LUNAR"."EMP"   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,        "ENAME" VARCHAR2(10),        "JOB" VARCHAR2(9),        "MGR" NUMBER(4,0),        "HIREDATE" DATE,        "SAL" NUMBER(7,2),        "COMM" NUMBER(7,2),        "DEPTNO" NUMBER(2,0)   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "SYSTEM" SQL>SQL>

dbms_metadata.get_ddl也可以用來查詢其他對象的創建語句，使用的方法如下；

select dbms_metadata.get_ddl('對象類型','對象名','用戶名') from dual;

例如:

oracle@cs_db02:/arch1/lunar/tools > get_obj_sql.sh procedure aa miscConnected.CREATE OR REPLACE PROCEDURE "MISC"."AA" isbegindelete from error_tip;end aa;

上一篇：教你輕松掌握一個縱表轉橫表的"SQL"

下一篇：巧用數據庫引擎優化Tuning Advistor和dta