I come, I see, I conquer

                    —Gaius Julius Caesar
  博客园 :: 首页 :: 新随笔 :: 联系 :: 订阅 :: 管理 ::
oracle数据字典

一、什么是数据字典

      数据字典是Oracle存放有关数据库信息的地方，其用途是用来描述数据的。比如一个表的创建者信息，创建时间信息，所属表空间信息，用户访问权限信息等。当用户在对数据库中的数据进行操作时遇到困难就可以访问数据字典来查看详细的信息。
　　Oracle中的数据字典有静态和动态之分。静态数据字典主要是在用户访问数据字典时不会发生改变的，但动态数据字典是依赖数据库运行的性能的，反映数据库运行的一些内在信息，所以在访问这类数据字典时往往不是一成不变的。以下分别就这两类数据字典来论述。
　　1． 静态数据字典
　　这类数据字典主要是由表和视图组成，应该注意的是，数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_*、 all_*、 dba_*。
　　user_*
　　　　该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）
　　all_*
　　　　该试图存储了当前用户能够访问的对象的信息。（与user_*相比，all_* 并不需要拥有该对象，只需要具有访问该对象的权限即可）
　　dba_*
　　　　该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）
　　从上面的描述可以看出，三者之间存储的数据肯定会有重叠，其实它们除了访问范围的不同以外（因为权限不一样，所以访问对象的范围不一样），其他均具有一致性。具体来说，由于数据字典视图是由SYS（系统用户）所拥有的，所以在却省情况下，只有SYS和拥有DBA系统权限的用户可以看到所有的视图。没有DBA权限的用户只能看到user_*和all_*视。如果没有被授予相关的SELECT权限的话，他们是不能看到 dba_*视图的。

      2． 动态数据字典
　　Oracle包含了一些潜在的由系统管理员如SYS维护的表和视图，由于当数据库运行的时候它们会不断进行更新，所以称它们为动态数据字典（或者是动态性能视图）。这些视图提供了关于内存和磁盘的运行情况，所以我们只能对其进行只读访问而不能修改它们。Oracle中这些动态性能视图都是以v$开头的视图。

 

二、系统中有哪些数据字典？

数据字典视图非常多，我们无法一一记住，但是有个视图，我们必须知道，那就是dictionary视图，该视图里记录了所有的数据字典视图的名称。所以当我们需要查找某个数据字典而又不知道这个信息在哪个视图里的时候，就可以在dictionary视图里找。该视图还有个同名词dict。

SQL> desc dict

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                         VARCHAR2(30)
 COMMENTS                                           VARCHAR2(4000)

select * from dict;

部分结果如下：

