Vi. Oracle database management and data dictionary and Dynamic View

 

Database Management

DBA: Database Administrator

Main responsibilities:

1. Install and upgrade the Oracle database
2. Database creation, tablespace, table, view, index...
3. Develop and implement backup and recovery plans
4. Database permission management,OptimizationAnd troubleshooting and recovery
5. For senior DBAs, you must be able to participate in project development and write complex SQL statements, stored procedures, triggers, rules, constraints, and packages.

 

Database Management users are mainly sys and system, similarSys is chairman of the Board,System is the general managerThe main differences are:

1. The most important difference is that the importance of stored data is different.

SYS: The base tables and views of all ORACLE data dictionaries are stored in SYS users. These base tables and views are crucial for Oracle operation and are maintained by the database, no user can change it manually. Sys users have the role or permission of DBA, sysdba, and sysoper, and are the top users with Oracle permissions.

System: used to store internal data once, such as the Management Information of some features or tools of oracle. System users have dBA and sysdba permissions.

1. Second difference

Sys users must Log On As sysdba or as sysoper. You cannot log on to the database as normal.

If the system is logged on normally, it is actually a common DBA user. However, if you log on with sysdba, the result is actually logged on as the Sys user, which can be seen from the logon information.

 

Users with DBA permissions:

A dba user is a database user with a DBA role. a privileged user can start an instance, close an instance, and perform other operations. a dba user can perform various management tasks only after starting the database.

 

Data Dictionary and Dynamic View

User_tables

Displays all tables owned by the current user. Returns only all tables of the corresponding solution.

Example: Select table_name from user_table;

All_tables

Displays all tables accessible to the current user. Return not only all the tables of the current user's solution, but also the tables of the solution that the current user can access.

Example: Select table_name from all_table;

Dba_tables

Displays all tables owned by all solutions. However, to query such database views, the user must be a DBA role or have the select any table permission.

For example, when the user system queries the data dictionary view dba_table, the system, sys, Scott... All tables corresponding to the scheme.

 

Username, permission, and role

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

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

You can query the data dictionary view dba_sys_privs to view the system permissions that the user has;

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 view the column permissions you have;

You can query the database dictionary view dba_role_privs to view the roles of users.

 

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

Select * From dba_role_privs where grantee = 'Scott ';

 

Relationship between roles and permissions:

A role is composed of a series of permissions.

If a role is assigned to a user, the user has the permissions contained in the role.

There are two types of permissions: Object permission and system permission.

 

For example, Scott has the connect and resource roles.

 

How can I query permissions contained in a role?

1. System permissions contained by a role
   Select * From dba_sys_privs where grantee = 'dba '; -- DBA is the role name and can be changed to connect.
   

   Or: Select * From role_sys_privs where role = 'dba ';

2. System permissions contained by a role
   

   Select * From dba_tab_privs where grantee = 'connection ';

 

How many roles does oracle have?

Select * From dba_roles; -- a total of 25

 

How can I view the roles of a user?

Select * From dba_role_privs where grantee = 'username ';

 

-- Query allSystem Permissions, Generally DBA Select * From system_privilege_map order by name; -- Query allObject permission, Generally DBA Select disinct privilege from dba_tab_privs; -- Query all roles in Oracle, generally DBA Select * From dba_roles; -- Query the tablespace of a Database Select tablespace_name from dba_tablespaces;

 

 

Display the data dictionary view of the current user

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

Display the full name of the current database

Select * From global_name;

 

The database dictionary view mainly queries Oracle system information and database management information.

 

Dynamic Performance view: less practical applications.

It is mainly used to record the activity information of the current routine and is real-time.