Brief analysis of Oracle data dictionary technology _oracle

The Oracle tutorial being looked at is: Oracle's data dictionary technology analysis.

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

Data dictionaries in Oracle have static and dynamic points. The static data dictionary is not changed when the user accesses the data dictionary, but the Dynamic Data dictionary is dependent on the performance of the database running, reflecting some intrinsic information of the database running, so it is often not invariable to access such data dictionary. The following two types of data dictionary are discussed.

1. static Data dictionary

This type of data dictionary consists primarily of tables and views, and it should be noted that the tables in the data dictionary are not directly accessible, but you can access the views in the data dictionary. The views in the static data dictionary are grouped into three categories, which are made up of three prefixes: user_*, all_*, and dba_*.

User_*

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

All_*

This attempt to store information about an object that the current user has access to. (all_* does not need to own the object, as opposed to user_*, only the permission to access the object)

Dba_*

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

As can be seen from the above description, the data stored between the three are bound to overlap, in fact, they are in addition to the scope of access (because the permissions are different, so the scope of access to objects is not the same), others are consistent. Specifically, because the data dictionary view is owned by the SYS (System user), only the SYS and users with DBA system privileges can see all views in the province. Users who do not have DBA authority can only see user_* and all_* view. They cannot see the dba_* view if they are not granted the relevant SELECT permission.

Because the three are similar, here are some common static views, using User_ as an example:

User_users View

Mainly describes the current user's information, including the current user name, account ID, account status, table space name, creation time, and so on. For example, you can return this information by executing the following command.
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 makes clear what tables the current user can manipulate. Execute command as: SELECT * from User_tables

User_objects View

Describes information about 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, you need to obtain information about an object type named "Package1" and its status, and you can execute the following command:


Attention should be paid to the use of upper, all the objects in the data dictionary are uppercase, while the pl/sql is not case sensitive, so in the actual operation must pay attention to case matching.

User_tab_privs View

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


Knowing the current user's permissions on the table makes it clear what actions can be performed and which actions cannot be performed.

The previous view starts with the User_, but the All_ begins with exactly the same, except that the listed information is an object that the current user can access rather than the current user. The other uses are exactly the same for Dba_, which begins with the need for administrator privileges, and is no longer discussed here.

[NextPage]

2. Dynamic Data dictionary

Oracle contains a number of 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 information about memory and disk operations, so we can only read-only access to them and not modify them.

These dynamic performance views in Oracle are views that start with v$, such as v$access. The following is an introduction to several key dynamic performance views.

V$access

This view displays the database objects locked in the database and the session objects that access those objects.

Run the following command:


The results are as follows: (as a result of more records, so here is an excerpt of some records)


V$session

This view lists the details of the current session. Because the view field is larger, there is no detail field here, for more information, you can type directly under the Sql*plus command line: Desc v$session.

V$active_instance

This view mainly describes the information for an active instance under the current database. You can still use the SELECT statement to observe this information.

V$context

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

3. Summary

This is the basic content of Oracle's data dictionary, there are many useful views because of the reasons for space can not be explained here, I hope that everyone in peacetime use more attention. In short, the use of data dictionary technology, so that the database developers can better understand the full picture of the database, so that the database optimization, management and so have great help.

Previous page