如何查找某个对象的定义（V$_X$_DBA）

经常遇到需要查找某个对象的定义的情况，下面针对不同类型的对象分别讨论：

一、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          NO
LUNAR                          RESOURCE                       NO           YES          NO
PUBLIC                         PLUSTRACE                      NO           YES          NO

SQL> select count(*) from v$fixed_table;

select count(*) from v$fixed_table

ORA-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(*)
----------
       912

SQL> select count(*) from v_$fixed_table;

select count(*) from v_$fixed_table

ORA-00942: 表或视图不存在

SQL> select count(*) from sys.v_$fixed_table;

  COUNT(*)
----------
       912

SQL>


也可以授予用户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          NO
LUNAR                          RESOURCE                       NO           YES          NO
PUBLIC                         PLUSTRACE                      NO           YES          NO

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
LUNAR                          SELECT ANY TABLE                         NO

SQL> select count(*) from v$fixed_table;

  COUNT(*)
----------
       912

SQL> select * from v$fixed_table where rownum<2;

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
X$KQFTA                        4294950912 TABLE          0

SQL> select * from v_$fixed_table where rownum<2;

select * from v_$fixed_table where rownum<2

ORA-00942: 表或视图不存在

SQL> select * from sys.v_$fixed_table where rownum<2;

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
X$KQFTA                        4294950912 TABLE          0

SQL>

通过查询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 10000
SQL> 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$kqfdt

SQL>

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_column
Name            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                                         0
X$KQFTA                                   2 INDX                                         0

SQL>

 

一般来说，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, @#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 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_views
Name             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 10000
SQL> 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_username
       from sys.user$ u, sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
            sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
       where u.datats# = dts.ts#
       and u.resource$ = p.profile#
       and u.tempts# = tts.ts#
       and u.astatus = m.status#
       and u.type# = 1
       and u.resource$ = pr.profile#
       and dp.profile# = 0
       and dp.type#=1
       and dp.resource#=1
       and pr.type# = 1
       and pr.resource# = 1


SQL>


三、如何查找用户自定义的某个表的定义？
在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 selected

SQL>

从Oracle 9i开始，可以使用dbms_metadata.get_ddl来找到对象的定义，例如：

SQL> @C:\TEMP\get_obj_sql.sql
SQL> 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_O
WNER@#)) 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.sql
SQL> 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_O
WNER@#)) from dual
新值    1: select dbms_metadata.get_ddl(upper(@#TABLE@#),upper(@#DEPT@#),upper(@#LUNAR@#)) from dual
ERROR:
ORA-25153: 临时表空间为空
ORA-06512: 在"SYS.DBMS_LOB", line 424
ORA-06512: 在"SYS.DBMS_METADATA", line 557
ORA-06512: 在"SYS.DBMS_METADATA", line 1221
ORA-06512: 在line 1

 

未选定行

SQL>
SQL> SELECT NAME FROM V$TEMPFILE;

未选定行

SQL> SELECT NAME FROM V$TABLESPACE;

NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
INDX
USERS

SQL> 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.sql
SQL> 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_O
WNER@#)) 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 misc
Connected.


  CREATE OR REPLACE PROCEDURE "MISC"."AA" is
begin
delete from error_tip;
end aa;


oracle@cs_db02:/arch1/lunar/tools >

 

 

 

原文转自：http://www.ltesting.net