A brief analysis of Oracle's data dictionary technology

  • 2020-06-01 11:13:03
  • OfStack

The ORACLE tutorial you are looking at is a brief analysis of the data dictionary technology of Oracle.

The data dictionary is the place where Oracle holds information about the database, and its purpose is to describe the data. For example, the creator information of a table, the creation time information, the table space information, user access rights information and so on. When users encounter difficulties in manipulating data in the database, they can access the data dictionary to see detailed information.

The data dictionary in Oracle can be static or dynamic. Static data dictionary is mainly in the user access to the data dictionary will not change, but dynamic data dictionary is dependent on the performance of the database operation, reflects the database operation of some internal information, so in the access to this kind of data dictionary is often not unchanged. 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 accessed directly, but the views in the data dictionary can be accessed. The views in a static data dictionary are divided into three categories, each consisting of three prefixes: user_*, all_*, dba_*.

user_ *

This view stores information about objects owned by the current user. (that is, all objects in this user mode)

all_ *

This attempt stores information about an object that the current user can access. (compared with user_*, all_* does not need to own the object, just have access to it.)

dba_ *

This view stores information about all objects in the database. (provided that the current user has access to these databases, 1 generally must have administrator privileges)

As can be seen from the above description, the data stored between the three must have overlaps. In fact, except for the difference in access scope (because the permission is not 1, so the access object is not 1), all of them are 1. Specifically, since the data dictionary view is owned by SYS (system user), only SYS and users with DBA system permissions can see all views in the save case. Users without DBA permissions can only see user_* and all_*. They cannot see the dba_* view without being granted the relevant SELECT permissions.

Due to the similarity of the three, user_ is taken 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, table space name, creation time, and so on. For example, execute the following command to return this information.
select * from user_users

user_tables view

It mainly describes the information of all the tables owned by the current user, including table name, table space name, cluster name and so on. This view gives you a clear idea of which tables the current user can manipulate. The execute command is: select * from user_tables

user_objects view

It mainly describes the information of all objects owned by the current user, including tables, views, stored procedures, triggers, packages, indexes, sequences, and so on. This view is more comprehensive than the user_tables view. For example, to get information about the type and state of an object named "package1", execute the following command:

Note the use of upper here, all objects in the data dictionary are capitalized, while PL/SQL are not case-sensitive, so in practice 1 must pay attention to case-matching.

user_tab_privs view

This view basically stores permission information for all tables under the current user. For example, to understand the current user's permission information for table1, execute the following command:

Knowing the current user's rights to the table makes it clear which operations can and cannot be performed.

The previous views all start with user_, in fact, all_ is exactly the same, only the information listed is the object that the current user can access rather than the object that the current user owns. The other USES of dba_, which require administrator privileges at the beginning, are completely the same and will not be discussed here.


2. Dynamic data dictionary

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

These dynamic performance views in Oracle start with v$, such as v$access. The main dynamic performance views are described below.

v $access

This view shows the database objects that are locked in the database and the session objects (session objects) that access these objects.

Run the following command:

The results are as follows :(there are many records, so this is only an excerpt)

v $session

This view lists the details of the current session. Since there are too many fields in this view to list the detail fields, you can simply type: desc v$session from the sql*plus command line for details.

v $active_instance

This view describes information about active instances under the current database. You can still observe this information using the select statement.

v $context

This view lists the property information for the current session. Such as namespaces, attribute values, and so on.

3. summary

The above is the basic content of the data dictionary of Oracle, and there are many useful views which cannot be explained here due to the space. I hope you can pay more attention to them in daily use. In short, good use of data dictionary technology, database developers can better understand the overall picture of the database, so as to database optimization, management and so on have great help.

On 1 page

Related articles: