DB-hub Technology Oracle 教程 Data Dictionary

Oracle 教程 Data Dictionary

1. 概念

Oracle数据字典是由一组只读的表及其视图组成，存储有关数据库结构信息的一些数据库对象。数据库字典描述了实际数据是如何组织的。比如一个表的创建者信息，创建时间信息，所属表空间信息，用户访问权限信息等。对它们可以象处理其他数据库表或视图一样进行查询，但不能进行任何修改。它们存放在SYSTEM表空间中，当用户在对数据库中的数据进行操作时遇到困难就可以访问数据字典来查看详细的信息。用户可以用SQL语句访问数据库数据字典。

Oracle数据库字典通常是在创建和安装数据库时被创建的，Oracle数据字典是Oracle数据库系统工作的基础，没有数据字典的支持，Oracle数据库系统就不能进行任何工作。数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。

数据字典记录了数据库的物理、逻辑结构，内容包括：

  • 数据库的物理结构和逻辑结构
  • 数据库对象的信息（表、视图、快照、索引、聚集、同义词、序列、过程、函数、包及触发器等）
  • 数据库对象空间分配及当前使用情况
  • 列的缺省值
  • 数据的完整性约束信息
  • 审计信息（如谁存取或修改各种对象）
  • 用户信息
  • 角色信息
  • 权限信息

数据字典由四部分组成：

  • 内部表（X$）：Oracle的核心，官网不做说明, Oracle通过大量X$建立起大量视图，仅供用户查询。
  • 数据字典表：用以存储表、索引、约束以及其他数据库结构信息，通常以$结尾，如tab$,obj$,ts$,aud$等。
  • 数据字典视图
    • 静态视图
    • 动态性能视图

什么时间数据字典会改动呢？

当使用DDL(DATA DEFINE LANGUAGE)语句的时候，比如：创建表 ，删除表，添加约束和索引等，ORACLE SERVER会修改数据字典的信息。数据字典的owner（所有者）是SYS 用户，其它用户不能修改SYS用户的信息。

表dict记录了所有数据字典表的名称，dict是dictionary的同义词。

SQL> select * from dict where table_name='DBA_OBJECTS';
TABLE_NAME        COMMENTS
---------------   ------------------------------------------------------------
DBA_OBJECTS       All objects in the database

SQL> select count(*) from dict;
  COUNT(*)
----------
      2323

2. 数据字典表

数据字典表里的数据是Oracle系统存放的系统数据，而普通表存放的是用户的数据。为了方便的区别这些表，这些表的名字都是用"$"结尾，这些表属于SYS用户。
数据字典表由ORACLE_HOME/rdbms/admin/sql.bsq脚本创建， 这个脚本里又调用了其他的脚本来创建这些数据字典表。 在那些创建脚本里有创建基表的SQL。

Oracle 对数据字典表的说明：
These underlying tables store information about the database. Only Oracle Database should write to and read these tables. Users rarely access the base tables directly because they are normalized and most data is stored in a cryptic format.
这些数据字典表，只有Oracle 能够进行读写。

SYS用户下的这些数据字典表，存放在system 表空间下面，表名都用”$”结尾，为了便于用户对数据字典表的查询， Oracle对这些数据字典都分别建立了用户视图，这样即容易记住，还隐藏了数据字典表表之间的关系，Oracle针对这些对象的范围，分别把视图命名为DBA_XXXX, ALL_XXXX和USER_XXXX。

Oracle为了便于汇总数据字典表的信息，把所有的数据字典都汇集到dictionary表里了，通过对这个表的查询，可以找到数据库提供的数据字典。

3. 数据字典视图

数据字典视图由脚本：$ORACLE_HOME/rdbms/admin/catalog.sql 创建， 该脚本也只是一个总的调用，在该脚本里由调用了其他的脚本。 在那些脚本里，可以看到创建视图的SQL。
因为这个脚本会创建动态性能视图，所以在做DB升级的时候，也需要执行这个脚本，重新创建视图。

数据字典视图分2类：静态数据字典（静态视图）和动态数据字典（动态性能视图）。

3.1 静态数据字典（静态视图）

静态数据字典中的视图分为三类，它们分别由三个前缀够成：USER_XXXX、 ALL_XXXX、 DBA_XXXX。

  • USER_XXXX：该视图存储了关于当前用户所拥有的对象的信息，即所有在该用户模式下的对象。
  • ALL_XXXX：该试图存储了当前用户能够访问的对象的信息, 而不是当前用户拥有的对象。与USER_XXXX相比，ALL_XXXX并不需要拥有该对象，只需要具有访问该对象的权限即可。
  • DBA_XXXX：该视图存储了数据库中所有对象的信息。前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限。

