Oracle Data Dictionary Overview

 

Oracle Data Dictionary Overview

 

Data dictionary is an important component of Oracle databases. It is a set of read-only tables used to record database information.

The data dictionary contains:

Definition of all schema objects in the database (including tables, views, indexes, clusters, synonyms, sequences, processes, functions, packages, triggers, etc)

How much space the database allocates to a solution object and how much space the object currently uses

Column Default Value

Integrity constraints

Database username

Each user is granted permissions (privilege) and roles (role)

Monitoring (Audit) information, such as which user accesses or updates a scheme object

Other summary information in the database

 

A data dictionary consists of the following two structures:

Base table:Stores related database information. Only oracle can read and write these tables. Users seldom access them directly because these tables are normalized.

User Access View:Summarizes and displays the data stored in the data dictionary base table. These views use join and where clauses to simplify the data in the base table and decode it as understandable information.

 

 

Data Dictionary has three main purposes:

Oracle uses data dictionaries to obtain information about users, solution objects, and storage structures.

After a DDL statement is executed in the database, Oracle modifies the data dictionary.

All oracle users can use the data dictionary to obtain information about the database.

 

 

Synonym for data dictionary:Oracle has created public synonym for many data dictionary views so that users can easily access them.

 

Data Dictionary cache:Since Oracle needs to constantly access the data dictionary to verify user permissions and the state of the solution object during database operation, a large amount of data dictionary information will be cached in the SGA data dictionary cache (Dictionary cache).

 

 

--------------------------------------------------------------------------------

 

Prefix coverage

 

--------------------------------------------------------------------------------

 

User user view (Objects in the user schema)

 

All Extended user view (accessible objects)

 

DBA view (Objects in all user Schemes)

 

 

DBA data dictionary:The view prefixed with DBA can display all the contents of the entire database. Synonyms should not be created on these views, because views prefixed with DBA should only be queried by the Administrator. Therefore, when querying the DBA view,The Administrator must add the prefix of the view owner sys before the view name.

Dual table:A table named Dual is a small table in the data dictionary. Oracle and user programs can use this table to obtain a definite value. This table has only one column named dummy; only one row of data with the value of X.

 

 

During database operation, Oracle maintains a set of virtual tables to record the current status of the database. These tables are called dynamic performance tables ).

Dynamic Performance tables are not real tables, and most users should not access the information. However, the database administrator can query it, create a view on it, and grant access permissions to other users. These views are sometimes called fixed views because the database administrator cannot modify or remove them.

Dynamic Performance tables are owned by sys users. Their names all start with V _ $. These tables create a view, and the view creates a public synonym.The names of these common synonyms start with V $.