Data Dictionary数据字典oracle
Data Dictionary数据字典oracle
An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database. A data dictionary contains information such as the following:
■The definitions of every schema object in the database, including default values for columns and integrity constraint information
■The amount of space allocated for and currently used by the schema objects
■The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users (see "User Accounts" on page 17-1)
The data dictionary is a central part of data management for every Oracle database. For example, the database performs the following actions:
■Accesses the data dictionary to find information about users, schema objects, and storage structures
■Modifies the data dictionary every time that a DDL statement is issued (see "Data Definition Language (DDL) Statements" on page 7-3)
Because Oracle Database stores data dictionary data in tables, just like other data, users can query the data with SQL. For example, users can run SELECT statements to determine their privileges, which tables exist in their schema, which columns are in these tables, whether indexes are built on these columns, and so on.
Contents of the Data Dictionary
The data dictionary consists of the following types of objects:
■Base tables
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.
■Views
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. These views contain the names and description of all objects in the data dictionary. Some views are accessible to all database users, whereas others are intended for administrators only.
Typically, data dictionary views are grouped in sets. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes, as shown in Table 6–1. By querying the appropriate views, you can access only the information relevant for you.
Not all views sets have three members. For example, the data dictionary contains a DBA_LOCK view but no ALL_LOCK view.
The system-supplied DICTIONARY view contains the names and abbreviated descriptions of all data dictionary
[sql]
SQL> SELECT * FROM DICTIONARY
2 ORDER BY TABLE_NAME;
TABLE_NAME COMMENTS
------------------------------ ----------------------------------------
ALL_ALL_TABLES Description of all object and relational tables accessible to the user
ALL_APPLY Details about each apply process that dequeues from the queue visible to the current user
.
.
.
Views with the Prefix DBA_
Views with the prefix DBA_ show all relevant information in the entire database. DBA_ views are intended only for administrators.
For example, the following query shows information about all objects in the database:
[sql]
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
ORDER BY OWNER, OBJECT_NAME;
Views with the Prefix ALL_
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:
[sql]
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS
ORDER BY OWNER, OBJECT_NAME;
Because the ALL_ views obey the current set of enabled roles, query results depend on which roles are enabled, as shown in the following example:
[sql]
SQL> SET ROLE ALL;
Role set.
SQL> SELECT COUNT(*) FROM ALL_OBJECTS;
COUNT(*)
----------
68295
SQL> SET ROLE NONE;
Role set.
SQL> SELECT COUNT(*) FROM ALL_OBJECTS;
COUNT(*)
----------
53771
Application developers should be cognizant of the effect of roles when using ALL_ views in a stored procedure, where roles are not enabled by default.
Views with the Prefix USER_
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 private environment in the database, including metadata about schema objects created by the user, grants made by the user, and so on
■Display only rows pertinent to the user, returning a subset of the information in the ALL_ views
■Has columns identical to the other views, except that the column OWNER is implied
■Can have abbreviated PUBLIC synonyms for convenience
For example, the following query returns all the objects contained in your schema:
[sql]
SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS
ORDER BY OBJECT_NAME;
The DUAL Table
DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result. The dual table is useful when a value must be returned only once, for example, the current date and time. All database users have access to DUAL.
The DUAL table has one column called DUMMY and one row containing the value X. The following example queries DUAL to perform an arithmetical operation:
[sql]
SQL> SELECT ((3*4)+5)/3 FROM DUAL;
((3*4)+5)/3
-----------
5.66666667
Storage of the Data Dictionary
- 更多Oracle疑问解答:
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有,生产Oracle Rman 备份脚本的工具啊!
- 初学orcle,希望有大大帮忙解说一下详细步骤,从登录oracle到创建表的过程
- oracle语句问题:一张user表,三个字段,id,name,time,插入记录比如:张三2007,李四2008,张三2011
- 如何写一个ORACLE触发器同步两个表中的数据?
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G,在RMAN中如何按指定的时间恢复数据文件啊?
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊?