Oracle Data Dictionary

Oracle uses data dictionaries to manage and display database information. data dictionaries usually store database metadata such as ldquo and rdquo ;. Data words

Oracle uses data dictionaries to manage and display database information. data dictionaries usually store database metadata such as ldquo and rdquo ;. Data words

Oracle uses data dictionaries to manage and display database information. data dictionaries usually store database metadata, which is the "Database" of databases ". Generally, a data dictionary consists of four parts: an internal RDBMS (X $) table, a data dictionary table, a dynamic performance view (V $), and a (static) data dictionary view. (Two tables and two views)

1. Internal RDBMS (X $) Table

X $ represents the core part of the Oracle database. These tables are used to track internal information of the database and maintain normal operation of the database. The X $ table is encrypted and is not described in the Oracle documentation. X $ table is the basis for running Oracle databases. It is dynamically created by Oracle Applications When the database is started. For example, we are familiar with X $ BH and X $ KSMSP. The best way to study these tables is to borrow the Oracle autotrace function (set autotrace trace explain ).

2. Data dictionary table

A Data dictionary table is used to store information about tables, indexes, constraints, and other database structures. These objects usually end with "$" (for example: TAB $, OBJ $, TS $, etc.). When creating a database, run $ ORACLE_HOME/rdbms/admin/SQL. bsq script is created. SQL. bsq is a very important file, which contains the definition and comments of the data dictionary table. You should carefully read the research.

For example, the V $ DBA_OBJECT view we often use is created based on V $ OBJ. For example, when a user creates a table, Oracle will execute a series of internal operations in the background, for example, insert data to the objV $ table and record table data to the tab $ table. Oracle parses DDL into DML operations and records these operations in the data dictionary table. Then, we can obtain the original creation statement through reverse parsing, starting from Oracle 9i, A new toolkit, DBMS_METADATA, can complete this function:

3. Static Data Dictionary View

Since X $ tables and data dictionary tables cannot be directly accessed by users, Oracle creates a static data dictionary view to provide users with access to data dictionary information, because the information is usually relatively stable, it cannot be directly modified, so it is also called the static data dictionary view. The static data dictionary view is created by the $ ORACLE_HOME/rdbms/admin/catagory. SQL script when you create a database.

The static data dictionary view is generally divided into three types based on different prefixes:

● USER _: USER-Related Object Information;

● ALL _: information of ALL objects with access permissions;

● DBA _: information of all related objects in the database, which can be accessed only when the select any table permission is granted.

Three types of views are essentially used for permission control. In Oracle databases, each user corresponds to a Schema, which is a collection of objects owned by the user. The database isolates objects of different users through Schema. Users can freely access their own objects, but related authorization is required to access other Schema objects.

4. Dynamic Performance View

The dynamic performance view records the database runtime information and statistical data. Most Dynamic Performance views are updated in real time and reflect the current status of the database. When the database is started, Oracle dynamically creates the X $ table. On this basis, Oracle creates the GV $ and V $ views, and GV $ is Global V $, each V $ corresponds to a GV $. GV $ is generated for the OPS/RAC environment. Each V $ is based on GV $, but GV $ displays the instance ID with the INST_ID column added.

(1) how to view the underlying creation statements of a view?

Oracle provides some special views to record the creation methods of other views. v $ fixed_view_definition is one of them.

(2) When querying the V $ view, the error is that the V _ $ view does not exist. Isn't the V _ $ view created based on the V $ view?

To prevent misoperations by common users, Oracle restricts access to the V $ view through the software mechanism. It does not allow normal users to directly access the V $ view, but aren't we always visiting? This is actually the V _ $ view introduced by Oracle. After the V $ view is created, Oracle creates the V _ $ view, and creates a shared synonym for the V _ $ view with the same name as the V $ view. These operations are implemented through the catalog. SQL script (which is located in the $ ORACLE_HOME/rdbms/admin/directory.

For example:

Create or replace view v _ $ fixed_table as select * from v $ fixed_table;

Create or replace public synonym v $ fixed_table for v _ $ fixed_table; -- synonym of the same name

That is to say, most users access the V $ object, instead of the view, but execute the synonym of the V _ $ view, while the V _ $ view is created based on the real V $ view.

This article permanently updates the link address: