Data Dictionary)

As the name implies, a dictionary is like a dictionary used in reality. When a word or word does not understand it, it is used to look up the dictionary. the dictionary contains the language information we use. data is also loaded with a lot of data information like a dictionary.

Store database structure information in the dictionary. For example, we often use information about database objects such as table, view, index, triger, sequence, and synonym.

The data dictionary is a table and view, which is similar to the table and view created by the user. It is only created by the system, so it is saved under the Sys user.

All data dictionary tables end with $..

For example, table tab $ stores information about all tables created by users, and table user $ stores username and password information.Supplement:For the sake of security, the information in this table is naturally very important.

Although sys users with the highest permissions can view this table, the username and password in the table are encrypted with MD5, so only the ciphertext is displayed. therefore, in theory, the user name and password will never be seen in Oracle. however

We only need to change the password. You can reset the password without knowing the previous password.

However, we basically do not directly access the table, but access the view generated based on the table.

Three dba_xxx, all_xxx, and user_xxx views are available.

The view prefixed with user _ contains information about the objects owned by the current user.

The view prefixed with all _ contains information about the objects that the current user can access. Some objects belong to other users, but you have access permissions.

The view prefixed with DBA _ contains information about all objects in the database.

It is easy to see the relationship between the three. The user view is a subset of the all view, and the all view is a subset of the DBA view.

In the following illustration, I only use the user view as an example. Because all views in the user view are available in the DBA view, you only need to change the view prefix name.

 

Common Views in data dictionary:

1. Table-related views:

User_tables has information about all tables of the user.

User_all_tables contains a little more information than user_tables. It contains the information of the object table, that is, a table with a custom object type.

Table column information of the user_tab_columns user. For example, to find information about all columns in the Arwen table, select * From user_tab_columns where table_name = 'arwen ';

2. View-related views:

User_views

3. Index-related views

User_indexes (synonym: Ind) User's index information.

Table column information corresponding to the index of user_ind_columns

4. View of constraints

User_constraints object constraint information: for example, to find the constraints in the Arwen table, select * From user_constraints where table_name = 'arwen ';

Note that constraint type is abbreviated to N, U, P, f, and C, respectively, corresponding to not null, unique, primary key, foreign key, check

Table column information corresponding to the constraints of the user_cons_columns user

5. Synonym View:

User_synonym (Synonym SYN)

6. Sequence View:

User_sequence (Synonym SEQ)

7. Directory View:

Dba_directories)

In Oracle, if you want to access a directory in the operating system, you must create a directory object based on the directory and then use the directory object.

For example, create directory temp_dir as c: \ Temp;

8. Trigger View:

User_triggers

 

9. view of all objects

User_catalog information of all tables, views, synonyms, and sequences of the current user

User_tables information of all tables of the current user

User_objects has information about all objects of the user. user_catalog is equivalent to its subset.

 

8. Permission-related views

User_sys_privs system permission information of the current user: for example, permission for creating a table and permission for creating a new user

User_tab_privs object permission information of the current user: read and write permissions on the objects of other users

In addition to granting other users the permission to access a table, user_col_privs can also further grant the permission to access a column in the table. this view includes permissions granted to other users to access a column and permissions granted to others to access a column.

User_role_privs current user's role permission information each role has some corresponding permissions. Sometimes, to grant simple permissions, create a role and then add the corresponding permissions, and then assign the role to some users.

A simple method for using data dictionaries

The view name mentioned above is only a small part, which is hard to remember. however, you don't have to worry about it. Oracle stores the information of all views of the data dictionary in a View called dictionary. it also has a synonym dict.

It is equivalent to a dictionary. it has only two columns: table_name, comments. note that table_name should not be the table name as the name implies before. In fact, it is the name of all views, that is, the view prefixed with user _, all _, DBA. the real table is not included. comments is the description, indicating what the view is.

If you want to search for table-related information, but do not know which data dictionaries have table information. you can query select * From dictionary where table_name like '% table %' In this way. In this way, there are still a lot of search results, so you actually add a qualified prefix user _

Select * From dictionary where table_name like '% USER _ % table % ';

You will see user_tables. Then you can find user_tables to learn about table-related information.

Therefore, you only need to remember the dict view and add some conditions in the where statement for fuzzy query.