Important v $ dynamic performance views, system views, and tables in Oracle

V $ Database: database information, such as the database name and creation time.

V $ instance information, such as Instance name and start time.

V $ parameter information, select * from V $ parameter where name like '% name' ----> show parameter name (executed in sqlplus)

V $ process: Information about processes running, such as PID, spid, and process name, such as SMON and pmon. in windows, a process similar to Oracle is displayed. the name of a thread such as EXE (pmon.

V $ mystat the session Sid and other information .. after knowing the Sid, you can query the session-related information.

V $ session information related to sessions connected to the database, such as the machine name, client program name, PID (UNIX), etc.

If you use select distinct (SID) from V $ mystat; to obtain the SID,

You can obtain information about this session, such as the address of the background process serving this session (paddr) Select * from V $ session where Sid = 157

After obtaining the paddr, you can get the background process information: Select * from V $ process where ADDR = '3424e3bc'

The location information of the V $ controlfile control file can be viewed in the nomount stage, but there is no data. This view only has data after the database is mounted.

Configuration information in the V $ controlfile_record_section control file.

V $ datafile: Location Information of the data file. This view can be viewed only after the database is mounted. when the database is opened, check whether checkpoint_change # (obtained from the control file) is consistent with V $ datafile_header.

V $ datafile_header: the data file header information. It is read from the data file header. During restoration, checkpoint_change # Can be seen after an archived log application.

Select dbms_flashback.get_system_change_number from dual

V $ logfile: Location Information of the log file. This view can be viewed only after the database is mounted.

V $ instance_recovery: instance recovery time and redo log information, fast_start_mttr_target: instance recovery time limit. Oracle converts this time
The number of redo blocks S. When the number of redo blocks not written to log file in log buffer exceeds this value, an incremental checkpoint is triggered.

(This is related to the order in which the database Loads files. When the database starts up nomount, the control file is loaded according to the parameter file. After startup is mounted, the data files and log files are loaded according to the control file)

V $ log: the log information, such as the log group currently used.

V $ sga_dynamic_components SGA information of each memory block, such as the size of the Java pool, the size of the shared pool, and the data buffer (in 10 Gb, there are different buffer zones based on the size of the data block, for example, the default 2 K buffer cache has 2 k data blocks, and the default 4 K buffer cache has 4 K data blocks)

Users with sysdba and sysoper permissions in the V $ pwfile_users database.

V $ rollstat rollback segment information, USN rollback segment number, and the number of xacts active transactions. You can view the rollback segments in dba_rollback_segs.

V $ transaction refers to the transaction information of the activity, such as the corresponding rollback segment (xidusn.

V $ SQL: executed SQL statement.

V $ lock: What kind of locks are occupied by those sessions, what kind of locks are applied for, and whether the session is blocked by other sessions.

Which objects are locked by V $ locked_object.

V $ session_wait: Select * from V $ session_wait where wait_class! = 'Idle': Check the currently waiting session, such as the blocked session.

V $ session_wait_history: the historical information of session waiting. Only the latest 10 records are saved.

V $ active_session_history: historical information of the Active session (waiting or using CPU), which is collected every second. The disk is recorded every one hour, which can be viewed from dba_hist_active_sess_history. Run db_1 \ RDBMS \ admin \ ashrpt. SQL to generate a report.

V $ statistics_level: The advisor is opened and the advisor obtains statistics from those views during statistics.

V $ sga_dynamic_components: Dynamic statistics of various SGA modules.

V $ log online log information. The group is in use and the SCN is used at the beginning.

V $ archived_log refers to the archived log information from the SCN to the SCN.

V $ backup the tablespace is in hot backup mode. When alter tablespace XX begin backup is performed, the corresponding SCN is recorded.

The V $ statname database dynamically counts the information, including the information Name and ID. It is generally used together with V $ sesstat.

V $ sesstat: values of some dynamic statistics, such as user commits, opened cursors current, and redo size,

Select name, value from V $ sesstat, V $ statname
Where V $ statname. Statistic # = V $ sesstat. Statistic # And v $ statname. name like '% redo size %' and SID = 115

Related to falshback

V $ flashback_database_logfile flashback log file related information, oldest_flashback_scn/oldest_flashback_time: These two columns are used to record the data that can be restored to the earliest time point.
V $ flashback_database_log: log usage
V $ flashback_database_stat: flashback statistics.

The preceding three views only have data in alter database flashback on.
Flashback_transaction_query
V $ nls_valid_values: language, country, and encoding settings. Alter session set nls_language = American;

Parameter values that can be set by V $ parameter_valid_values

V $ SGA: SGA memory information, select sum (value)/1024/1024 from V $ SGA usually = sga_max_size or sga_target

V $ sga_dynamic_free_memory: memory not allocated by SGA.

The data block information cached in the V $ BH dB buffer.

Set Client Encoding

Nls_lang = american_america.zhs16gbk, three parts: American language, prompt information, America country affected date format, zhs16gbk encoding.

View the server code select name, value $ from props $ where name = 'nls _ characterset ';

"Views starting with DBA _ and V $ are usually used. The information at the beginning of DBA _ is usually static, and the information is in the file (but it does not mean that the information in it does not change ). The information starting with V $ is usually dynamic, from the information in the control file and memory 』

Dba_data_files (V $ datafile) data file information, which does not include information about the file corresponding to the temporary tablespace, dba_tablespaces (V $ tablespace) tablespace information, dba_tables table information, and dba_segments section information, dba_extents area information.

Dba_segments: the segments are saved to the tablespace, which occupies a lot of space.

Select * From dba_segments where segment_name = 'test'
Select * From dba_segments where tablespace_name = 'users'
Select * From dba_segments where owner = 'yorker'

Dba_users user information, such as the default tablespace. Dba_roles: roles defined in the system, such as DBA (note the difference with sysdba) and connect. There is also a view related to the user dba_profiles: profile information defined in the database. Profile refers to the number of logon attempts, password expiration time limit, and so on. How many sessions can the user have, how long it takes to disconnect the user from being idle, which can be specified at create user.

Dba_sys_privs: users or roles that grant system permissions.

Dba_tab_privs checks which user or role grants the object permissions.

Dba_role_privs: roles assigned to a user or role. The permissions granted by the role must be logged on again to take effect.

Permission transfer: the system permission is revoked, the transferred permission is not revoked, the object permission is revoked, and the transferred permission is also revoked.

Dba_constraints constraints. table-level constraints: Primary (P), foreign key (R), unique (u), check. the column-level constraint is not null (it can be considered a type of check constraint. In dba_constraints, The constraint_type and check constraints are the same, all of which are 'C '). there are also four types of constraints for not null alter table statements (alter table tb add constraints ...) is different: alter table tb modify col_name
Not null.

Dba_priv_audit_opts: the database opens the permission audit. aduIt create session;. aduIt create session whenever not successful;

Dba_obj_audit_opts: the database opens the audit objects. Audit select on TB [by session/access].

Dba_stmt_audit_opts: Audit audit table by usera

In the dba_audit_trail audit information view, some information is queried from the audit information of the table AUD $. The table AUD $ can only be viewed with sysdba permissions.

Dba_fga_audit_trail stores the fine-grained audit information created through dbms_fga.add_policy.

Dba_common_audit_trail stores the type information of the created standard and fine-grained audit.

Dba_rollback_segs information of the database rollback segment.

Dba_hist_wr_control AWR (Auto workload repository) generates control information about database load information, including retention time and Statistical frequency. Dbms_workload_repository. modify_snapshot_settings (retention => 1440, interval => 30) modify control.

Dba_hist_snapshot: historical information of the database running snapshot. It is automatically generated based on the control information of dba_hist_wr_control. It can be generated through dbms_workload_repository. create_snapshot.

You can use db_1 \ RDBMS \ admin \ awrrpt. SQL to generate database operation reports. Db_1 \ RDBMS \ admin \ awrrpt. SQL \ addmrpt. SQL can generate the corresponding diagnosis suggestion report.

Dbms_workload_repository.create_baseline you can choose to create a baseline from a snapshot to another snapshot (the database runs well during this time) as the benchmark for database operation information comparison in the future.

The Snapshot benchmark information saved by dba_hist_baseline.

Upper Limit of dba_thresholds warning. For example, how many alarms are used in the tablespace. The background process mmon monitors this information.

Dba_outstanding_alerts recent 10 warning messages. History of dba_alert_history warning.

Table:

System_privilege_map: system permission information. For example, sysdba and sysoper are commonly used as system permissions. For example, create tablespace and drop tablespace. Note that drop table is not a system permission, and drop any table is a system permission. The unlimited tablespace permission cannot be granted to the role, which means that the DBA Role user must also separately grant the unlimited tablespac permission.

For database views, the table definitions can be found in the following view:

Select * From dict
Select * From dict_columns
Select * from V $ fixed_table
Select * from V $ fixed_view_definition

Select '* Table:' | table_name, comments
From all_tab_comments
Where owner = 'sys'
And table_name = 'user _ synonyms'
Union
Select 'col: '| column_name, comments
From all_col_comments
Where owner = 'sys'
And table_name = 'user _ synonyms ';