官方文档：Book => REF => Reference => Part II Static Data Dictionary Views
数据字典是Oracle数据库的一个重要组成部分，是元数据的存储地点。我们可以通过数据字典来获取数据库相关信息。
数据字典由四部分组成：内部RDBMS(X$)表丶数据字典表丶静态数据字典视图丶动态性能视图。
Oracle的内核层引用了DSI，如下 图所示：
一丶内部RDBMS(X$)表:是Oracle的核心部分，这些表用于跟踪内部数据库信息，维持数据库的正常运行。
x$bh --buffer header
x$kglob --X$KGLOB - [K]ernel Layer [G]eneric Layer [L]ibrary Cache Manager All [OB]jects
x$ksmsp -- X$KSMSP - [K]ernel Layer [S]ervice Layer [M]emory Management [S]GA Hea[P]
x$kvit --X$KVIT -[K]ernel Layer Performance Layer[V][I]nformation tables [T]ransitory Instance parameters
sys@OCM> col KVITDSC for a60
sys@OCM> col KVITTAG for a30
sys@OCM> select kvittag,kvitval,kvitdsc from x$kvit;
KVITTAG KVITVAL KVITDSC
------------------------------ ---------- ------------------------------------------------------------
ksbcpu 2 number of logical CPUs in the system used by Oracle
ksbcpucore 0 number of physical CPU cores in the system used by Oracle
ksbcpusocket 0 number of physical CPU sockets in the system used by Oracle
ksbcpu_hwm 2 high water mark of number of CPUs used by Oracle
ksbcpucore_hwm 0 high water mark of number of CPU cores on system
ksbcpusocket_hwm 0 high water mark of number of CPU sockets on system
ksbcpu_actual 2 number of available CPUs in the system
ksbcpu_dr 1 CPU dynamic reconfiguration supported
kcbnbh 62996 number of buffers
kcbldq 25 large dirty queue if kcbclw reaches this
kcbfsp 40 Max percentage of LRU list foreground can scan for free
kcbcln 2 Initial percentage of LRU list to keep clean
kcbnbf 1000 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
kcvgcw 0 SGA:opcode for pq checkpoint cross-instance call
17 rows selected.
二丶数据字典表：用以存储表丶索引丶约束以及其它数据库结构的信息。
more /u01/app/oracle/product/11.2.0/rdbms/admin/sql.bsq
dcore.bsq
dsqlddl.bsq
dmanage.bsq
dplsql.bsq
dtxnspc.bsq
dfmap.bsq
denv.bsq
........
more dcore.bsq
tab$ --表
obj$ --对象
col$ --列
con$ --约束
seg$ --段
ts$ --表空间
三丶静态数据字典视图:这些信息通常相对稳定丶不能直接修改。分三类：USER_ 丶ALL_丶DBA_
more /u01/app/oracle/product/11.2.0/rdbms/admin/catalog.sql
--CATCTL -S Initial scripts single process
@@cdstrt
@@cdfixed.sql
@@cdcore.sql
--CATCTL -M
@@cdplsql.sql
@@cdsqlddl.sql
@@cdmanage.sql
............
--CATCTL -S Final scripts single process
@@cdoptim.sql
@@catsum.sql
@@catexp.sql
........
1丶USER_ 当前用户拥有的对象
sys@OCM> conn gyj/gyj
gyj@OCM> select table_name from user_tables;
TABLE_NAME
------------------------------
T512
T3
D1
TEST1
TEST2
2丶ALL_ 当前用户可以访问的对象
sys@OCM> revoke dba from gyj;
sys@OCM> conn hr/hr
grant select on employees to gyj;
sys@OCM> conn gyj/gyj
gyj@OCM> select table_name ,owner from all_tables where owner in(\'GYJ\',\'HR\');
TABLE_NAME OWNER
------------------------------ ------------------------------
T512 GYJ
T3 GYJ
D1 GYJ
TEST1 GYJ
TEST2 GYJ
EMPLOYEES HR
3丶DBA_ 数据库中的所有对象
gyj@OCM> conn / as sysdba
Connected.
sys@OCM> select owner,count(*) from dba_tables group by owner order by 2;
OWNER COUNT(*)
------------------------------ ----------
FLOWS_FILES 1
OWBSYS 1
APPQOSSYS 2
PM 2
OUTLN 3
ORDSYS 5
HR5 7
HR1 7
OE 8
HR 11
SH 16
IX 17
GYJ 24
DBSNMP 25
XDB 33
WMSYS 43
EXFSYS 47
CTXSYS 47
ORDDATA 73
MDSYS 120
OLAPSYS 126
SYSTEM 156
APEX_030200 360
SYSMAN 728
SYS 956
25 rows selected.
4丶常用数据字典视图
（1）DICT/DICTIONARY
sys@OCM> col object_name for a20
sys@OCM> col owner for a10
sys@OCM> select owner,object_name,object_type from dba_objects where object_name in(\'DICT\',\'DICTIONARY\');
OWNER OBJECT_NAME OBJECT_TYPE
---------- -------------------- -------------------
SYS DICTIONARY VIEW
PUBLIC DICT SYNONYM
PUBLIC DICTIONARY SYNONYM
sys@OCM> set linesize 100
sys@OCM> desc DICTIONARY
Name Null? Type
------------------ ------------------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
gyj@OCM> select table_name from DICT where table_name like \'%TABLES%\';
TABLE_NAME
------------------------------
USER_ADVISOR_SQLA_TABLES
USER_ADVISOR_SQLW_TABLES
USER_ALL_TABLES
USER_EVALUATION_CONTEXT_TABLES
USER_EXTERNAL_TABLES
USER_FILE_GROUP_TABLES
USER_FILE_GROUP_TABLESPACES
USER_FLASHBACK_ARCHIVE_TABLES
USER_NESTED_TABLES
USER_OBJECT_TABLES
USER_PART_TABLES
USER_PENDING_CONV_TABLES
USER_QUEUE_TABLES
USER_SOURCE_TABLES
USER_SUBSCRIBED_TABLES
USER_TABLES
USER_TABLESPACES
USER_TSTZ_TABLES
USER_XML_TABLES
ALL_ALL_TABLES
ALL_CAPTURE_PREPARED_TABLES
ALL_CHANGE_TABLES
ALL_EVALUATION_CONTEXT_TABLES
ALL_EXTERNAL_TABLES
ALL_FILE_GROUP_TABLES
ALL_FILE_GROUP_TABLESPACES
ALL_NESTED_TABLES
ALL_OBJECT_TABLES
ALL_PART_TABLES
ALL_PENDING_CONV_TABLES
ALL_QUEUE_TABLES
ALL_SYNC_CAPTURE_TABLES
ALL_TABLES
ALL_TSTZ_TABLES
ALL_XML_TABLES
GV$ENCRYPTED_TABLESPACES
V$ENCRYPTED_TABLESPACES
ALL_SOURCE_TABLES
ALL_SUBSCRIBED_TABLES
39 rows selected.
（2）DICT_COLUMNS
gyj@OCM> col comments for a50
gyj@OCM> select column_name,comments from dict_columns where table_name=\'DICT\';
COLUMN_NAME COMMENTS
------------------------------ --------------------------------------------------
TABLE_NAME Name of the object
COMMENTS Text comment on the object
sys@OCM> select table_name from dict where table_name like \'DBA%COLUMNS\';
TABLE_NAME
------------------------------
DBA_APPLY_CONFLICT_COLUMNS
DBA_APPLY_KEY_COLUMNS
DBA_APPLY_TABLE_COLUMNS
DBA_AUDIT_POLICY_COLUMNS
DBA_CLU_COLUMNS
DBA_COMPARISON_COLUMNS
DBA_CONS_COLUMNS
DBA_CONS_OBJ_COLUMNS
DBA_CUBE_DIM_VIEW_COLUMNS
DBA_CUBE_HIER_VIEW_COLUMNS
DBA_CUBE_VIEW_COLUMNS
DBA_ENCRYPTED_COLUMNS
DBA_IND_COLUMNS
DBA_JOIN_IND_COLUMNS
DBA_LOG_GROUP_COLUMNS
DBA_OLDIMAGE_COLUMNS
DBA_PART_KEY_COLUMNS
DBA_PUBLISHED_COLUMNS
DBA_REPFLAVOR_COLUMNS
DBA_REPKEY_COLUMNS
DBA_STREAMS_COLUMNS
DBA_STREAMS_KEEP_COLUMNS
DBA_SUBPART_KEY_COLUMNS
DBA_SUBSCRIBED_COLUMNS
DBA_TAB_COLUMNS
DBA_UPDATABLE_COLUMNS
26 rows selected.
（3）OBJ$/DBA_OBJECTS/OBJ
gyj@OCM> conn gyj/gyj
gyj@OCM> select object_name,object_type from obj;
OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
RECORD_NEW_ORDER PROCEDURE
ORDER_INFO_PK INDEX
KZCZ214001 PROCEDURE
SMALL_CUSTOMERS TABLE
MEDIUM_CUSTOMERS TABLE
LARGE_CUSTOMERS TABLE
ORDERS TABLE
EMP TABLE
SYS_C0012179 INDEX
EMP2 TABLE
SYS_C0012180 INDEX
DEPARTMENT TABLE
DEPT60_BONUSES TABLE
ORDER_LOG TABLE
ORDER_LOG_PK INDEX
ORDER_INFO TABLE
D1 TABLE
TEST1 TABLE
SYS_C0012034 INDEX
TEST2 TABLE
SYS_C0012037 INDEX
TEST3 TABLE
SYS_TEMP_FBT TABLE
T128 TABLE
T512 TABLE
T3 TABLE
DEPT TABLE
T2_ID_AGE_U INDEX
T2 TABLE
EMP1 TABLE
DEPT80 TABLE
V_T2 VIEW
T5 TABLE
V_T3_RN VIEW
IDX_T5_OBJECT_NAME INDEX
T1 SYNONYM
T10 TABLE
37 rows selected.
（4）*_source视图
sys@OCM> COL TEXT FOR A80
sys@OCM> select TEXT from DBA_SOURCE where name like \'%KZCZ%\';
TEXT
-----------------------------------------------------------------------------
procedure kzcz214001(customer_id in number,
order_id in number,
opmoney in number) as
begin
insert into order_info values(customer_id,order_id,opmoney,sysdate);
record_new_order(customer_id, order_id);
rollback;
end;
8 rows selected.
注意条件中表名是大写。ORACLE是不区分大小写的，在创建各种对象如表丶视图等等，ORACLE会把对象名统统转为大写，存在数据字典表中。所有我们要注意，以对象名为条件时，名字都要是大写。
最后我们要说的DBA_系列视图，DBA_系列视图中显示的是数据库所有对象相关的元数据。普通用户一般没访问DBA_系列视图的权限。
以USER_TABLES丶ALL_TABLES和DBA_TABLES为例，它们三个的信息都来自于TAB$。DBA_TABLES从TAB$中选择最多的行与列。ALL_TABLES其次，而USER_TABLES中的信息最少。
四.动态性能视图:记录了数据库运行时信息和统计数据，大部分运态性能视图被实时更新以反映数据库的当前状态。
数据字典视图显示的都是取自数据字典表的元数据。这些数据相对来说比较固定，不会随时变化。一般只有DDL命令，数据定义语言，它专门修改元数据。只有在执行DDL时，才会影响到各种数据字典。数据字典视图是相对来说比较“静态”的视图。
ORACLE中还有一系列视图，它里面的信息随时变化，以返应数据库当前的运行状态，比如什么用户正在加什么样的锁，什么用户正在等待什么资源，等等。这些信息往往随时变化，存储这些信息的视图被称为动态性能视图。动态性能视图的名字规则是以V$开头。动态性能视图的基表是以X$开头的特殊表。通常我们不必要访问X$表。动态性能视图是我们进行数据库调优很重要的工具，通过它们，使用我们可以了解数据库的运行状态，DBA可以根据动态性能视图中的信息，判断数据库的运行是否正常，如果不正常，问题出在什么地方。
sys@OCM> select * from V$FIXED_TABLE where name like \'%BH%\';
NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
X$KCBBHS 4294951429 TABLE 260
X$BH 4294950954 TABLE 266
X$KCBOBH 4294952086 TABLE 280
X$KRCFBH 4294952015 TABLE 404
X$KTFBHC 4294951516 TABLE 463
X$KFCBH 4294952608 TABLE 676
X$KFBH 4294952611 TABLE 678
GV$BH 4294951405 VIEW 65537
V$BH 4294951406 VIEW 65537
sys@OCM> select * from V$FIXED_VIEW_DEFINITION where view_name like \'%BH%\';
VIEW_NAME
------------------------------
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------
GV$BH
select bh.inst_id, file#, dbablk, class, decode(state,0,\'free\',1,\'xcur\',2,\'scur\',3,\'cr\', 4,\'read\',5,\'mrec\',6,\'irec\',7,\'write\',8,\'pi\', 9,\'memory\',10,\'mwrite\',11,\'donated\', 12,\'protected\', 13,\'securefile\', 14,\'siop\',15,\'recckpt\', 16, \'flashfree\', 17, \'flashcur\',
18, \'flashna\'), 0, 0, 0, bh.le_addr, le_id1, le_id2, decode(bitand(flag,1), 0, \'N\', \'Y\'), decode(bitand(flag,16), 0, \'N\', \'Y\'), decode(bitand(flag,1536), 0, \'N\', \'Y\'), decode(bitand(flag,16384), 0, \'N\', \'Y\'), decode(bitand(flag,65536), 0, \'N\', \'Y\'), \'N\', obj,
ts#, lobid, bitand(OBJ_FLAG, 240)/16 from x$bh bh, x$le le where bh.le_addr = le.le_addr (+)
V$BH
select file#, block#, class#, status, xnc, forced_reads, forced_writes, lock_element_addr, lock_element_name, lock_element_class, dirty, temp, ping, stale, direct, new, objd, ts#, lobid, cachehint from gv$bh where inst_id = USERENV(\'Instance\')
sys@OCM> conn gyj/gyj
Connected.
gyj@OCM> desc v$session
ERROR:
ORA-04043: object "SYS"."V_$SESSION" does not exist
gv_$丶v_$和gv$丶v$
**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM:
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!
答案在：http://blog.csdn.net/guoyjoe/article/details/8624392
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036