TABLE_NAMECOMMENTS
DBA_2PC_NEIGHBORS information about incoming and outgoing connections for pending transactions
DBA_2PC_PENDING info about distributed transactions awaiting recovery
DBA_ADVISOR_ACTIONS  
DBA_ADVISOR_COMMANDS  
DBA_ADVISOR_DEFINITIONS  
DBA_ADVISOR_DEF_PARAMETERS  
DBA_ADVISOR_DIRECTIVES  
DBA_ADVISOR_FINDINGS  
DBA_ADVISOR_JOURNAL  
DBA_ADVISOR_LOG  
DBA_ADVISOR_OBJECTS  
DBA_ADVISOR_OBJECT_TYPES  
DBA_ADVISOR_PARAMETERS  
DBA_ADVISOR_PARAMETERS_PROJ  
DBA_DATAPUMP_JOBS Datapump jobs
DBA_DATAPUMP_SESSIONS Datapump sessions attached to a job
DBA_DATA_FILES Information about database data files
DBA_DB_LINKS All database links in the database
DBA_DDL_LOCKS  
DBA_DEPENDENCIES Dependencies to and from objects
DBA_DIMENSIONS Description of the dimension objects accessible to the DBA
DBA_HIST_SEG_STAT_OBJ Segment Names
DBA_HIST_SERVICE_NAME Service Names
DBA_HIST_SERVICE_STAT Historical Service Statistics
DBA_HIST_SERVICE_WAIT_CLASS Historical Service Wait Class Statistics
DBA_HIST_SESSMETRIC_HISTORY System Metrics History
DBA_HIST_SESS_TIME_STATS CPU and I/O time for interesting (STREAMS) sessions
DBA_HIST_SGA SGA Historical Statistics Information
DBA_HIST_SGASTAT SGA Pool Historical Statistics Information
DBA_HIST_SGA_TARGET_ADVICE SGA Target Advice History
DBA_HIST_SHARED_POOL_ADVICE Shared Pool Advice History
DBA_HIST_SNAPSHOT Snapshot Information
DBA_HIST_SNAP_ERROR Snapshot Error Information
DBA_HIST_SQLBIND SQL Bind Information
DBA_HIST_SQLSTAT SQL Historical Statistics Information
DBA_HIST_SQLTEXT SQL Text
DBA_HIST_SQL_BIND_METADATA SQL Bind Metadata Information
DBA_HIST_SQL_PLAN SQL Plan Information
DBA_HIST_SQL_SUMMARY Summary of SQL Statistics
DBA_HIST_SQL_WORKAREA_HSTGRM SQL Workarea Histogram History
DBA_HIST_STAT_NAME Statistic Names
DBA_HIST_STREAMS_APPLY_SUM STREAMS Apply Historical Statistics Information
DBA_HIST_STREAMS_CAPTURE STREAMS Capture Historical Statistics Information
DBA_HIST_STREAMS_POOL_ADVICE Streams Pool Advice History
DBA_HIST_SYSMETRIC_HISTORY System Metrics History
DBA_HIST_SYSMETRIC_SUMMARY System Metrics History
DBA_HIST_SYSSTAT System Historical Statistics Information
DBA_HIST_SYSTEM_EVENT System Event Historical Statistics Information
DBA_HIST_SYS_TIME_MODEL System Time Model Historical Statistics Information
DBA_HIST_TABLESPACE_STAT Tablespace Historical Statistics Information
DBA_HIST_TBSPC_SPACE_USAGE Tablespace Usage Historical Statistics Information
DBA_HIST_TEMPFILE Names of Temporary Datafiles
DBA_HIST_TEMPSTATXS Temporary Datafile Historical Statistics Information
USER_COL_PRIVS Grants on columns for which the user is the owner, grantor or grantee
USER_COL_PRIVS_MADE All grants on columns of objects owned by the user
USER_COL_PRIVS_RECD Grants on columns for which the user is the grantee
USER_CONSTRAINTS Constraint definitions on user's own tables
USER_CONS_COLUMNS Information about accessible columns in constraint definitions
USER_CONS_OBJ_COLUMNS List of types an object column or attribute is constrained to in the tables owned by the user
USER_DATAPUMP_JOBS Datapump jobs for current user
USER_DB_LINKS Database links owned by the user
USER_DEPENDENCIES Dependencies to and from a users objects
USER_DIMENSIONS Description of the dimension objects accessible to the DBA
USER_DIM_ATTRIBUTES Representation of the relationship between a dimension level and
a functionally dependent column
USER_DIM_CHILD_OF Representaion of a 1:n hierarchical relationship between a pair of levels in
a dimension
USER_DIM_HIERARCHIES Representation of a dimension hierarchy
USER_DIM_JOIN_KEY Representation of a join between two dimension tables. 
USER_DIM_LEVELS Description of dimension levels visible to DBA
USER_DIM_LEVEL_KEY Representations of columns of a dimension level
USER_ENCRYPTED_COLUMNS Encryption information on columns of tables owned by the user
USER_ERRORS Current errors on stored objects owned by the user
ALL_DB_LINKS Database links accessible to the user
ALL_DEF_AUDIT_OPTS Auditing options for newly created objects
ALL_DEPENDENCIES Dependencies to and from objects accessible to the user
ALL_DEQUEUE_QUEUES All queues accessible to the user
ALL_DIMENSIONS Description of the dimension objects accessible to the DBA
ALL_DIM_ATTRIBUTES Representation of the relationship between a dimension level and
a functionally dependent column
ALL_DIM_CHILD_OF Representaion of a 1:n hierarchical relationship between a pair of levels in
a dimension
ALL_DIM_HIERARCHIES Representation of a dimension hierarchy
ALL_DIM_JOIN_KEY Representation of a join between two dimension tables. 
ALL_DIM_LEVELS Description of dimension levels visible to DBA
ALL_DIM_LEVEL_KEY Representations of columns of a dimension level
ALL_DIRECTORIES Description of all directories accessible to the user
ALL_ENCRYPTED_COLUMNS Encryption information on all accessible columns
ALL_ERRORS Current errors on stored objects that user is allowed to create
ALL_EVALUATION_CONTEXTS rule evaluation contexts seen by user
ALL_EVALUATION_CONTEXT_TABLES tables in all rule evaluation contexts seen by the user
ALL_EVALUATION_CONTEXT_VARS variables in all rule evaluation contexts seen by the user
ALL_EXTERNAL_LOCATIONS Description of the external tables locations accessible to the user
ALL_EXTERNAL_TABLES Description of the external tables accessible to the user
ALL_VIEWS Description of views accessible to the user
ALL_WARNING_SETTINGS Warnings ettings for objects accessible to the user
ALL_XML_SCHEMAS  
ALL_XML_SCHEMAS2  
AUDIT_ACTIONS Description table for audit trail action type codes.  Maps action type numbers to action type names
COLUMN_PRIVILEGES Grants on columns for which the user is the grantor, grantee, owner, or
an enabled role or PUBLIC is the grantee
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBMS_ALERT_INFO  
DBMS_LOCK_ALLOCATED  
DICTIONARY Description of data dictionary tables and views
DICT_COLUMNS Description of columns in data dictionary tables and views
DUAL  
GLOBAL_NAME global database name
INDEX_HISTOGRAM statistics on keys with repeat count
INDEX_STATS statistics on the b-tree
NLS_DATABASE_PARAMETERS Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS NLS parameters of the instance
NLS_SESSION_PARAMETERS NLS parameters of the user session
RESOURCE_COST Cost for each resource
ROLE_ROLE_PRIVS Roles which are granted to roles
ROLE_SYS_PRIVS System privileges granted to roles
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS Privileges which the user currently has set
SESSION_ROLES Roles which the user currently has enabled.
TABLE_PRIVILEGES Grants on objects for which the user is the grantor, grantee, owner,
or an enabled role or PUBLIC is the grantee
V$MAP_LIBRARY Synonym for V_$MAP_LIBRARY
V$MAP_FILE Synonym for V_$MAP_FILE
V$MAP_FILE_EXTENT Synonym for V_$MAP_FILE_EXTENT
V$MAP_ELEMENT Synonym for V_$MAP_ELEMENT
V$MAP_EXT_ELEMENT Synonym for V_$MAP_EXT_ELEMENT
V$MAP_COMP_LIST Synonym for V_$MAP_COMP_LIST
V$MAP_SUBELEMENT Synonym for V_$MAP_SUBELEMENT
V$LOCK_TYPE Synonym for V_$LOCK_TYPE
V$RMAN_CONFIGURATION Synonym for V_$RMAN_CONFIGURATION
V$DATABASE_INCARNATION Synonym for V_$DATABASE_INCARNATION
V$METRIC Synonym for V_$METRIC
V$METRIC_HISTORY Synonym for V_$METRIC_HISTORY
V$SYSMETRIC Synonym for V_$SYSMETRIC
V$SYSMETRIC_HISTORY Synonym for V_$SYSMETRIC_HISTORY
V$METRICNAME Synonym for V_$METRICNAME
V$METRICGROUP Synonym for V_$METRICGROUP
V$SERVICE_WAIT_CLASS Synonym for V_$SERVICE_WAIT_CLASS
V$SERVICE_EVENT Synonym for V_$SERVICE_EVENT
V$ACTIVE_SERVICES Synonym for V_$ACTIVE_SERVICES
V$SERVICES Synonym for V_$SERVICES
V$SYSMETRIC_SUMMARY Synonym for V_$SYSMETRIC_SUMMARY
V$SESSMETRIC Synonym for V_$SESSMETRIC
V$FILEMETRIC Synonym for V_$FILEMETRIC
V$FILEMETRIC_HISTORY Synonym for V_$FILEMETRIC_HISTORY
V$EVENTMETRIC Synonym for V_$EVENTMETRIC
V$WAITCLASSMETRIC Synonym for V_$WAITCLASSMETRIC
V$WAITCLASSMETRIC_HISTORY Synonym for V_$WAITCLASSMETRIC_HISTORY
V$SERVICEMETRIC Synonym for V_$SERVICEMETRIC
V$SERVICEMETRIC_HISTORY Synonym for V_$SERVICEMETRIC_HISTORY
V$ADVISOR_PROGRESS Synonym for V_$ADVISOR_PROGRESS
V$XML_AUDIT_TRAIL Synonym for V_$XML_AUDIT_TRAIL
V$SQL_JOIN_FILTER Synonym for V_$SQL_JOIN_FILTER
V$PROCESS_MEMORY Synonym for V_$PROCESS_MEMORY
V$PROCESS_MEMORY_DETAIL Synonym for V_$PROCESS_MEMORY_DETAIL
V$PROCESS_MEMORY_DETAIL_PROG Synonym for V_$PROCESS_MEMORY_DETAIL_PROG
V$SQLSTATS Synonym for V_$SQLSTATS
V$MUTEX_SLEEP Synonym for V_$MUTEX_SLEEP
V$MUTEX_SLEEP_HISTORY Synonym for V_$MUTEX_SLEEP_HISTORY
V$ENCRYPTION_WALLET Synonym for V_$ENCRYPTION_WALLET
GV$ENCRYPTION_WALLET Synonym for GV_$ENCRYPTION_WALLET
GV$MUTEX_SLEEP Synonym for GV_$MUTEX_SLEEP
GV$MUTEX_SLEEP_HISTORY Synonym for GV_$MUTEX_SLEEP_HISTORY
GV$SQLSTATS Synonym for GV_$SQLSTATS
GV$MAP_LIBRARY Synonym for GV_$MAP_LIBRARY
GV$MAP_FILE Synonym for GV_$MAP_FILE
GV$MAP_FILE_EXTENT Synonym for GV_$MAP_FILE_EXTENT
GV$MAP_ELEMENT Synonym for GV_$MAP_ELEMENT
GV$MAP_EXT_ELEMENT Synonym for GV_$MAP_EXT_ELEMENT
GV$MAP_COMP_LIST Synonym for GV_$MAP_COMP_LIST
GV$MAP_SUBELEMENT Synonym for GV_$MAP_SUBELEMENT
GV$MAP_FILE_IO_STACK Synonym for GV_$MAP_FILE_IO_STACK
GV$BSP Synonym for GV_$BSP
GV$OBSOLETE_PARAMETER Synonym for GV_$OBSOLETE_PARAMETER
GV$FAST_START_SERVERS Synonym for GV_$FAST_START_SERVERS
GV$FAST_START_TRANSACTIONS Synonym for GV_$FAST_START_TRANSACTIONS
GV$ENQUEUE_LOCK Synonym for GV_$ENQUEUE_LOCK
GV$TRANSACTION_ENQUEUE Synonym for GV_$TRANSACTION_ENQUEUE
GV$BACKUP_PIECE Synonym for GV_$BACKUP_PIECE
GV$BACKUP_DATAFILE Synonym for GV_$BACKUP_DATAFILE
GV$BACKUP_SPFILE Synonym for GV_$BACKUP_SPFILE
GV$BACKUP_REDOLOG Synonym for GV_$BACKUP_REDOLOG
GV$BACKUP_CORRUPTION Synonym for GV_$BACKUP_CORRUPTION
GV$COPY_CORRUPTION Synonym for GV_$COPY_CORRUPTION
GV$DATABASE_BLOCK_CORRUPTION Synonym for GV_$DATABASE_BLOCK_CORRUPTION
GV$MTTR_TARGET_ADVICE Synonym for GV_$MTTR_TARGET_ADVICE
GV$STATISTICS_LEVEL Synonym for GV_$STATISTICS_LEVEL
GV$DELETED_OBJECT Synonym for GV_$DELETED_OBJECT
GV$PROXY_DATAFILE Synonym for GV_$PROXY_DATAFILE
GV$PROXY_ARCHIVEDLOG Synonym for GV_$PROXY_ARCHIVEDLOG
GV$CONTROLFILE_RECORD_SECTION Synonym for GV_$CONTROLFILE_RECORD_SECTION
GV$ARCHIVED_LOG Synonym for GV_$ARCHIVED_LOG
GV$OFFLINE_RANGE Synonym for GV_$OFFLINE_RANGE
GV$DATAFILE_COPY Synonym for GV_$DATAFILE_COPY
GV$LOG_HISTORY Synonym for GV_$LOG_HISTORY
GV$RECOVERY_LOG Synonym for GV_$RECOVERY_LOG
V$LOGMNR_LOGFILE Synonym for V_$LOGMNR_LOGFILE
V$LOGMNR_PROCESS Synonym for V_$LOGMNR_PROCESS
V$LOGMNR_LATCH Synonym for V_$LOGMNR_LATCH
V$LOGMNR_TRANSACTION Synonym for V_$LOGMNR_TRANSACTION
V$LOGMNR_REGION Synonym for V_$LOGMNR_REGION
V$LOGMNR_CALLBACK Synonym for V_$LOGMNR_CALLBACK
V$LOGMNR_SESSION Synonym for V_$LOGMNR_SESSION
GV$LOGMNR_LOGFILE Synonym for GV_$LOGMNR_LOGFILE
GV$LOGMNR_PROCESS Synonym for GV_$LOGMNR_PROCESS
GV$LOGMNR_LATCH Synonym for GV_$LOGMNR_LATCH
GV$LOGMNR_TRANSACTION Synonym for GV_$LOGMNR_TRANSACTION
GV$LOGMNR_REGION Synonym for GV_$LOGMNR_REGION
GV$LOGMNR_CALLBACK Synonym for GV_$LOGMNR_CALLBACK
GV$LOGMNR_SESSION Synonym for GV_$LOGMNR_SESSION
GV$LOGMNR_STATS Synonym for GV_$LOGMNR_STATS
GV$LOGMNR_DICTIONARY_LOAD Synonym for GV_$LOGMNR_DICTIONARY_LOAD
GV$DB_CACHE_ADVICE Synonym for GV_$DB_CACHE_ADVICE
V$SQL_BIND_CAPTURE Synonym for V_$SQL_BIND_CAPTURE
GV$SQL_BIND_CAPTURE Synonym for GV_$SQL_BIND_CAPTURE
V$ALERT_TYPES Synonym for V_$ALERT_TYPES
GV$ALERT_TYPES Synonym for GV_$ALERT_TYPES
V$THRESHOLD_TYPES Synonym for V_$THRESHOLD_TYPES
GV$THRESHOLD_TYPES Synonym for GV_$THRESHOLD_TYPES
V$CLIENT_STATS Synonym for V_$CLIENT_STATS
GV$CLIENT_STATS Synonym for GV_$CLIENT_STATS
V$SERV_MOD_ACT_STATS Synonym for V_$SERV_MOD_ACT_STATS
GV$SERV_MOD_ACT_STATS Synonym for GV_$SERV_MOD_ACT_STATS
V$SERVICE_STATS Synonym for V_$SERVICE_STATS
GV$SERVICE_STATS Synonym for GV_$SERVICE_STATS
V$SYS_TIME_MODEL Synonym for V_$SYS_TIME_MODEL
GV$SYS_TIME_MODEL Synonym for GV_$SYS_TIME_MODEL
V$SESS_TIME_MODEL Synonym for V_$SESS_TIME_MODEL
GV$SESS_TIME_MODEL Synonym for GV_$SESS_TIME_MODEL
DBA_SQLSET_DEFINITIONS Synonym for DBA_SQLSET
USER_SQLSET_DEFINITIONS Synonym for USER_SQLSET

 

