ORACLE关于数据字典的困惑~

最近在论坛上一直看到有朋友对数据字典里的内容搞不太清楚，比如说V$、V_$、GV$等等，到底哪个是同义词，哪个是视图，哪个基于哪个创建。今天正好看到盖国强的《深入浅出ORACLE》第三章讲到这方面内容，总结一下，也方便大家学习。

数据字典由四部分组成：

1、内部RDBMS(X$)表

X$是Oracle数据库的核心部分，这些表用于跟踪内部数据库信息，维护数据库的正常运行。X$表是加密的，Oracle通过X$表建立起其他大量视图，提供用户查询管理数据库之用。X$表是Oracle数据库的运行基础，在数据库启动时由Oracle应用程序动态创建。Oracle不允许sysdba之外的用户直接访问X$表，显示授权不被允许。

发现、观察、研究X$表的一个好方法是借用Oracle的Autotrace功能，当查询一些常用视图时，可以通过autotrace功能发现这些view的底层表。

SQL> set autotrace trace explain

SQL> select * from v$parameter;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE

1 0 MERGE JOIN

2 1 FIXED TABLE (FULL) OF 'X$KSPPCV'

3 1 FILTER

4 3 SORT (JOIN)

5 4 FIXED TABLE (FULL) OF 'X$KSPPI'

介绍X$KVIT，名称含义为：[K]ernel Layer Performance Layer [V] [I]nformation tables [T]ransitory Instance parameters，这个视图记录的是和实例相关的一些内部参数设置。

SQL> select kvittag,kvitval,kvitdsc from x$kvit;

KVITTAG KVITVAL KVITDSC

---------- ---------- -------------------------------------------------------

kcbnbh 3000 number of buffers

kcbldq 25 large dirty queue if kcbclw reaches this

kcbdsp 25 Max percentage of LRU list dbwriter can scan for dirty

kcbfsp 40 Max percentage of LRU list foreground can scan for free

kcbnbf 750 number buffer objects

kcbwst 0 Flag that indicates recovery or db suspension

kcteln 0 Error Log Number for thread open

kcvgcw 0 SGA: opcode for checkpoint cross-instance call

kcbdsp 25 Max percentage of LRU list dbwriter can scan for dirty

触发DBWR写动作的条件之一：脏数据域值达到，从上面得知为25%。

kcbfsp 40 Max percentage of LRU list foreground can scan for free

触发DBWR写动作的条件之二：No Free Buffer，也就是当进程扫描LRU一定数量的Block之后，如果还找不到足够的Free空间，则触发DBWR执行写出，从上面得知这个比例为40%。

2、数据字典表

数据字典表用以存储表、索引、约束以及其他数据库结构的信息，这些对象通常以“$”结尾（如tab$、obj$、ts$等），在创建数据库的时候通过sql.bsq脚本来创建。

3、动态性能视图

动态性能视图记录了数据库运行时信息和统计数据，大部分动态性能视图被实时更新以反应数据库当前状态。

在数据库启动时，Oracle动态创建X$表，在此基础上创建了GV$和V$视图。

GV$视图的产生是为了满足OPS环境的需要，在OPS环境中，查询GV$视图返回所有实例信息，而每个V$视图是基于GV$视图，增加了INST_ID列的where条件限制，只包含当前连接实例信息。

注意，每个V$视图都包含类似语句：where inst_id=userenv(‘Instance’)

Oracle提供了一些特殊视图用以记录其他视图的创建方式，V$FIXED_VIEW_DEFINITION就是其中之一。

SQL> select view_definition from v$fixed_view_definition

2 where view_name='V$FIXED_TABLE';

VIEW_DEFINITION

--------------------------------------------------------------------------------

select NAME , OBJECT_ID , TYPE , TABLE_NUM from GV$FIXED_TABLE where inst_id =

USERENV('Instance')

SQL> select view_definition from v$fixed_view_definition

2 where view_name='GV$FIXED_TABLE';

VIEW_DEFINITION

--------------------------------------------------------------------------------

select inst_id,kqftanam, kqftaobj, 'TABLE', indx from x$kqfta union all select i

