7 The Data Dictionary

001 This chapter describes the central set of read-only reference tables and views of each Oracle database, known collectively as the data dictionary.
　		 本章描述了 Oracle 数据库中一系列重要的只读参考表和视图（read-only reference tables and views），这些表和视图被统称为数据字典（data dictionary）。
　
002 This chapter contains the following topics: 本章包含以下主题：
003

Introduction to the Data Dictionary

7.1 数据字典简介
004 One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains:
  • The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
  • How much space has been allocated for, and is currently used by, the schema objects
  • Default values for columns
  • Integrity constraint information
  • The names of Oracle users
  • Privileges and roles each user has been granted
  • Auditing information, such as who has accessed or updated various schema objects
  • Other general database information
 数据字典（data dictionary）是 Oracle 数据库的一个重要组成部分，这是一组用于记录数据库信息的只读（read-only）表。数据字典中包 含：
  • 数据库中所有方案对象（schema object）的定义(包括表，视图，索引，簇，同义词，序列，过程，函数，包，触发器等等)
  • 数据库为一个方案对象分配了多少空间，以及该对象当前使用了多少空间
  • 列的默认值
  • 完整性约束（integrity constraint）信息
  • 数据库用户名
  • 每个用户被授予（grant）的权限（privilege）与角色（role）
  • 监控（audit）信息，例如哪个用户对某个方案对象进行了访问或更新操作
  • 数据库中的其他概要信息
005 The data dictionary is structured in tables and views, just like other database data. All the data dictionary tables and views for a given database are stored in that database's SYSTEM tablespace.
　		 与数据库内的其他数据相同，数据字典也是以表及视图的形式组织的。一个数据库全部的数据字典表与数据字典视图都存储在该数据库的 SYSTEM 表空间内。
　
006 Not only is the data dictionary central to every Oracle database, it is an important tool for all users, from end users to application designers and database administrators. Use SQL statements to access the data dictionary. Because the data dictionary is read only, you can issue only queries (SELECT statements) against it's tables and views.
　		 数据字典不仅是 Oracle 数据库的基础组成部分，她也为所有用户--从终端用户，到应用程序设计者，再到数据库管理员--提供了一个重要的工具。数据字典可以通过 SQL 语句进行访问。由于数据字典是只读的，用户只能对这些表和视图执行查询操作（SELECT 语句）。
　
007

See Also:

"Bigfile Tablespaces" for more information about SYSTEM tablespaces
另见：

“大文件表空间” 了解关于 SYSTEM 表空间的信息
008

Structure of the Data Dictionary

7.1.1 数据字典的组织结构
009 The data dictionary consists of the following:
　		 数据字典由以下两种结构组成：
　
010

Base Tables

7.1.1.1 基表
011 The underlying tables that store information about the associated database. Only Oracle should write to and read these tables. Users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format.
　		 基表（base table）用于存储相关的数据库信息。只有 Oracle 才能读写这些表。用户很少直接访问她们，因为这些表是规范化的（normalized），且其中的大部分数据的含义是普通用户无法理解的。
　
012

User-Accessible Views

7.1.1.2 用户访问视图
013 The views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information, such as user or table names, using joins and WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables.
　		 用户访问视图（user-accessible view）用于对数据字典基表内存储的数据进行汇总与展示。这些视图利用连接（join）及 WHERE 子句简化基表中的数据，将其解码（decode）为用户可理解的信息，例如用户名，表名等。大多数数据库用户只能访问这些视图，而不能访问基表。
　
014

SYS, Owner of the Data Dictionary

7.1.2 SYS，数据字典的所有者
015 The Oracle user SYS owns all base tables and user-accessible views of the data dictionary. No Oracle user should ever alter (UPDATE, DELETE, or INSERT) any rows or schema objects contained in the SYS schema, because such activity can compromise data integrity. The security administrator must keep strict control of this central account.
　		 Oracle 中的 SYS 用户拥有数据字典中的所有基表（base table）及用户访问视图（user-accessible view）。其他 Oracle 用户不应修改（UPDATE，DELETE，或 INSERT）SYS 方案中的对象定义及数据，否则将危害数据库的数据完整性（data integrity）。安全管理员必须严格控制此账户。
　
016

Caution:

Altering or manipulating the data in data dictionary tables can permanently and detrimentally affect the operation of a database.
警告：

修改或操作数据字典表中的数据可能会给数据库的运行带来不可恢复的损害。
017

How the Data Dictionary Is Used