三、进一步了解数据字典（四层次）

来自：Oracle数据字典详解(原创)

Oracle字典视图包括四个层次，分别为x$内部表、基础数据字典表、数据字典视图和动态性能视图。

数据字典表和用户创建的表没有什么区别，不过数据字典表里的数据是Oracle系统存放的系统数据，而普通表存放的是用户的数据而已，为了方便的区别这些表，这些表的名字都是用"$"结尾，在我们看到的sql语句里看到"$"结尾的这些表，大家就可以想到这可能是一个数据字典表了，同样，既然如此，我们创建自己的用户表的时候就不要用“$”结尾，以免让别人误会，数据字典表是系统存放系统用户的，所以他的owner是sys，在手工用create database的命令的时候，会调用$ORACLE_HOME/rdbms/admin/sql.bsq文件，这个就会执行生成我们这些数据字典表。打开sql.bsq会发现很多数据字典几乎都以$结尾，比如col$,tab$等。

对于数据字典表，里面的数据是有数据库系统自身来维护的，所以这里虽然和普通表一样可以用DML语句来修改数据内容，但是大家最好还是不要自己来做了，因为这些表都是作用于数据库内部的，我们有时看到的604的的recursive sql这里的sql实际上很有可能就是我们的相关的数据字典表的执行内容。所以这里我们切记记住不要去修改这些表里的内容。