nst_id,kqfvinam, kqfviobj, 'VIEW', 65537 from x$kqfvi union all select inst_id,k

qfdtnam, kqfdtobj, 'TABLE', 65537 from x$kqfdt

可以看出，GV$视图基于X$表创建，然后V$视图基于GV$视图创建。

在GV$和V$之后，Oracle建立了GV$和V$视图，随后为这些视图建立了公用同义词。这些工作都是通过catalog.sql脚本实现的。

Create or replace view v_$fixed_table as select * from v$fixed_table;

Create or replace public synonym v$fixed_table for v_$fixed_table;

Create or replace view gv_$fixed_table as select * from gv$fixed_table;

Create or replace public synonym gv$fixed_table for gv_$fixed_table;

通过V_$视图，Oracle把V$视图和普通用户隔离，V_$视图的权限可以授予其他用户，而Oracle不允许任何对于V$视图的直接授权。

SQL> show user

USER为"SYS"

SQL> grant select on v$sga to scott;

grant select on v$sga to scott

*

ERROR位于第1行:

ORA-02030:只能从固定的表/视图查询

SQL> grant select on v_$sga to scott;

授权成功。

实际上通常大部分用户访问的v$对象，并不是视图，而是指向v_$视图的同义词，而v_$视图是基于真正的v$视图（这个视图是基于x$表建立的）创建的。

在进行数据访问时，Oracle访问view优先，然后是同义词。

SQL> conn scott/tiger

已连接。

SQL> create view v_$gvora as select username from all_users;

视图已建立。

SQL> create public synonym v$gvora for v_$gvora;

同义词已创建。

SQL> conn / as sysdba

已连接。

SQL> create view v$gvora as select username,user_id from user_users;

视图已建立。

SQL> desc v$gvora --这个时候显示的是视图的内容

名称是否为空?类型

----------------------------------------- -------- ----------------------------

USERNAME NOT NULL VARCHAR2(30)

USER_ID NOT NULL NUMBER

SQL> drop view v$gvora;

视图已丢掉。

SQL> desc v$gvora --视图删除后才显示同义词的内容

名称是否为空?类型

----------------------------------------- -------- ----------------------------

USERNAME NOT NULL VARCHAR2(30)

4、数据字典视图

数据字典视图是在X$表和数据字典表之上创建的视图，在创建数据库时由catalog.sql脚本创建。

按照前缀的不同，数据字典视图通常被分成3类：

USER_类视图：包含了用户所拥有的相关对象信息，通过where o.owner#=userenv(‘SCHEMAID’)进行限制；

ALL_类视图：包含了用户有权限访问的所有对象的信息，这个条件扩展了用户有权限访问的对象信息；

DBA_类视图：包含了数据库所有相关对象的信息，没有关于owner的限制，查询返回数据库中所有表的信息。

数据字典视图的定义通过DBMS_METADATE.GET_DDL来得到：

SQL> select dbms_metadata.get_ddl('VIEW','USER_TABLES','SYS') from dual;

CREATE OR REPLACE FORCE VIEW "SYS"."USER_TABLES" ("TABLE_NAME", "TABLESPACE
_NAME", "CLUSTER_NAME", "IOT_NAME", "STATUS", "PCT_F
REE", "PCT_USED", "INI_TRANS", "MAX_TRANS", "INITIAL
_EXTENT", "NEXT_EXTENT", "MIN_EXTENTS", "MAX_EXTENTS
", "PCT_INCREASE", "FREELISTS", "FREELIST_GROUPS", "
LOGGING", "BACKED_UP", "NUM_ROWS", "BLOCKS", "EMPTY_
BLOCKS", "AVG_SPACE", "CHAIN_CNT", "AVG_ROW_LEN", "A
VG_SPACE_FREELIST_BLOCKS", "NUM_FREELIST_BLOCKS", "D
EGREE", "INSTANCES", "CACHE", "TABLE_LOCK", "SAMPLE_
SIZE", "LAST_ANALYZED", "PARTITIONED", "IOT_TYPE", "
TEMPORARY", "SECONDARY", "NESTED", "BUFFER_POOL", "R
OW_MOVEMENT", "GLOBAL_STATS", "USER_STATS", "DURATIO

N", "SKIP_CORRUPT", "MONITORING", "CLUSTER_OWNER", "
DEPENDENCIES", "COMPRESSION", "DROPPED") AS
select o.name, decode(bitand(t.property, 2151678048), 0,
ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags,
536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824,
'UNUSABLE', 'VALID'),
decode(bitand(t.property, 32+64), 0, mod(t.pctfre
e$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL

),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans,
null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.
blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),

decode(bitand(o.flags, 2), 2, 1, decode(s.lists,
0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.grou
ps, 0, 1, s.groups))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, 'YES', 'NO'), nu
ll),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, nu
ll),
decode(bitand(t.property, 64), 0, t.empcnt, null),

