Analysis of Oracle Data Dictionary Technology

Analysis of Oracle Data Dictionary Technology

Analysis of Oracle Data Dictionary Technology

The ORACLE tutorial is a brief analysis of Oracle Data dictionary technology.

A data dictionary is a place where Oracle stores database information. It is used to describe data. For example, the Creator information, creation time information, tablespace information, and user access permission information of a table. You can access the data dictionary to view detailed information when you encounter difficulties in performing operations on the data in the database.

Data dictionaries in Oracle are static and dynamic. The static data dictionary does not change when users access the data dictionary. However, the dynamic data dictionary depends on the performance of the database and reflects some internal information about the database operation, therefore, accessing such data dictionaries is often not static. The following two types of data dictionaries are discussed respectively.

1. Static Data Dictionary

This type of data dictionary is mainly composed of tables and views. It should be noted that the tables in the data dictionary cannot be directly accessed, but can access the views in the data dictionary. The views in the static data dictionary are divided into three types, which have three prefixes: user _ *, all _ *, and dba _*.

User _*

This view stores information about the objects owned by the current user. (All objects in this user mode)

All _*

This attempt stores the information of objects accessible to the current user. (Compared with user _ *, all _ * does not need to own this object. You only need to have the permission to access this object)

Dba _*

This view stores information about all objects in the database. (The premise is that the current user has the permission to access these databases. Generally, the user must have the Administrator permission)

From the above description, we can see that the data stored between the three must overlap. In fact, they have different access scopes (because of different permissions, the access object range is different ), others are consistent. Specifically, because the data dictionary view is owned by SYS (system user), only SYS and the user with DBA system permissions can view all views while saving the need. Users without DBA permissions can only view user _ * And all. If the SELECT permission is not granted to the user, the user cannot see the dba _ * view.

Because the three are similar, the following uses user _ as an example to introduce several common static views:

User_users View

It mainly describes the information of the current user, including the current user name, account id, account status, tablespace name, and creation time. For example, you can run the following command to return the information.
Select * from user_users

User_tables View

This section describes information about all tables owned by the current user, including the table name, tablespace name, and cluster name. Through this view, you can clearly understand what tables can be operated by the current user. Run the following command: select * from user_tables.

User_objects View

This section describes information about all objects owned by the current user, including tables, views, stored procedures, triggers, packages, indexes, and sequences. This view is more comprehensive than the user_tables view. For example, to obtain the object type and status information named "package1", run the following command:


Note the usage of upper. All objects in the data dictionary are in uppercase format, while PL/SQL is not case sensitive. Therefore, you must pay attention to case-sensitive matching in actual operations.

User_tab_privs View

This view stores the permissions of the current user on all tables. For example, to understand the permissions of the current user on table 1, you can run the following command:


After understanding the permissions of the current user on the table, you can clearly know which operations can be performed and which operations cannot be performed.

The preceding views start with user _. In fact, all _ starts with the same, but the information listed is the objects accessible to the current user rather than the objects owned by the current user. Administrator permissions are required for dba _. Other operations are the same, so we will not repeat them here.

[NextPage]

2. Dynamic Data Dictionary

Oracle contains some potential tables and views maintained by system administrators, such as SYS. Since these tables and views are constantly updated when the database is running, therefore, they are called dynamic data dictionaries (or dynamic performance views ). These views provide information about the memory and disk operations, so we can only read-only access them, but not modify them.

In Oracle, these dynamic performance views all start with v $, such as v $ access. The following describes several major dynamic performance views.

V $ access

This view displays the database objects locked in the database and the session objects accessing these objects (session objects ).

Run the following command:


The result is as follows: (because there are many records, some records are extracted here)


V $ session

This view lists the details of the current session. Because there are many fields in this view, detailed fields are not listed here. For details, you can directly enter desc v $ session under the SQL * plus command line.

V $ active_instance

This view mainly describes the information of active instances in the current database. You can still use the select statement to observe this information.

V $ context

This view lists the attributes of the current session. For example, namespace and attribute value.

3. Summary

The above is the basic content of Oracle Data Dictionary. There are still many useful views which cannot be explained one by one for space reasons. I hope you will pay more attention to them during normal usage. In short, the use of data dictionary technology allows database developers to better understand the full picture of the database, which is of great help to database optimization and management.

Previous Page