一、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 YESNOLUNAR RESOURCE NO YESNOPUBLIC PLUSTRACE NO YESNOSQL> 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 YESNOLUNAR RESOURCE NO YESNOPUBLIC PLUSTRACE NO YESNOSQL> 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_TABLEselect 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_NAMEINDEX_NUMBER COLUMN_NAME COLUMN_POSITION------------------ ------------ -------------- ---------------X$KQFTA 1 ADDR0X$KQFTA 2 INDX0SQL>
一般情况下，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_PROCESSselect 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 TypeNullable Default Comments ---------------- ------------ -------- ------- -------OWNER VARCHAR2(30) Owner of the view VIEW_NAMEVARCHAR2(30) Name of the view TEXT_LENGTH NUMBER YLength of the view text TEXT LONG YView text TYPE_TEXT_LENGTH NUMBER YLength of the type clause of the object view TYPE_TEXTVARCHAR2(4000) YType clause of the object viewOID_TEXT_LENGTH NUMBER YLength of the WITH OBJECT OID clause of the object view OID_TEXT VARCHAR2(4000) YWITH OBJECT OID clause of the object view VIEW_TYPE_OWNER VARCHAR2(30) YOwner of the type of the view if the view is an object view VIEW_TYPEVARCHAR2(30) YType of the view if the view is an object view SUPERVIEW_NAME VARCHAR2(30) YName 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 /TABNAMECOLUMN_NAME RTRIM(DATA_TYPE)||'('||DATA_LE--------------------------- --------------------- -------BONUS ENAME VARCHAR2(10)BONUS JOBVARCHAR2(9)BONUS SAL NUMBER(22)BONUS COMMNUMBER(22)DEPT DEPTNO NUMBER(22)DEPT DNAME VARCHAR2(14)DEPT LOCVARCHAR2(13)DUMMY DUMMY NUMBER(22)EMPEMPNO NUMBER(22)EMPENAME VARCHAR2(10)EMPJOBVARCHAR2(9)EMPMGR NUMBER(22)EMPHIREDATE DATE(7)EMPSAL NUMBER(22)EMPCOMMNUMBER(22)EMPDEPTNO 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:TEMPget_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:TEMPget_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:oracle92oradatatest1TEMP01.DBF' SIZE 10M;ALTER TEMPORARY TABLESPACE TEMP ADD TEMPFILE 'D:oracle92oradatatest1TEMP01.DBF' SIZE 10M *ERROR 位于第 1 行:ORA-00940: 无效的 ALTER 命令SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:oracle92oradatatest1TEMP01.DBF' SIZE 10M;表空间已更改。SQL> @C:TEMPget_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;
