[Reprint] Summary of Oracle system view and table Daquan

Summarize Oracle system views and table Daquan:
Dba_ opening .....
Dba_users Database user Information
Dba_segments Table Segment Information
Dba_extents Data Area Information
Dba_objects Database object Information
Dba_tablespaces database table Space information
Dba_data_files Data File setup Information
Dba_temp_files Temporary data file information
Dba_rollback_segs Rollback Segment Information
Dba_ts_quotas User table Space quota information
Dba_free_space Database Free Space information
Dba_profiles Database User Resource limit information
Dba_sys_privs User's system permissions information
Dba_tab_privs the object permission information that the user has
DBA_COL_PRIVS user has Column object permission information
Dba_role_privs The role information that the user has
Dba_audit_trail Audit trail Record information
Dba_stmt_audit_opts Audit Setup Information
Dba_audit_object Object Audit Results information
Dba_audit_session Session Audit Results Information
Dba_indexes index information for user mode
User_ Opening
User_objects User Object Information
User_source all resource object information for a database user
User_segments User's Table segment information
User_tables User's Table object information
User_tab_columns User's Table column information
User_constraints object constraint information for a user
User_sys_privs System permissions information for the current user
User_tab_privs Object permission information for the current user
User_col_privs table column permission information for the current user
User_role_privs role permission information for the current user
User_indexes User's index information
Table column information for the User_ind_columns user's index
Table column information for the USER_CONS_COLUMNS user's constraints
User_clusters all cluster information for the user
User_clu_columns The content information contained in the user's cluster
User_cluster_hash_expressions Hash Cluster information
v$ Opening
V$database Database Information
V$datafile Data File Information
V$controlfile Control File Information
V$logfile Redo Log Information
V$instance DB Instance Information
V$log Log Group Information
V$loghist Log History information
V$SGA Database SGA Information
V$parameter Initialization of parameter information
V$process Database Server process information
V$bgprocess Database Background Process information
V$controlfile_record_section the information of each part of the control file
V$thread Thread Information
V$datafile_header information in the header of the data file
V$archived_log Archive Log Information
V$archive_dest setting information for archived logs
v$logmnr_contents DML DDL result information for archived log analysis
V$logmnr_dictionary dictionary file information for log analysis
V$logmnr_logs log list information for log analysis
V$tablespace Table Space Information
V$tempfile Temporary file information
I/O statistics for v$filestat data files
V$undostat Undo Data Information
V$rollname Online rollback segment Information
V$session Session Information
V$transaction Transaction information
V$rollstat Rollback Segment Statistics
V$pwfile_users Privileged User Information
V$sqlarea Resources and related information that have been accessed by SQL statements that are currently queried
V$sql basic information about the same as V$sqlarea
V$sysstat Database System State information
All_ Opening
All_users information for all users of the database
All_objects information about all objects in the database
All_def_audit_opts All default Audit setup Information
All_tables all Table Object information
All_indexes information for all database object indexes
Session_ Opening
Role information for Session_roles sessions
Permissions information for Session_privs sessions
Index_ Opening
Index_stats setting and storing information for indexes
Pseudo table
Dual system pseudo-list information
Oracle Common Dynamic View Description:
2.1.1v$lock
Given the lock information, such as the Type field, the user type locks has 3 kinds: tm,tx,ul,system Type locks There are many, common are: Mr,rt,xr,ts and so on. We only care about TM,TX locks.
When TM locks, the Id1 field represents object_id; when the TX lock, Trunc (Id1/power (2,16)) represents the rollback segment number.
2.1.2v$sqlarea
The SQL stored in the shared pool and some related information, such as the cumulative number of executions (executions), logical reads (buffer_gets), physical reads (disk_reads), and other statistical information.
You can locate a SQL based on address and hash_value. Sql_text Word Gencun The first 1000 characters of this SQL. Finding the entire SQL also needs to go to V$sqltext or v$sqltext_with_newlines.
2.1.3v$session
V$session is the basic information view that is used to find the user SID or Saddr. However, it also has some columns that change dynamically and can be used to check the user.
2.1.4v$sesstat
The session ID can be used to get the statistics
2.1.6v$process
The V$process view contains all the process information that the current system Oracle runs. is often used to establish a connection between the operating system process ID of an Oracle or service process and the database session.
2.1.7v$transaction
Depending on the session ID, you can find the transaction information that is executing at the current session:
SELECT * from V$transaction where addr in (select Taddr from v$session where Sid=&sid);
2.1.8v$sort_usage
Temp table space usage, when the temp table space becomes huge, according to SESSION_ADDR can get session ID, according to SQLADDR and Sqlhash can get the SQL that is executing:
2.1.9v$sysstat
Statistical information for all instance
2.1.10 V$sqltext View
The V$sqltext view includes the full text of the SQL statement in the shared pool, and an SQL statement may be divided into multiple blocks to be saved in multiple records.
2.1.11 v$session_wait View
This is a critical view of looking for performance bottlenecks. It provides any case where the session is currently waiting in the database (if the session is currently not doing anything, it shows its last wait event). When there is a performance problem with the system, this view can be used as a starting point to indicate the direction in which the problem is explored.
In v$session_wait, each SESSION connected to the instance corresponds to a record.
2.1.12 Overview
and permissions, the role-related views are probably the following:
Dba_sys_privs: Querying system permissions owned by a user
User_sys_privs: System permissions owned by the current user
Session_privs: All permissions owned by the current user
Role_sys_privs: System permissions owned by a role
Note: To query this view with SYS user login, otherwise return NULL.
Role_role_privs: Role assigned to the current role
Session_roles: The role that the current user is activating
User_role_privs: The role that the current user is granted
There is also a view of access rights for tables:
Table_privileges
All_tab_privs
Role_tab_privs: Permissions on related tables that a role is given
2.2.4Examples
1. Querying permissions owned by the current user
Select * from Session_privs;
2. Query the system permissions that a user has been given.
There are many ways to
Select * from User_sys_privs;
Or: SELECT * from Dba_sys_privs where grantee= ' XXX '
(requires current user to have DBA role)
3. Query the role that the current user is granted:
1). Select * from Session_roles ORDER by ROLE
Description: This query returns all roles that the current user has been granted, including nested authorization roles. For example, a DBA role is granted to a user, and the roles that the DBA role has been granted (for example, Exp_full_database and imp_full_database) are also queried.
2). Select * from User_role_privs
4. Querying the system permissions assigned to a role
Select Privilege from Role_sys_privs where role=&role
5. Query the role that the current role is granted
Select granted_role from Role_role_privs where role=&role
Description: Plustrace This role is used to execute SQL AUTO trace, which can be generated by executing $oracle_home/sqlplus/admin/plustrce.sql
This article is for the headline author and does not represent today's headline position.

[Reprint] Summary of Oracle system view and table Daquan