Data Dictionary and dynamic performance view <5>

Data Dictionary and dynamic performance View

Introduction: What is a data dictionary?

Data dictionary is the most important component of Oracle databases. It provides some system information about the database.

The dynamic performance view records information after the routine is started.

? Data Dictionary

The data dictionary records the system information of the database. It is a collection of read-only tables and views. The owner of the data dictionary is the Sys user.

You can only execute the SELECT statement on the data dictionary, and the maintenance and modification are automatically completed by the system.

Here we will talk about the composition of the data dictionary: the data dictionary includes the data dictionary base table and the data dictionary view. The base table stores the basic information of the database.

You can directly access the base table of the data dictionary. The data dictionary view is a view created based on the data dictionary base table. You can obtain the system by querying the data dictionary view.

Information. The data dictionary view contains three types: user_xxx, all_xxx, and dba_xxx.

? User_tables;

Displays all tables owned by the current user. It returns only all tables of the user's corresponding solution.

For example, select table_name from user_tables;

? All_tables;

This interface is used to display all tables accessible to the current user. It not only returns all tables of the current user's solution, but also returns tables of other schemes accessible to the current user:

For example, select table_name from all_tables;

? Dba_tables;

It displays the database tables owned by all solutions. However, to query this database dictionary view, the user must be a DBA role or have the select any table system permission.

Limits.

For example, when you use the System user to query the dba_tables data dictionary view, the system, sys, Scott... database table corresponding to the solution is returned.

? User name, permission, role

When a user is created, Oracle stores the user information in the data dictionary. When a user is granted permissions or roles, Oracle stores the permissions and role information.

To the data dictionary.

By querying dba_users, you can view detailed information of all database users;

By querying the data dictionary view dba_sys_privs, you can view your system permissions;

By querying the data dictionary view dba_tab_privs, you can view the object permissions that the user has;

You can query the data dictionary dba_col_privs to display the column permissions that the user has;

You can query the database dictionary view dba_role_privs to display the role of the user.

Here we will talk about the relationship between roles and permissions.

For example, to view Scott's roles, You can query dba_role_privs;

SQL> select * From dba_role_privs where grantee = 'Scott ';

// Query all the system permissions of Orale, generally DBA

Select * From system_privilege_map order by name;

// Query all the object permissions in Oracle, generally DBA

Select distinct privilege from dba_tab_privs;

// Query all roles in Oracle, generally DBA

Select * From dba_roles;

// Query the tablespace of the database

Select tablespace_name from dba_tablespaces;

Question 1: How do I query the permissions of a role?

A. System permissions contained in a role

Select * From dba_sys_privs where grantee = 'Role name'

You can also view it as follows:

Select * From role_sys_privs where role = 'Role name'

B. object permissions contained in a role

Select * From dba_tab_privs where grantee = 'Role name'

Question 2: How many roles does oracle have?

SQL> select * From dba_roles;

Question 3: How can I view the roles of a user?

Select * From dba_role_privs where grantee = 'username'

? Displays all data dictionary views accessible to the current user.

Select * From dict where comments like '% grant % ';

? Display the full name of the current database

Select * From global_name;

? Other Instructions

The data dictionary records all the system information of the Oracle database. You can obtain the following system information by querying the data dictionary:

1. object definition

2. space occupied by objects

3. Column Information

4. constraint information

...

However, this information can be obtained through PL/SQL developer tools, so I will be here.

? Dynamic Performance View

The dynamic performance view is used to record the activity information of the current routine. When ORACLE Server is started, the system creates a dynamic performance view. When ORACLE Server is stopped,

The system deletes the dynamic performance view. All the dynamic performance views of Oracle start with V _ $, and Oracle provides the same

And its synonyms start with V $. For example, the synonym for V _ $ datafile is V $ datafile, and the owner of the dynamic performance view is sys. Generally,

The dynamic performance view is queried by DBAs or privileged users.

Because this is rarely used in actual use, it flies over.

Data Dictionary and dynamic performance view <5>