刚才说道了。这里的数据字典表的用户都是sys，存在在system这个表空间里，表名都用"$"结尾，为了便于用户对数据字典表的查询，这样的名字是不利于我们记忆的，所以Oracle对这些数据字典都分别建立了用户视图视图，不仅有更容易接受的名字，还隐藏了数据字典表表之间的关系，让我们字节通过视图来进行查询，简单而形象，Oracle针对这些对象的范围，分别把视图命名为DBA_XXXX, ALL_XXXX和USER_XXXX

注意：在创建数据库是会运行两个脚本。先运行catalog.sql，该脚本用来创建数据库的内部字典表。然后再运行catrpoc.sql，该脚本用来创建数据库内建的存储过程、包等pl\sql对象。如果我们是使用dbca来创建数据库，则dbca会自动调用这两个脚本。否则在执行create database命令来创建时，则需要手工运行这两个脚本。

追踪下 dba_synonyms

SQL> select table_name from dict where table_name like'%SYNONYM%';

TABLE_NAME
------------------------------
DBA_SYNONYMS
USER_SYNONYMS
ALL_SYNONYMS

在DBA_SYNONYMS验证下dba_synonyms是否为同名词

SQL>select OWNER,SYNONYM_NAME,TABLE_NAME from dba_synonyms where SYNONYM_NAME='DBA_SYNONYMS'