decode(bitand(t.property, 64), 0, t.avgspc, null),
t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10
),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),
5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),

decode(bitand(t.property, 64), 64, 'IOT',

decode(bitand(t.property, 512), 512, 'I
OT_OVERFLOW',
decode(bitand(t.flags, 536870912), 536870912, 'IO
T_MAPPING', null))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', '
N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECY
CLE', NULL)),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISAB

LED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 83886
08,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 2147483648, 'NO'
,
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
decode(bitand(t.property, 1024), 0, null, cu.name),

decode(bitand(t.flags, 8388608), 8388608, 'ENABLED',
'DISABLED'),
decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DIS
ABLED')),
decode(bitand(o.flags, 128), 128, 'YES', 'NO')
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$k
sppi ksppi
where o.owner# = userenv('SCHEMAID') --限制了当前查询只返回当前用户的SCHEMA对象信息
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0

and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = '_dml_monitoring_enabled'

SQL> select dbms_metadata.get_ddl('VIEW','ALL_TABLES','SYS') from dual;

CREATE OR REPLACE FORCE VIEW "SYS"."ALL_TABLES" ("OWNER", "TABLE_NAME", "TA
BLESPACE_NAME", "CLUSTER_NAME", "IOT_NAME", "STATUS"
, "PCT_FREE", "PCT_USED", "INI_TRANS", "MAX_TRANS",
"INITIAL_EXTENT", "NEXT_EXTENT", "MIN_EXTENTS", "MAX
_EXTENTS", "PCT_INCREASE", "FREELISTS", "FREELIST_GR
OUPS", "LOGGING", "BACKED_UP", "NUM_ROWS", "BLOCKS",
"EMPTY_BLOCKS", "AVG_SPACE", "CHAIN_CNT", "AVG_ROW_
LEN", "AVG_SPACE_FREELIST_BLOCKS", "NUM_FREELIST_BLO
CKS", "DEGREE", "INSTANCES", "CACHE", "TABLE_LOCK",
"SAMPLE_SIZE", "LAST_ANALYZED", "PARTITIONED", "IOT_
TYPE", "TEMPORARY", "SECONDARY", "NESTED", "BUFFER_P
OOL", "ROW_MOVEMENT", "GLOBAL_STATS", "USER_STATS",

"DURATION", "SKIP_CORRUPT", "MONITORING", "CLUSTER_O
WNER", "DEPENDENCIES", "COMPRESSION", "DROPPED") AS

select u.name, o.name,decode(bitand(t.property, 2151678048), 0, ts.name, nu
ll),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912))
,
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE',
'VALID'),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 6
4, 0, null),

decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 6
4, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, nul
l),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),

s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),

decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lis
ts))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s
.groups))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
decode(bitand(t.property, 64), 0, t.avgspc, n

ull),
t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),

lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.inst
ances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED')
,
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',

decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW
',
decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING',
null))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES
')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),

decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISA
BLED'),
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 214
7483648, 'NO',
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
decode(bitand(t.property, 1024), 0, null, cu.n
ame),

decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),

decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, 'E
NABLED', 'DISABLED')),
decode(bitand(o.flags, 128), 128, 'YES', 'NO')
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co
, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$k
sppi ksppi
where o.owner# = u.user#
and o.obj# = t.obj#
and bitand(t.property, 1) = 0

