Data Dictionary
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> 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:
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:
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> 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:
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> SELECT ((3*4)+5)/3 FROM DUAL;
((3*4)+5)/3
-----------
5.66666667
Storage of the Data Dictionary
The data dictionary base tables are the first objects created in any Oracle database. All data dictionary tables and views for a database are stored in the SYSTEM tablespace. Because
the SYSTEM tablespace is always online when the database is open, the data dictionary is always available when the database is open.
How Oracle Database Uses the Data Dictionary
The Oracle Database user SYS owns all base tables and user-accessible views of the data dictionary. Data in the base tables of the data dictionary is necessary for Oracle Database to
function. Therefore, only Oracle Database should write or change data dictionary information. No Oracle Database user should ever alter 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.
Caution:
Altering or manipulating the data in data dictionary tables can permanently and detrimentally affect database operation.
During database operation, Oracle Database reads the data dictionary to ascertain that schema objects exist and that users
have proper access to them. Oracle Database also updates the data dictionary continuously to reflect changes in database structures, auditing, grants, and data.
For example, if user hr creates a table named interns, then new rows are added to the data dictionary that reflect the new table, columns, segment, extents, and the privileges that
hr has on the table. This new information is visible the next time the dictionary views are queried.
Public Synonyms for Data Dictionary Views
Oracle Database 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.
Cache the Data Dictionary for Fast Access
Much of the data dictionary information is in the data dictionary cache because the database constantly requires the information to validate user access and verify the state of schema
objects. Parsing information is typically kept in the caches.
The COMMENTS columns describing the tables and their columns are not cached in the dictionary cache, but may be cached in the database buffer cache.
Other Programs and the Data Dictionary
Data DictionaryOther Oracle Database 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. Synonyms are less likely to change between releases.
分享到:
相关推荐
Data dictionary for oracle(数据字典)是一款可查看ORACLE数据字典的工具。
查看数据库数据字典,包括表、视图、索引、存储过程等内容; 支持SQL Server和Oracle两大商业数据库; 能将数据字典导出为Html、XML、Word等文件格式; 免费注册、免费使用; 没有注册,会定时弹出About窗口。
Kaggle-GiveMeSomeCredit
data dictionary in documentum
ICESAT/GLAS GLAH01 Product 数据字典
Commands for Querying Oracle Data Dictionary Tables
ProblemB_data dictionary.md
数据字典(Data dictionary)是一种用户可以访问的记录数据库和应用程序元数据的目录。主动数据字典是指在对数据库或应用程序结构进行修改时,其内容可以由DBMS自动更新的数据字典。被动数据字典是指修改时必须手工...
SAP ABAP Training Course (Introduction + Data Dictionary)
ERP系统信息化资料:SAP专业文档资料BC-DataDictionary.ppt
teradata Data Dictionary官方文档
satish @ satish-Lenovo-V570:〜/ Downloads $ git clone 克隆到“ DataDictionary” ...远程:计数对象:120,完成。 远程:压缩对象:100%(84/84),已完成。 远程:总计120(增量24),已重用100(增量6),...
这个数据集是美国某一周出租车运营得来的,有运乘时间、经纬度、乘客人数等等。主要用于配合我的文章——R语言数据集常用操作汇总用来举例说明的,大家需要可以免费下载。文件比较大,请耐心哈。
K3Cloud数据字典K3Cloud Data Dictionary,非常全面实用,是从事金蝶开发的技术人员不可缺少的金蝶CLOUD工具
Architecture Architecture ArchitectureArchitecture Architecture ArchitectureArchitecture Architecture ArchitectureArchitecture Architecture ArchitectureArchitecture ...Architecture
teradat database NCR
ICESAT/GLAS GLAH05 Product 数据字典
ICESAT/GLAS GLAH14 Product 数据字典