OWNER                          SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------ ------------------------------
PUBLIC                         DBA_SYNONYMS                   DBA_SYNONYMS

果然，DBA_SYNONYMS为DBA_SYNONYMS表的同名词。

继续查找dba_objects验证DBA_SYNONYMS为何种对象。

SQL>select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='DBA_SYNONYMS'

OWNER                          OBJECT_NAME         OBJECT_TYPE
------------------------------ ------------------ -------------------
SYS                               DBA_SYNONYMS        VIEW
PUBLIC                           DBA_SYNONYMS       SYNONYM

可以看出，DBA_SYNONYMS为sys用户下的视图，继续查找，dba_views查看DBA_SYNONYMS的具体定义。

SQL> select VIEW_NAME,TEXT from dba_views where VIEW_NAME='DBA_SYNONYMS';

VIEW_NAME                        TEXT
-------------------------------------------------------------------------------------------------------------------
DBA_SYNONYMS                  select u.name, o.name, s.owner, s.name, s.node from sys.user$ u, sys.syn$ s, sys

可以看到，DBA_SYNONYMS是基于sys用户下的user$和syn$这两张数据字典表创建的视图。

这里也就验证了，我们前面所说的数据字典组织方式：先基于数据字典创建数据字典视图，然后在基于数据字典视图创建同名词，并将该同名词赋给了PUBLIC，从而是得所有用户都可以通过同名词查看数据字典信息。

总结：数据字典表xxxx$   -->   数据字典视图  --->  同名词

 

 

动态性能视图概述

所谓动态性能视图，就是指将内存里的数据或控制文件里的数据以表的形式展现出来。他们实际都是需表，并不是真正的表。只不过是为了能够让我们更好的管理数据库的性能，所以将内存里的活动情况以表的形式展现出来。只要数据库还在运行，就会不断更新动态性能视图。一旦数据库关闭或崩溃，动态性能视图里的数据就会全部丢失，下次重启时则会重新计算。就像所有的数据字典的名词都存在dictionary里一样，v$fixed_table里存放了所有的动态性能视图的名称。所有的动态性能的属主都是sys，且都以v$_开头，创建同名词时则都以v$开头。

追踪v$session

SQL> select OWNER,SYNONYM_NAME,TABLE_NAME from dba_synonyms where SYNONYM_NAME='V$SESSION';

OWNER                          SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------ ------------------------------
PUBLIC                           V$SESSION                         V_$SESSION 
查看V_$SESSION的对象类型和属主

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V_$SESSION';

OWNER                          OBJECT_NAME        OBJECT_TYPE
------------------------------ ------------------ -------------------
SYS                                V_$SESSION         VIEW 
这里可以知道V_$SESSION是视图，查看该视图的具体定义。

SQL>select TEXT from dba_views where VIEW_NAME='V_$SESSION'

TEXT
-------------------------------------------------------------------------------------------------------------------------
select "SADDR","SID","SERIAL#","AUDSID","PADDR","USER#","USERNAME","COMMAND","OWNERID","TADDR","LOCKWAIT","STATUS","SERVER","SCHEMA#","SCHEMANAME","OSUSER","PROCESS","MACHINE","TERMINAL","PROGRAM","TYPE","SQL_ADDRESS","SQL_HASH_VALUE","SQL_ID","SQL_CHILD_NUMBER","PREV_SQL_ADDR","PREV_HASH_VALUE","PREV_SQL_ID","PREV_CHILD_NUMBER","PLSQL_ENTRY_OBJECT_ID","PLSQL_ENTRY_SUBPROGRAM_ID","PLSQL_OBJECT_ID","PLSQL_SUBPROGRAM_ID","MODULE","MODULE_HASH","ACTION","ACTION_HASH","CLIENT_INFO","FIXED_TABLE_SEQUENCE","ROW_WAIT_OBJ#","ROW_WAIT_FILE#","ROW_WAIT_BLOCK#","ROW_WAIT_ROW#","LOGON_TIME","LAST_CALL_ET","PDML_ENABLED","FAILOVER_TYPE","FAILOVER_METHOD","FAILED_OVER","RESOURCE_CONSUMER_GROUP","PDML_STATUS","PDDL_STATUS","PQ_STATUS","CURRENT_QUEUE_DURATION","CLIENT_IDENTIFIER","BLOCKING_SESSION_STATUS","BLOCKING_INSTANCE","BLOCKING_SESSION","SEQ#","EVENT#","EVENT","P1TEXT","P1","P1RAW","P2TEXT","P2","P2RAW","P3TEXT","P3","P3RAW","WAIT_CLASS_ID","WAIT_CLASS#","WAIT_CLASS","WAIT_TIME","SECONDS_IN_WAIT","STATE","SERVICE_NAME","SQL_TRACE","SQL_TRACE_WAITS","SQL_TRACE_BINDS" from v$session

可以看到v_$session视图是基于与动态性能视图v$session视图创建的，而动态性能视图的定义信息记录在v$fixed_view_definition里面