and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and (o.owner# = userenv('SCHEMAID')
or o.obj# in
(select oa.obj#
from sys.objauth$ oa
where grantee# in ( select kzsrorol
from x$kzsro
)

)
or /* user has system privileges */
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY
TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE
ANY TABLE */,
-50 /* DELETE ANY TABLE *
/)
)
)

--这个条件扩展了关于用户有权限访问的对象信息

and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = '_dml_monitoring_enabled'

SQL> select dbms_metadata.get_ddl('VIEW','DBA_TABLES','SYS') from dual;

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_TABLES" ("OWNER", "TABLE_NAME", "TA
BLESPACE_NAME", "CLUSTER_NAME", "IOT_NAME", "STATUS"
, "PCT_FREE", "PCT_USED", "INI_TRANS", "MAX_TRANS",
"INITIAL_EXTENT", "NEXT_EXTENT", "MIN_EXTENTS", "MAX
_EXTENTS", "PCT_INCREASE", "FREELISTS", "FREELIST_GR
OUPS", "LOGGING", "BACKED_UP", "NUM_ROWS", "BLOCKS",
"EMPTY_BLOCKS", "AVG_SPACE", "CHAIN_CNT", "AVG_ROW_
LEN", "AVG_SPACE_FREELIST_BLOCKS", "NUM_FREELIST_BLO
CKS", "DEGREE", "INSTANCES", "CACHE", "TABLE_LOCK",
"SAMPLE_SIZE", "LAST_ANALYZED", "PARTITIONED", "IOT_
TYPE", "TEMPORARY", "SECONDARY", "NESTED", "BUFFER_P
OOL", "ROW_MOVEMENT", "GLOBAL_STATS", "USER_STATS",

"DURATION", "SKIP_CORRUPT", "MONITORING", "CLUSTER_O
WNER", "DEPENDENCIES", "COMPRESSION", "DROPPED") AS

select u.name, o.name, decode(bitand(t.property,2151678048), 0, ts.name, nu
ll),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912))
,
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE',
'VALID'),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 6
4, 0, null),

decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 6
4, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, nul
l),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),

s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),

decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lis
ts))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s
.groups))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
t.avgspc, t.chncnt, t.avgrln, t.avgspc_flb,

decode(bitand(t.property, 64), 0, t.flbcnt, nu
ll),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.
instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABL
ED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVER
FLOW',

decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPI
NG', null))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO',
'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', N
ULL)),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),

decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', '
DISABLED'),
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 2147483648, 'NO',
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))
),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLE
D'),

decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')),

decode(bitand(o.flags, 128), 128, 'YES', 'NO')
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$
o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = u.user#
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#

and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = '_dml_monitoring_enabled'

--DBA_TABLES就没有上述那些限制条件了

X$表、GV$视图和V$视图的信息都可以从V$FIXED_TABLE中得到

SQL> conn / as sysdba

已连接。

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9iEnterpriseEdition Release9.2.0.1.0 - Production

PL/SQL Release9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for 32-bit Windows: Version9.2.0.1.0 - Production

NLSRTL Version9.2.0.1.0 – Production

SQL> select count(*) from v$fixed_table where name like 'X$%';

COUNT(*)

----------

394

SQL> select count(*) from v$fixed_table where name like 'GV$%';

COUNT(*)

----------

259

SQL> select count(*) from v$fixed_table where name like 'V$%';

COUNT(*)

----------

259

394+259+259=912

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

COUNT(*)

----------

912

在非SYS用户下查询，当访问v$parameter对象时，访问的是视图还是同义词？

同义词，因为除了SYS用户以外，其他用户不能查询v$视图，v$视图也不能被授权给其他用户。

总结SQL语句中Oracle对于对象名的解析顺序：

Oracle首先查看在发出命令的用户模式中是否存在表或视图；

如果表或视图不存在，Oracle检查私有同义词是否存在；

如果私有同义词存在，将使用这个同义词所引用的对象；

如果私有同义词不存在，检查同名的公共同义词是否存在；

如果公共同义词存在，将使用这个同义词所引用的对象；

如果公共同义词不存在，Oracle返回消息“ORA_00942 table or view does not exist”。