Oracle Common views and table grooming

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

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

About this also involves two commonly used examples are as follows:

1. In Oracle, query which table a field belongs to

SQL code
Select Table_name,owner from Dba_tab_columns t where t.column_name like Upper ('%username% ');

2. Query the number of columns in Oracle for a table

SQL code
Select COUNT (*) from User_tab_columns where Table_name= Upper (' sys_operate ');

Note: These two examples all use upper this function, is because here the table name is capitalized, otherwise the result of the detection is not correct

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

Oracle's 9 most important dynamic performance views

V$session + v$session_wait (in the 10g function is integrated, make a count of 1.)
V$process
V$sql
V$sqltext
V$BH (preferring to be x$bh)
V$lock
V$latch_children
V$sysstat
V$system_event

Several sets of important performance views by component

1. System's Over view
   V$sysstat, V$system_event, V$parameter

2. The current situation of a session
   V$process, V$session, v$session_wait, V$session_event, V$sesstat

3. Case of SQL
   V$sql, V$sqlarea, V$sql_plan, V$sql_plan_statistics, V$sqltext_with_newlines

4. Latch/lock/enqueue
   V$latch, V$latch_children, V$latch_holder, V$lock, V$enqueue_stat, V$enqueue_lock

5. IO aspect of
   V$segstat, V$filestat, V$tempstat, V$datafile, V$tempfile

6. Shared Pool/library Cache
   V$librarycache, V$rowcache, X$ksmsp

7. A few advice, too.
   V$db_cache_advice, V$pga_target_advice, V$shared_pool_advice

Oracle Common views and table grooming