7.2 如何使用数据字典
018 The data dictionary has three primary uses:
  • Oracle accesses the data dictionary to find information about users, schema objects, and storage structures.
  • Oracle modifies the data dictionary every time that a data definition language (DDL) statement is issued.
  • Any Oracle user can use the data dictionary as a read-only reference for information about the database.
 数据字典有三个主要用途：
  • Oracle 利用数据字典来获取关于用户，方案对象，及存储结构（storage structure）的信息。
  • 每当数据库中执行了 DDL 语句后，Oracle 将对数据字典进行修改。
  • 所有 Oracle 用户都可以使用数据字典来获取关于数据库的信息。
019

How Oracle Uses the Data Dictionary

7.2.1 Oracle 如何使用数据字典
020 Data in the base tables of the data dictionary is necessary for Oracle to function. Therefore, only Oracle should write or change data dictionary information. Oracle provides scripts to modify the data dictionary tables when a database is upgraded or downgraded.
　		 数据字典基表（base table）中的数据是 Oracle 正常工作所必须的。因此，只有 Oracle 才能增加或修改数据字典中的信息。当用户对数据库进行升级（upgrade）或降级（downgrade）的操作时，Oracle 提供了修改数据字典表的脚本。
　
021

Caution:

No data in any data dictionary table should be altered or deleted by any user.

警告：

任何用户都不应该删除或修改数据字典表内的数据。
022 During database operation, Oracle reads the data dictionary to ascertain that schema objects exist and that users have proper access to them. Oracle also updates the data dictionary continuously to reflect changes in database structures, auditing, grants, and data.
　		 在数据库运行期间，Oracle 通过数据字典内的信息来确定方案对象（schema object）是否存在，以及用户是否具备相应的权限。Oracle 会持续地更新数据字典，以便反映数据库在结构，数据，授权，监控（audit）等方面的变化。
　
023 For example, if user Kathy creates a table named parts, then new rows are added to the data dictionary that reflect the new table, columns, segment, extents, and the privileges that Kathy has on the table. This new information is then visible the next time the dictionary views are queried.
　		 例如，用户 Kathy 创建了名为 parts 的表，则数据字典中将会增加记录这个新表，及相关列，数据段（segment），数据扩展（extent）的数据，并记录 Kathy 对此表的权限（privilege）信息。当数据字典再次被查询时，用户就能够看到这些信息。
　
024

Public Synonyms for Data Dictionary Views

7.2.1.1 数据字典视图的公共同义词
025 Oracle creates public synonyms for many data dictionary views so users can access them conveniently. The security administrator can also create additional public synonyms for schema objects that are used systemwide. Users should avoid naming their own schema objects with the same names as those used for public synonyms.
　		 Oracle 为许多数据字典视图创建了公共同义词（public synonym），以便用户能够方便地对其进行访问。安全管理员也可以为需要在全系统使用的方案对象（schema object）创建公共同义词。用户应防止自身的方案对象名与公共同义词名重复。
　
026

Cache the Data Dictionary for Fast Access

7.2.1.2 缓存数据字典以便加速访问
027 Much of the data dictionary information is kept in the SGA in the dictionary cache, because Oracle constantly accesses the data dictionary during database operation to validate user access and to verify the state of schema objects. All information is stored in memory using the least recently used (LRU) algorithm.
　		 由于 Oracle 在数据库运行中需要不断地访问数据字典来验证用户权限及方案对象状态，大量的数据字典信息将被缓存到 SGA 的数据字典缓存（dictionary cache）内。这些数据将依据最近最少使用（least recently used，LRU）算法存储在内存中。
　
028 Parsing information is typically kept in the caches. The COMMENTS columns describing the tables and their columns are not cached unless they are accessed frequently.
　		 解析信息（parsing information）通常会保存在缓存中。而用于描述表及列的 COMMENTS 通常不会被缓存，除非用户使用很频繁。
　
029

Other Programs and the Data Dictionary

7.2.1.3 其他程序与数据字典
030 Other Oracle products can reference existing views and create additional data dictionary tables or views of their own. Application developers who write programs that refer to the data dictionary should refer to the public synonyms rather than the underlying tables: the synonyms are less likely to change between software releases.
　		 其他 Oracle 产品可能会引用已有的数据字典视图，并创建其专用数据字典表及视图。Oracle 应用程序开发者在程序中应该引用数据字典的公共同义词（public synonym）而非直接引用基表：因为公共同义词在不同版本的 Oracle 间通常会保持一致。
　
031

How to Use the Data Dictionary