这些视图由SYS用户创建的，所以使用需要加上SYS,为了方便， Oracle为每个数据字典表的视图头建立了同名字的公共同义词(public synonyms). 这样简单的处理就省去了写”SYS.”的麻烦。

三类视图之间的数据有重叠，它们之间除了因为访问权限不一样所以访问范围不一样之外，其他均具有一致性。具体来说，由于数据字典视图是由SYS（系统用户）所拥有的，在缺省情况下，只有SYS和拥有DBA系统权限的用户可以看到所有的视图。没有DBA权限的用户只能看到USER_和ALL_视图。如果用户没有被授予相关的SELECT权限，就不能看到DBA_视图的内容。举例来说，USER_USERS、ALL_USERS、DBA_USERS视图内放的都是有关用户的信息，USER_USERS内放的是用户名，用户状态，用户创建时间等信息，而ALL_USERS内放的是当前数据库内所有用户的用户名称和用户创建时间。DBA_USERS内描述的字段信息和USER_USERS一样，但显示所有用户的信息。三者一般同时出现，有USER_USERS就有DBA_USERS和ALL_USERS；同样，有USER_TABLES就有DBA_TABLES和ALL_TABLES等，只是范围不一样。

几个常用的静态性能视图：
（1）user_tables：主要描述当前用户拥有的所有表的信息，主要包括表名、表空间名、簇名等。通过此视图可以清楚了解当前用户可以操作的表有哪些。

（2）user_indexes： 查询该用户拥有哪些索引。

（3）user_views： 查询该用户拥有哪些视图

（4）user_objects：查询该用户拥有哪些数据库对象,对象包括表、视图、存储过程、触发器、包、索引、序列、Java文件等。

SQL>select object_type,status
from user_objects
where object_name=upper（‘package1’）；

（5）user_users：主要描述当前用户的信息，主要包括当前用户名、帐户id、帐户状态、表空间名、创建时间等。

（6）all_objects：查询某一用户下的所有表、过程、函数等信息。

查看最常用静态对象
1．查看用户信息
查看当前用户的缺省表空间：

SQL>select username,default_tablespace
 from user_users;

查看当前用户的角色：

SQL>select *  from user_role_privs;

查看当前用户的系统权限和表级权限：

SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;

2．表
查看用户下所有的表：

SQL>select * from user_tables;

查看名称包含log字符的表：

SQL>select object_name,object_id
from user_objects
where instr（object_name,'LOG'）>0;

查看某表的创建时间：

SQL>select object_name,created
from user_objects
where object_name=upper（'&table_name'）;

查看某表的大小：

SQL>select sum（bytes）/（1024*1024） as "size（M）"
from user_segments
where segment_name=upper（'&table_name'）;

查看放在Oracle的内存区里的表：

SQL>select table_name,cache
from user_tables
where instr（cache,'Y'）>0;

3．索引
查看索引个数和类别：

SQL>select index_name,index_type,table_name 
from user_indexes
order by table_name;

查看索引被索引的字段：

SQL>select *
from user_ind_columns
where index_name=upper（'&index_name'）;

查看索引的大小：

SQL>select sum（bytes）/（1024*1024） as "MB"
from user_segments
where segment_name=upper（'&index_name'）;

4．序列号
查看序列号，last_number是当前值：

SQL>select * from user_sequences;

5．视图
查看视图的名称：

SQL>select view_name
 from user_views;

查看创建视图的select语句：

SQL>set view_name,text_length
from user_views;
SQL>set long 2000;  --说明：可以根据视图的text_length的值设定set long的大小
SQL>select text
from user_views
where view_name=upper（'&view_name'）;

6．同义词
查看同义词的名称：

SQL>select * from user_synonyms;

7．约束条件
查看某表的约束条件：

SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
  from user_constraints
  where table_name = upper（'&table_name'）;

SQL>select c.constraint_name,c.constraint_type,cc.column_name
    from user_constraints c,user_cons_columns cc
    where c.owner = upper（'&table_owner'） and c.table_name = upper（'&table_name'）
    and c.owner = cc.owner and c.constraint_name = cc.constraint_name
    order by cc.position;

8．存储函数和过程
查看函数和过程的状态：

SQL>select object_name,status from user_objects where object_type='FUNCTION';

SQL>select object_name,status from user_objects where object_type='PROCEDURE';

