Shulou(Shulou.com)06/01 Report--

A data dictionary is a collection of tables and views in which oracle stores key information. The oracle process maintains these tables and views in the sys schema, that is, the data dictionary is owned by the sys user, and the data is stored in the system table space. The data dictionary describes how the actual data is organized, such as the creator information of a table, the creation time information, the table space information to which it belongs, the user access rights, and so on. The data dictionary can be queried in the same way as other databases and tables or attempts, but no modifications can be made.

Oracle data dictionaries are usually created when the database is created and installed. Oracle data dictionary is the foundation of oracle database system. Without the support of the data dictionary, the oracle database system cannot do any work.

The composition of data dictionary: data dictionary is divided into data dictionary table and data dictionary view. The tables in the data dictionary cannot be accessed directly, but the views in the data dictionary can be accessed. Data dictionary views are divided into two categories: static data dictionary view and dynamic data dictionary view. Data dictionary views are divided into two categories: static data dictionary view and dynamic data dictionary view.

Data dictionary table: the data in the data dictionary table is the system data stored in the oracle system, while the ordinary table stores the user's data. In order to easily distinguish these tables, the names of these tables all end with "$". These tables belong to SYS users.

In order to facilitate users to query data dictionary tables, oracle establishes user views for these data dictionaries respectively, which is not only easy to remember, but also hides the relationship between data dictionary tables.

Static data dictionary view

Views in static data dictionaries are divided into three categories: they are made up of three prefixes: USER, ALL, and DBA.

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

ALL: this view stores information about objects that can be accessed by the current user (compared with USER, ALL does not need to own this object, only needs to have access to it)

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

List commonly used data dictionary views

Usertables: mainly describes the information of all the tables owned by the current user, including table name, table space and other information. Use SCOTT users to connect to the database instance and view the information of all tables under the SCOTT user.

Check 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

Screenshot of query result

View the 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 name and description

Dynamic data dictionary view and its use

Except for three types of views in the static data dictionary, the other dictionary views are mainly V$ views, so named because these views are constantly updated to reflect the health of the current instance and database. The dynamic performance table is used to record the activity of the current database, which only exists during the operation of the database, and the actual information is taken from memory and control files. DBA can use dynamic views to monitor and maintain databases.

An example is given to illustrate the use of dynamic data dictionary.

As can be seen from the results, the log group being used in the current database is GROUP 3, and the database is running in non-archived mode. The log group has a log member, the storage directory is / u01ax, and the log file is called redo03.log.

If you have any questions about this article, please add Wechat below to discuss it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.