Oracle Data Dictionary

Data dictionary

First, what is a data dictionary

A data dictionary is a collection of tables and views that Oracle stores all instance information. The Oracle process is in the SYS mode

Maintain these tables and views, that is, the owner of the data dictionary is the SYS user, and the data resides 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 owning table empty

Information, user access rights information, and more. They can be queried just like other database tables or views, but not

can make any changes.

Oracle database dictionaries are typically created when databases are created and installed, and Oracle data dictionaries are Oracle

The base of database system work, without the support of data dictionary, Oracle database system can not do any work.

Experience:

When users are having trouble working with data in the database, they can access the data dictionary to check

See the detailed information. For example, if you do not know the partitioning of partitioned tables, you can query

The User_tab_partitions view obtains the partitioning of the table.

Ii. composition of the data dictionary

Data dictionaries are divided into data dictionary tables and data dictionary views. Tables in the data dictionary are not directly accessible, but

You can access the views in the data dictionary. The Data dictionary view is divided into 2 categories: static data dictionary (static Performance view) and dynamic

Data dictionary (Dynamic Performance View).

|→→→ Data Dictionary Table

Data Dictionary →→→ data dictionary view →→→ static data dictionary: three classes, each of which is made up of three prefixes: user_*, all_*, dba_*

|→→→ Dynamic Data Dictionary

1. Data dictionary table

The table in the data dictionary is not directly accessible, the data in the table is the system data stored by the Oracle system, and

The normal table holds the user's data. To facilitate the distinction between these tables, the names of these tables are terminated with "$", these

The table belongs to the SYS user.

To make it easier for users to query the data dictionary tables, Oracle has created a user view of these data dictionaries,

This is easy to remember, and also hides the relationship between the data dictionary tables, and Oracle's scope for these objects, respectively,

Name the view dba_*, all_*, and user_*.

2. Static Data dictionary

Views in a static data dictionary are divided into three categories, each of which is made up of three prefixes: user_*, all_*, dba_*.

User_*: This view stores information about the objects owned by the current user (that is, all the pairs in that user mode

Image).

All_*: This view stores information about the objects that the current user can access, not the objects owned by the current user (

User_*, all_* does not need to have the object, only the permission to access the object.

Dba_*: This view stores information for all objects in the database (provided that the current user has access to these databases

permissions, which generally must have administrator rights).

Example

(1) User_tables: Mainly describes the information of all the tables owned by the current user, including the table name, table space name,

Cluster names, and so on. This view provides a clear understanding of what tables the current user can manipulate.

Sql>select * from User_tables;

(2) User_indexes: Query which indexes the user owns.

Sql>select index_name from User_indexes;

(3) User_views: Query What views the user has.

Sql>select view_name from User_views;

(4) User_objects: Query which database objects the user owns, including tables, views, stored procedures,

Triggers, packages, indexes, sequences, Java files, and so on.

Sql>select object_name from User_objects;

(5) User_users: Mainly describes the current user's information, mainly including the current user name, account ID, account status,

Table space name, creation time, and so on.

Sql>select * from User_users;

(6) All_objects: Query All the tables, procedures, functions and other information under a certain user.

Sql>select owner, Object_name,object_type from All_objects;

View Data dictionary: We view all views and their descriptions through the dictionary dictionary, which has only 2

Field: Table name and description.

Sql> DESC Dictionary

is the name empty? Type

---------------------------- -------------------------

TABLE_NAME VARCHAR2 (30)

COMMENTS VARCHAR2 (4000)

--Querying the objects owned by the current user

Sql> SELECT table_name from dictionary WHERE table_name like ' user% ';

Experience:

You do not need to remember all the data views when working with data dictionaries. Just follow these steps:

1. Understand the structure of the data dictionary, such as roughly divided into three categories, commonly used is user_*.

Sql>select table_name from DICTIONARY WHERE table_name like

' user% ';

2. Find the data dictionary view you need based on the query structure. For example, to know the table can be queried

"User_table"; If you want to know the view, you can query "User_view"; you want to know that the stored procedure

You can query "User_procedure", and you can query the columns in the table if you want to

"User_tab_columns".

3. Dynamic Data dictionary

In addition to the three types of views in the static data dictionary, the main one in the other dictionary views is the v$ view, which is called

Because they all start with v$ or gv$. These views are constantly updated to provide information about memory and disk

, so we can only read access to them and not modify them. Dynamic performance tables are used to record

The activity of the current database is stored only during the database run, and the actual information is taken from the memory and control files. DBAs can

To use dynamic views to monitor and tune data.

Oracle Data Dictionary