查看函数和过程：

SQL>select text from all_source where owner=user and name=upper（'&plsql_name'）;

3.2 动态数据字典（动态性能视图）

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

Throughout its operation, Oracle Database maintains a set of virtual tables that record current database activity. These views are called dynamic performance views because they are continuously updated while a database is open and in use. The views, also sometimes called V$ views。

V$视图是基于X$虚拟视图的。V$视图是SYS用户所拥有的，在缺省状况下，只有SYS用户和拥有DBA系统权限的用户可以看到所有的视图，没有DBA权限的用户可以看到USER_和ALL_视图，但不能看到DBA_视图。与DBA_,ALL,和USER_视图中面向数据库信息相反，这些视图可视的给出了面向实例的信息。
动态性能表用于记录当前数据库的活动，只存于数据库运行期间，实际的信息都取自内存和控制文件。 DBA可以使用动态视图来监视和调整数据库。

为什么需要动态性能视图？
静态数据字典就像汽车的所有零件图，它列出了汽车的所有的部分，显示了汽车零件在汽车中的位置。
动态性能视图就像汽车的车速计和发动机的转速计，它显示当前汽车的速度，根据车速计，司机可以对汽车加速或者减速，数据库也一样，动态性能视图提供了数据库各个部分当前状况的反馈。

几个主要的动态性能视图：

1．V$ACCESS视图
视图内描述了数据库中锁定的数据库对象以及访问这些对象的会话对象（session对象）。

2．V$SESSION视图
视图内描述了当前会话的详细信息。该视图字段较多。

3．V$ACTIVE_INSTANCE视图
视图内描述了当前数据库下活动实例的信息。

4．V$CONTEXT视图
视图内描述了当前会话的属性信息，比如命名空间、属性值等。

数据库不同的状态的动态性能视图：

  • 数据库处于NOMOUNT状态。数据库启动时，会打开参数文件，分配SGA内存并启动后台进程。此时，数据库还没有挂载。动态性能视图收集的信息来源只有SGA，而不会从控制文件中收集信息。所以，动态性能视图的数量很少。
  • 当数据库处于MOUNT状态时，数据库会根据初始化参数打开所有的控制文件。所以，当数据库处于MOUNT状态时，动态性能视图收集到的信息就要比NOMOUNT状态多的多。此时，动态性能视图还会去收集控制文件的相关信息。不过此时动态性能视图所收集到的资料还不是最全的。
  • 当用户打开数据库时，数据库会根据控制文件所记载的信息去打开所有的数据库文件以及重做日志。此时，数据库管理员除了可以从SGA和控制文件中获取信息的动态性能视图外，还可以访问与数据库性能相关的动态性能视图，如会话等待时间等视图。另外只有在OPEN状态时，我们才能够访问数据库的数据字典视图。

3.2.1 GV$ GV_$ X$

GV$视图是从Oracle 8开始引入的，G表示Global。除了一些特例以外（如：V$CACHE_LOCK、V$LOCK_ACTIVITY、V$LOCKS_WITH_COLLISIONS和V$ROLLNAME），每个V$视图都有一个对应的GV$视图存在，如GV$FIXED_TABLE对应V$FIXED_TABLE视图。
GV$的产生是为了满足RAC或者并行服务器(OPS)环境的需要。在并行服务器环境中，GV$视图返回的是所有实例信息；而V$视图则是在GV$视图的基础上，增加了实例ID判断后的结果，即每个V$视图都是在GV$视图中包含语句where inst_id = USERENV（’Instance’）而产生的，如图所示:


V$、GV$实际上又是GV$，V$视图的同义词（图中以‘=’号表示），V$，GV$是用下面SQL语句从GV$，V$视图中创建的。

SQL>create or replace view v_$nls_parameters as select * from v$nls_parameters;
SQL>create or replace public synonym v$nls_parameters for v_$nls_parameters;
SQL>grant select on v_$nls_parameters to public;

可以通过v$fixed_table 视图查到所有的动态视图的名称；用于调优和数据库监控。

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

通过V$FIXED_VIEW_DEFINITION视图可以获取组成V$视图的底层X$表的所有信息。

SQL> select view_definition from vfixed_view_definition where view_name='VFIXED_TABLE';

