[ORACLE]Oracle数据字典

X$

X$表是Oracle数据库的核心部分，用于跟踪数据库内部信息，维护数据库的正常运行。

CREATE OR REPLACE VIEW BH AS SELECT * FROM SYS.X$BH;

CREATE OR REPLACE PUBLIC SYNONYM X$BH FOR BH;

数据字典表

数据字典表用以存储表、索引、约束以及其他数据库结构的信息。这些对象通常是以“$”结尾（例如：TAB$、OBJ$、TS$等）。Bsq是非常重要的一个文件，其中包含了数据字典的定义以及注释说明，深入学习Oracle数据库的用户都应该仔细阅读该文件，该文件位于$ORACLE_HOME/rdbms/admin目录下（在Oracle 11g中bsq文件被分别归类到不通的.bsq文件）。

静态数据字典视图

静态数据字典视图是由catalog.sql脚本创建（在$ORACLE_HOME/rdbms/admin下）。静态数据字典中的视图可以分为三类，它们分别由三个前缀构成：USER_*、ALL_*和DBA_*。

动态性能视图

动态性能视图基于从数据库内存结构构建的虚拟表。因此，它们不是存储在数据库中的常规表。由于数据动态更新，所以，动态性能视图的读一致性不能保证。

SYS是这些动态性能表的所有者，这些表的名字都以V_$开头，基于这些表的视图被创建后，Oracle还会为视图创建公共同义词。同义词名称以V$开头，例如，视图V$DATAFILE包含数据库数据文件的信息，而V$FIXED_TABLE包含数据库中所有动态性能表和视图的信息。NOMOUNT状态不能查询所有的V$视图。因为动态性能视图不是真实表，所以数据依赖于数据库和实例的状态。例如，当数据库处于NOMOUNT时，可以查询V$INSTANCE和V$BGPROCEP。但是，在MOUNT数据库之前，不能查询V$DATAFILE。

只有赋予了SELECT ANY DICTIONARY权限的用户才可以查询所有的V$视图。

v$动态视图主要是针对SGA和数据库实例进程的显示。在这个过程中，Oracle使用了DMA（Direct Memory Access）技术，可以高效的获取这些分析数据。

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

系统数据字典视图定义有关的几个视图

DICTIONARY视图记录了全部数据字典表的名称和解释，它有一个同义词DICT。

DICT_COLUMNS视图记录了全部数据字典表里字段名称和解释。

如果想知道数据字典中的USER_INDEXES视图中各字段的详细含义，那么可以用如下SQL语句：

SQL> SELECT COLUMN_NAME, COMMENTS  FROM DICT_COLUMNS WHERE TABLE_NAME = ‘USER_INDEXES‘;

COLUMN_NAME               COMMENTS
------------------------------ ----------------------------------------------------------------------------------------------------
INDEX_NAME               Name of the index
INDEX_TYPE
TABLE_OWNER               Owner of the indexed object
TABLE_NAME               Name of the indexed object
TABLE_TYPE               Type of the indexed object
UNIQUENESS               Uniqueness status of the index:    "UNIQUE",  "NONUNIQUE", or "BITMAP"
COMPRESSION               Compression property of the index: "ENABLED",  "DISABLED", or NULL
PREFIX_LENGTH               Number of key columns in the prefix used for compression
TABLESPACE_NAME            Name of the tablespace containing the index
INI_TRANS               Initial number of transactions
MAX_TRANS               Maximum number of transactions
INITIAL_EXTENT               Size of the initial extent in bytes
NEXT_EXTENT               Size of secondary extents in bytes
MIN_EXTENTS               Minimum number of extents allowed in the segment
MAX_EXTENTS               Maximum number of extents allowed in the segment
PCT_INCREASE               Percentage increase in extent size
PCT_THRESHOLD               Threshold percentage of block space allowed per index entry
INCLUDE_COLUMN               User column-id for last column to be included in index-only table top index
FREELISTS               Number of process freelists allocated in this segment
FREELIST_GROUPS            Number of freelist groups allocated to this segment
PCT_FREE               Minimum percentage of free space in a block
LOGGING                Logging attribute
BLEVEL                   B-Tree level
LEAF_BLOCKS               The number of leaf blocks in the index
DISTINCT_KEYS               The number of distinct keys in the index
AVG_LEAF_BLOCKS_PER_KEY        The average number of leaf blocks per key
AVG_DATA_BLOCKS_PER_KEY        The average number of data blocks per key
CLUSTERING_FACTOR           A measurement of the amount of (dis)order of the table this index is for
STATUS                   Whether the non-partitioned index is in USABLE or not
NUM_ROWS               Number of rows in the index
SAMPLE_SIZE               The sample size used in analyzing this index
LAST_ANALYZED               The date of the most recent time this index was analyzed
DEGREE                   The number of threads per instance for scanning the partitioned index
INSTANCES               The number of instances across which the partitioned index is to be scanned
PARTITIONED               Is this index partitioned? YES or NO
TEMPORARY               Can the current session only see data that it place in this object itself?
GENERATED               Was the name of this index system generated?
SECONDARY               Is the index object created as part of icreate for domain indexes?
BUFFER_POOL               The default buffer pool to be used for index blocks
FLASH_CACHE               The default flash cache hint to be used for index blocks
CELL_FLASH_CACHE           The default cell flash cache hint to be used for index blocks
USER_STATS               Were the statistics entered directly by the user?
DURATION               If index on temporary table, then duration is sys$session or sys$transaction else NULL
PCT_DIRECT_ACCESS           If index on IOT, then this is percentage of rows with Valid guess
ITYP_OWNER               If domain index, then this is the indextype owner
ITYP_NAME               If domain index, then this is the name of the associated indextype
PARAMETERS               If domain index, then this is the parameter string
GLOBAL_STATS               Are the statistics calculated without merging underlying partitions?
DOMIDX_STATUS               Is the indextype of the domain index valid
DOMIDX_OPSTATUS            Status of the operation on the domain index
FUNCIDX_STATUS               Is the Function-based Index DISABLED or ENABLED?
JOIN_INDEX               Is this index a join index?
IOT_REDUNDANT_PKEY_ELIM        Were redundant primary key columns eliminated from iot secondary index?
DROPPED                Whether index is dropped and is in Recycle Bin
VISIBILITY               Whether the index is VISIBLE or INVISIBLE to the optimizer
DOMIDX_MANAGEMENT           If this a domain index, then whether it is system managed or user managed
SEGMENT_CREATED            Whether the index segment has been created
ORPHANED_ENTRIES
INDEXING
AUTO                   Whether the index is automatically created
CONSTRAINT_INDEX

61 rows selected.

V$FIXED_TABLE可以查询数据库中所有底层的表。V$FIXED_TABLE视图显示数据库中所有动态性能表、视图和导出表。由于某些V$表（例如V$ROLLNAME）涉及底层的表，因此，没有列出.

V$FIXED_VIEW_DEFINITION这个视图包含所有固定视图（以V$起头的视图）的定义