Data Dictionary数据字典oracle(二)

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

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 d