X$表是Oracle数据库的运行基础，在数据库启动时由Oracle应用程序动态创建。
V$视图底层是一组虚拟的动态表称为X$表，oracle不允许直接访问X$表，而是在这些表上创建视图，然后再创建这些视图的同义词。
基表（x$)————->视图（v_$)————–>同义词v$——->User access
X$表创建的信息也可以通过bootstrap$表查看，bootstrap$表中记录了数据库启动的基本信息。
bootstrap$实际上存储的是数据字典的基表的定义，如OBJ$,C_OBJ$,TAB$等等。Oracle通过读取这些定义创建数据字典的基表，进而创建数据字典。

SQL> select * from bootstrap$;

3.2.2 动态性能视图的结构

数据库管理员经常会用到动态性能视图。动态性能视图是管理员获取数据库信息的一个接口。图2-36、图2-37和图2-38给出动态数据字典的结构和分类，以帮助理解。

3.2.3 动态性能视图分类

Oracle数据库中保存了太多动态性能视图，为了便于掌握，分类如下：

分类 动态性能视图
备份与恢复 v$archive
v$backup
v$recovery
v$recover_file
缓冲 v$bhv
v$db_object_cache
v$library_cache
v$object_dependency
v$rowcache
v$shared_pool_reserved
SQL语句 v$execution
v$open_cursor
v$sql
v$sqlarea
v$sqltext
v$sqltext_with_newlines
数据库 v$controlfile
v$database
v$db_file
v$db_pipes
v$dblink
数据导入 v$loadcstat
v$loadstat
数据库实例 v$bgprocess
v$fixed_table
v$fixed_view_definition
v$indexed_fixed_column
v$instance
v$license
v$option
v$reqdist
v$sga
v$sgastat
v$timer
v$type_size
v$version
I/O v$filestat
v$waistat
锁 `v$_lock
v$lock
v$resource`
多线程 v$circuit
v$dispatcher
v$mts
v$queue
v$shared_server
系统运行情况 v$enent_name
v$sysstat
v$system_cursor_cache
v$system_event
并行查询 v$pq_sesstat
v$pg_slave
v$pq_sysstat
并行服务器 v$cache
v$cache_lock
v$false_ping
v$lock_element
v$lock_with_collsions
v$ping
初始化参数 v$nls_parameters
v$nls_valid_value
v$parameters
重做日志 v$log
v$logfile
v$loghist
v$log_history
v$thread
回滚段 v$rollname
v$rollstat
v$transaction
安全 v$enabledprivs
v$pwfile_users
v$syslabel
会话 v$access
v$process
v$sesstat
v$sess_io
v$statname
多样性 v$compatibility
v$compatseg

3.3 数据字典视图总结

动态性能视图填充了来自实例和控制文件的信息，前缀为DBA_、ALL_、USER_的视图则填充了来自数据字典的信息，此差异决定了可以在不同启动阶段查询不同的视图。
动态性能视图中的信息则是动态变化的，它反映了实例的实际运行情况，这些信息来自SGA或者控制文件，随着实例的关闭和重新启动，这些信息将重新产生。
从动态性能视图中可以获得一些有用的统计信息，这些信息主要用于对数据库的性能进行调优。

例如：
执行下面的SELECT语句可以了解数据库中一些等待事件的发生情况：

SELECT event, total_waits, time_waited, average_wait FROM v$system_event;

执行下面的语句可以查看SGA中每种缓冲区的大小：

SELECT name, bytes/1024/1024 mb FROM v$sgastat;

动态性能视图用于记录当前实例的活动信息，当启动数据库时，系统会建立动态性能视图；当停止数据库时，系统会删除动态性能视图。
oracle的所有动态性能视图都是以v_$，v$或者gv$开始的，并且oracle为每个动态性能视图都提供了相应的同义词，V$是V_$的同义词，例如v_$datafile的同义词为v$datafile。
动态性能视图的所有者为SYS，一般情况下，由DBA或是特权用户来查询动态性能视图。

CATALOG.SQL文件包含这些视图的定义以及公用同义词，必须运行CATALOG.SQL创建这些视图及同义词。升级系统也后要执行这个脚本。

20个常用的动态性能视图：
（1） v$sysstat
（2） v$sesstat
（3） vsql&vsql_plan
（4） vsqltext&vsqlarea
（5） v$session
（6） vsession_wait&vsession_event
（7） v$process
（8） vlock&vlocked_object
（9） v$filestat
（10）v$session_longops
（11）vlatch v$latch_children
（12）v$db_object_cache
（13）v$open_cursor
（14）vparameter&vsystem_parameter
（15）v$rollstat
（16）v$rowcache
（17）vsegstat&vsegment_statistics
（18）v$system_event
（19）v$undostat
（20）v$waitstat