7.2.2 如何使用数据字典
032 The views of the data dictionary serve as a reference for all database users. Access the data dictionary views with SQL statements. Some views are accessible to all Oracle users, and others are intended for database administrators only.
　		 所有数据库用户都可以参考数据字典视图提供的信息。用户可以使用 SQL 语句来访问数据字典视图。有些视图可以供所有 Oracle 用户使用，而有些只有 DBA 才能访问。
　
033 The data dictionary is always available when the database is open. It resides in the SYSTEM tablespace, which is always online.
　		 只要数据库处于打开（open）状态，用户就可以使用数据字典。因为数据字典存储在 SYSTEM 表空间内，此表空间在数据库被打开后总是联机（online）的。
　
034 The data dictionary consists of sets of views. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes:
　		 数据字典视图可以被分为多组。大多数情况下，一组视图由三个包含相似信息的视图构成，其中的区别在于视图名称的前缀：
　
035 Table 7-1 Data Dictionary View Prefixes
　		 表7-1 数据字典视图的前缀
036
Prefix Scope
USER User's view (what is in the user's schema)
　
ALL Expanded user's view (what the user can access)
　
DBA Database administrator's view (what is in all users' schemas)
　

前缀 覆盖范围
USER 用户视图（用户方案（schema）内的对象）
　
ALL 扩展的用户视图（用户可访问的对象）
　
DBA DBA 视图 (所有用户方案内的对象)
　
037 The set of columns is identical across views, with these exceptions:
  • Views with the prefix USER usually exclude the column OWNER. This column is implied in the USER views to be the user issuing the query.
  • Some DBA views have additional columns containing information useful to the administrator.
 同一组中各视图所包含的列是完全相同的，但有以下例外：
  • 使用 USER 前缀的视图通常没有 OWNER 列。对于 USER 视图来说，这列的值等于提交查询的用户名。
  • 有些以 DBA 为前缀的视图包含管理员所需的额外列。
038
See Also:

Oracle Database Reference for a complete list of data dictionary views and their columns
另见：

Oracle Database Reference 了解所有的数据字典视图及其所包含的列
039

Views with the Prefix USER

7.2.2.1 以 USER 为前缀的视图
040 The views most likely to be of interest to typical database users are those with the prefix USER. These views:
  • Refer to the user's own private environment in the database, including information about schema objects created by the user, grants made by the user, and so on
  • Display only rows pertinent to the user
  • Have columns identical to the other views, except that the column OWNER is implied
  • Return a subset of the information in the ALL views
  • Can have abbreviated PUBLIC synonyms for convenience
 普通数据库用户最常使用是以 USER 为前缀的数据字典视图。这些视图的特点是：
  • 反映了数据库中某个用户的全部情况，包括由此用户创建的方案对象，此用户所做的授权行为，等等。
  • 只展示和某个用户相关的信息
  • 与其他视图拥有相同的列，只有 OWNER 信息是隐含的
  • 其内容是以 ALL 为前缀的视图的子集
  • 可以在其上创建经过缩写（abbreviated）的 PUBLIC 同义词（synonym）以便于使用
041 For example, the following query returns all the objects contained in your schema:
SELECT object_name, object_type FROM USER_OBJECTS;
 例如：以下查询返回当前用户方案内的全部数据库对象：
SELECT object_name, object_type FROM USER_OBJECTS;
042

Views with the Prefix ALL

7.2.2.2 以 ALL 为前缀的视图
043 Views with the prefix ALL refer to the user's overall perspective of the database. These views return information about schema objects to which the user has access through public or explicit grants of privileges and roles, in addition to schema objects that the user owns. For example, the following query returns information about all the objects to which you have access:
SELECT owner, object_name, object_type FROM ALL_OBJECTS;
 以 ALL 为前缀的视图反映了某个用户所能看到的全部数据库内容。其中除了包含用户所拥有的方案对象之外，还包含了用户可访问的公共对象，以及通过授予（grant）权限 或角色而能够访问的方案对象。以下查询返回当前用户所能访问的全部数据库对象：
SELECT owner, object_name, object_type FROM ALL_OBJECTS;
044

Views with the Prefix DBA

7.2.2.3 以 DBA 为前缀的视图
045 Views with the prefix DBA show a global view of the entire database. Synonyms are not created for these views, because DBA views should be queried only by administrators. Therefore, to query the DBA views, administrators must prefix the view name with its owner, SYS, as in the following:
SELECT owner, object_name, object_type FROM SYS.DBA_OBJECTS;
 以 DBA 为前缀的视图能够展现整个数据库的全部内容。不应在这些视图上创建同义词，因为以 DBA 为前缀的视图只应由管理员查询。因此在查询 DBA 视图时，管理员必须在视图名之前加上其拥有者的前缀 SYS，如下所示：
SELECT owner, object_name, object_type FROM SYS.DBA_OBJECTS;
046 Oracle recommends that you implement data dictionary protection to prevent users having the ANY system privileges from using such privileges on the data dictionary. If you enable dictionary protection (O7_DICTIONARY_ACCESSIBILITY is false), then access to objects in the SYS schema (dictionary objects) is restricted to users with the SYS schema. These users are SYS and those who connect as SYSDBA.
　		 Oracle 建议管理员对数据字典的访问加以控制，防止拥有 ANY 系统权限的用户任意访问数据字典。管理员可以开启数据字典保护（dictionary protection）功能（将 O7_DICTIONARY_ACCESSIBILITY 设为 false），则只有能够访问 SYS 方案的用户才能访问数据字典对象。这样的用户只有 SYS，及以 SYSDBA 登录的用户。
　
047
See Also:

Oracle Database Administrator's Guide for detailed information on system privileges restrictions
另见：

Oracle Database Administrator's Guide 了解关于系统权限控制的详细信息。
048

The DUAL Table

7.2.2.4 DUAL 表
049 The table named DUAL is a small table in the data dictionary that Oracle and user-written programs can reference to guarantee a known result. This table has one column called DUMMY and one row containing the value X.
　		 名为 DUAL 的表是数据字典内的一个小表，Oracle 及用户程序可以使用此表来获得一个确定的值。此表只有一列名为 DUMMY；只有一行数据，值为 X。
　
050
See Also:

Oracle Database SQL Reference for more information about the DUAL table
另见：

Oracle Database SQL Reference 了解关于 DUAL 表的信息
051

Dynamic Performance Tables

7.3 动态性能表
052 Throughout its operation, Oracle maintains a set of virtual tables that record current database activity. These tables are called dynamic performance tables.
　		 在数据库运行过程中，Oracle 将维护一组虚拟表（virtual table）用于记录数据库的当前状态（current database activity）。这组表被称为动态性能表（dynamic performance table）。
　
053 Dynamic performance tables are not true tables, and they should not be accessed by most users. However, database administrators can query and create views on the tables and grant access to those views to other users. These views are sometimes called fixed views because they cannot be altered or removed by the database administrator.
　		 动态性能表并不是真正的表，大多数用户不应访问其中的信息。但是数据库管理员可以对其进行查询，或在其上创建视图并将访问权限授予其他用户。这些视图有时被称为固定视图（fixed view），因为数据库管理员不能将其修改或移除。
　
054 SYS owns the dynamic performance tables; their names all begin with V_$. Views are created on these tables, and then public synonyms are created for the views. The synonym names begin with V$. For example, the V$DATAFILE view contains information about the database's datafiles, and the V$FIXED_TABLE view contains information about all of the dynamic performance tables and views in the database.
　		 动态性能表由 SYS 用户所拥有；她们的名字都以 V_$ 开头。这些表上创建有视图，而视图上创建有公共同义词。这些公共同义词的名字都以 V$ 开头。例如，V$DATAFILE 视图包含了数据库文件的信息，V$FIXED_TABLE 视图包含了数据库中所有动态性能表及视图的信息。
　
055
See Also:

Oracle Database Reference for a complete list of the dynamic performance views' synonyms and their columns
另见：

Oracle Database Reference 了解全部动态性能视图的同义词，及其中所包含的列
056

Database Object Metadata

7.4 数据库对象元数据
057 The DBMS_METADATA package provides interfaces for extracting complete definitions of database objects. The definitions can be expressed either as XML or as SQL DDL. Two styles of interface are provided:
  • A flexible, sophisticated interface for programmatic control
  • A simplified interface for ad hoc querying
 DBMS_METADATA 包为用户提供了获取数据库对象完整定义的接口。此包可以通过 XML 或 SQL DDL 的形式提供定义信息。此包具备两类接口：
  • 用于进行编程的灵活且完善的接口
  • 用于随机查询（ad hoc querying）的简化接口
　
058
See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about DBMS_METADATA
另见：

Oracle Database PL/SQL Packages and Types Reference 了解关于 DBMS_METADATA 的信息

翻译不确定的词汇（格式：黄色背景 ） 　
　

翻译不确定的技术性词汇（格式：黄色背景 ）
　

翻译不确定的Oracle词汇（格式：黄色背景 ）
[004] Auditing
[052] current database activity
[052] dynamic performance tables

翻译不确定的句子（格式：黄色背景 ）
　

注释性的文字（格式：[绿色]）
　

未完成的链接
　

Oracle学习问题（格式：黄色背景）
1、什么的 Parsing information？
[028] Parsing information is typically kept in the caches.

2、是不是指 dynamic performance table？
[053] These views are sometimes called fixed views because they cannot be altered or removed by the database administrator.

translator: zw1840@hotmail.com