經常遇到需要查找某個對象的定義的情況，下面針對不同類型的對象分別討論：
一、v$視圖和x$視圖
普通用戶不能訪問v$視圖：
sql> conn lunar/[email protected]
已連接。
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/[email protected]
已連接。
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/[email protected]
已連接。
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;
例如：
[email protected]_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;
[email protected]_db02:/arch1/lunar/tools >
