Oracle 11g R2 Data dictionary

A data dictionary is a collection of tables and views where Oracle stores critical information. The Oracle process maintains these tables and views in the SYS mode, that is, the owner of the data dictionary is the SYS user, the data is stored in the system table space, and the data dictionary describes how the actual data is organized, such as the creator information of a table, the creation of time information, the table space information to which it belongs, User access rights, and so on. The data dictionary can be queried in the same way as other databases and tables, but cannot be modified.

Oracle data dictionaries are typically created when the database is created and installed. Oracle data dictionaries are fundamental to the work of Oracle database systems. Without the support of a data dictionary, the Oracle database system cannot do any work.

Data dictionary composition: The data dictionary is divided into the data dictionary table and the data dictionary view. Tables in a data dictionary cannot be accessed directly, but they can access the views in the data dictionary. The Data dictionary view is divided into two categories: static data dictionary view and Dynamic Data dictionary view. The Data dictionary view is divided into two categories: static data dictionary view and Dynamic Data dictionary view;

Data dictionary tables: data in the Data dictionary table is the system data stored by the Oracle system, and the ordinary table is the user's data, in order to facilitate the difference between these tables, the names of these tables are "$" end, these tables belong to the SYS user.

To make it easier for users to query the data dictionary tables, Oracle sets up a user view of the data dictionaries, which is easy to remember and hides the relationships between the data dictionary tables.

static Data dictionary View

Views in a static data dictionary are divided into 3 categories: they have 3 prefixes: USER, all, DBA

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

All: The view stores information about the objects that are accessible to the current user (all do not need to have the object, as compared to user, and only have permission to access the object);

DBA: This view stores information about all objects in the database (provided that the current user has access rights and generally must have administrator privileges)

Enumerate Common data dictionary views

Usertables: Mainly describes all the tables currently owned by the user information, mainly including table name, table space and other information, using the Scott user to connect to the DB instance, to view all the tables under the Scott user information.

See which indexes Scott users have

View database objects owned by Scott users
View all the tables, procedures, functions, and other information that Scott users can access
Query results

View Data dictionary: If you need to know more about each view, you can view all views and other descriptions through the dictionary command, which has only two fields, table names and descriptions

Dynamic Data dictionary View and usage

In addition to the 3 class views in the static data dictionary, the other dictionary views are primarily v$ views, which are named because the views are constantly updated to reflect the health of the current instance and database. Dynamic performance tables are used to record the activity of the current database, only during the database run, and the actual information is taken from the memory and control files. DBAs can use dynamic views to monitor and maintain databases.

Illustrate the use of dynamic Data dictionaries

As can be seen from the results, the current database is in use of the log group is group 3, the database is running in non-archive mode, the log group has a log member, the storage directory is/u01/app/oracle/oradata/orcl/, the log file name is Redo03.log

If you have any questions about this article, please add the following discussion

Oracle 11g R2 Data dictionary