实例讲解如何查找某个对象的定义情况

来源：pcdog 作者：佚名出处：巧巧读书 2008-03-13 进入讨论组

关键词：
server
oracle
word

　
　　有人要问，那么这些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>

浏览URL http://www.qqread.com/oracle/2008/03/u401314.html 进入讨论组讨论。