SQL> select VIEW_DEFINITION from v$fixed_view_definition where VIEW_NAME='V$SESSION';

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------------------------------------
select  SADDR , SID , SERIAL# , AUDSID , PADDR , USER# , USERNAME , COMMAND , OWNERID, TADDR , LOCKWAIT , STATUS , SERVER , SCHEMA# , SCHEMANAME ,OSUSER , PROCESS , MACHINE , TERMINAL , PROGRAM , TYPE , SQL_ADDRESS , SQL_HASH_VALUE, SQL_ID, SQL_CHILD_NUMBER , PREV_SQL_ADDR , PREV_HASH_VALUE , PREV_SQL_ID, PREV_CHILD_NUMBER , PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID, MODULE , MODULE_HASH , ACTION , ACTION_HASH , CLIENT_INFO , FIXED_TABLE_SEQUENCE , ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAIT_BLOCK# , ROW_WAIT_ROW# , LOGON_TIME , LAST_CALL_ET , PDML_ENABLED , FAILOVER_TYPE , FAILOVER_METHOD , FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS, CURRENT_QUEUE_DURATION, CLIENT_IDENTIFIER, BLOCKING_SESSION_STATUS, BLOCKING_INSTANCE,BLOCKING_SESSION,SEQ#, EVENT#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW, P3TEXT,P3,P3RAW,WAIT_CLASS_ID, WAIT_CLASS#,WAIT_CLASS,WAIT_TIME, SECONDS_IN_WAIT,STATE,SERVICE_NAME, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS from GV$SESSION where inst_id = USERENV('Instance')

可以看到动态性能视图v$session是基于动态性能视图gv$session视图创建的，继续追踪

SQL> select VIEW_DEFINITION from v$fixed_view_definition where VIEW_NAME='GV$SESSION'

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------------------------------------
select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.ksusesow, decode(s.ksusetrn,hextoraw('00'),null,s.ksusetrn),decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED', 'KILLED'),decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'),  s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid,s.ksusemnm,s.ksusetid,s.ksusepnm, decode(bitand(s.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.ksusesch, 65535, to_number(null), s.ksusesch),  s.ksusepsq, s.ksusepha, s.ksusepsi,  decode(s.ksusepch, 65535, to_number(null), s.ksusepch),  decode(s.ksusepeo,0,to_number(null),s.ksusepeo),  decode(s.ksusepeo,0,to_number(null),s.ksusepes),  decode(s.ksusepco,0,to_number(null),s.ksusepco),  decode(s.ksusepco,0,to_number(null),s.ksusepcs),  s.ksuseapp, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt, s.ksuseltm, s.ksusectm,decode(bitand(s.ksusepxopt, 12),0,'NO','YES'),decode(s.ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),decode(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'),decode(s.ksusefs, 1, 'YES', 'NO'),s.ksusegrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')),decode(bitand(s.ksusepxopt,2),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),decode(bitand(s.ksusepxopt,32),32,'FORCED',decode(bitand(s.ksusepxopt,16),16,'DISABLED','ENABLED')),  s.ksusecqd, s.ksuseclid, decode(s.ksuseblocker,4294967295,'UNKNOWN',  4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'NOT IN WAIT','VALID'),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksuseblocker, 2147418112)/65536),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(null),bitand(s.ksuseblocker, 65535)),s.ksuseseq, s.ksuseopc,e.kslednam, e.ksledp1, s.ksusep1,s.ksusep1r,e.ksledp2, s.ksusep2,s.ksusep2r,e.ksledp3,s.ksusep3,s.ksusep3r,e.ksledclassid,  e.ksledclass#, e.ksledclass, decode(s.ksusetim,0,0,-1,-1,-2,-2, decode(round(s.ksusetim/10000),0,-1,round(s.ksusetim/10000))), s.ksusewtm,decode(s.ksusetim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME',  -1, 'WAITED SHORT TIME',   decode(round(s.ksusetim/10000),0,'WAITED SHORT TIME','WAITED KNOWN TIME')),s.ksusesvc, decode(bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.ksuseflg2,128),128,'TRUE','FALSE')from x$ksuse s, x$ksled e where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksuseopc=e.indx

可以看到gv$session是的最终定义信息是来自于x$ksuse s和x$ksle，这些表是动态性能视图的机表。但是这些表都是内存表，表中的数据不会存在数据文件中，只存在内存中。

 

总结：x$xxxx   -->   gv$xxx视图  --->  v$xxx视图  --->  v_$xxx视图  --->  同名词

posted on 2017-01-19 18:18  jcsu  阅读(394)  评论(0)    收藏  举报