Some important v$ dynamic performance views, system views, and tables in Oracle

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

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

V$parameter parameter information, select * from V$parameter where name like '%name '----> Show parameter name (performed in Sqlplus)

V$process runs the process of information, such as Pid,spid, and the name of the process, such as Smon,pmon in the UNIX process name, in Windows will see similar ORACLE.EXE (Pmon) such as the name of the thread.

V$mystat information such as SID number for this session: Knowing the SID can be done by querying the session related information.

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

such as through select DISTINCT (SID) from V$mystat;

You can get information about this session, such as the address of the background process serving the session (PADDR) SELECT * from V$session where sid=157

Get paddr After you can get information about the background process: SELECT * from v$process where addr = ' 3424E3BC '

The location information of the V$controlfile control file can be viewed in the nomount phase, but there is no data. The database has data in this view after mount.

V$controlfile_record_section the configuration information in the control file.

V$datafile the location information of the data file, the database can be viewed after mount. The database is opened to check if the checkpoint_change# (from the control file) is consistent with the v$datafile_header.

V$datafile_header data file header information. Is read from the data file header, when the recovery is done every time an archive log application, checkpoint_change# can see the changes.

Select Dbms_flashback.get_system_change_number from dual

V$logfile the location information of the log file, the database can be viewed after mount.

V$instance_recovery: Information about instance recovery and redo logs, Fast_start_mttr_target: time limit for instance recovery, Oracle translates this time into
Redo blocks number, when the number of redo blocks that are not written to log file in log buffer exceeds this value, an incremental checkpoint is triggered.

(This is related to the database load file order, database startup Nomount when the control file is loaded according to the parameter file, after startup mount, the data files and log files are loaded according to the control file)

V$log: Log information, such as the one that is currently used for the log group.

V$sga_dynamic_components SGA information about the various memory blocks, such as the size of the Java pool, the size of the shared pool, and the data buffer (in 10G depending on the size of the data block have different buffers, such as default 2K buffer Cache Drop Data block is 2k, DEFAULT 4K buffer cache Drop Data block is 4k)

V$pwfile_users a user with Sysdba,sysoper permissions in the database.

V$rollstat the fallback segment information, the number of the USN fallback segment, and the number of transactions that xacts the activity. Fallback segments can be viewed from dba_rollback_segs.

V$transaction the active transaction information, such as the corresponding fallback segment (XIDUSN).

V$SQL: Executed SQL statement.

V$lock: What kind of lock does the session occupy, and what kind of lock is applied, and whether the session is blocking the other session.

V$locked_object which objects are locked.

V$session_wait View Session Wait information: SELECT * from v$session_wait where wait_class! = ' Idle ' to see the session that is still waiting, such as a blocked session.

V$session_wait_history:session wait for historical information, save only the most recent 10 records from the current.

V$active_session_history: Historical information of the event session (waiting or using CPU), which is counted once per second. The disk is recorded every hour and can be seen from dba_hist_active_sess_history. Execute db_1\rdbms\admin\ashrpt.sql to generate a report.

V$statistics_level: Those advisor was opened as well as the advisor do statistics time from those view take statistics.

V$SGA_DYNAMIC_COMPONENTS:SGA dynamic statistics for each module.

V$log Online log information, that group is in use, start using the SCN.

V$archived_log archived log information from that SCN to the end of the SCN archive to that file.

V$backup The table space is in hot backup mode. alter TABLESPACE XX Begin backup will record the corresponding SCN.

The V$statname database dynamically counts the information, with the information name and ID, used in conjunction with V$sesstat.

V$sesstat the values of some dynamic statistics, such as user commits,opened cursors Current,redo size, and so on,

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 earliest point in time that can be recovered.
V$flashback_database_log: Usage of logs
V$flashback_database_stat:flashback statistical information.

The above three views have data on ALTER DATABASE flashback.
Flashback_transaction_query
V$nls_valid_values can be used in the language, country and encoding settings, alter session set Nls_language=american;

V$parameter_valid_values parameter values that can be set

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:SGA memory that is not allocated.

The data block information cached in V$BH DB buffer.

Set up client-side encoding

Nls_lang=american_america. ZHS16GBK, three parts, American language, hint information, America National influence date format, ZHS16GBK encoding.

View server encoding Select name,value$ from props$ where name = ' Nls_characterset ';

"Dba_ and v$ are usually the views that start with. The information inside the Dba_ opening is usually static, and the information is in the file (but it does not mean that the information inside does not change). The information that begins with v$ is usually dynamic, from control files and in-memory information "

Dba_data_files (v$datafile) data file information, which does not include information about the file that corresponds to the temporary tablespace, dba_tablespaces (v$tablespace) tablespace information, dba_tables table information, Dba_ Segments section information, dba_extents area information.

Dba_segments: Those paragraphs are saved to the table space, occupy a lot of space and so on.

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 table space. Dba_roles: Roles defined in the system, such as DBAs (note the difference from SYSDBA), connect, etc. There is also a user-related view dba_profiles: profile information defined in the database, the number of login attempts, password expiration time limit, and so on, the user can have how many sessions, idle how long can break the user, can be in the Create The user specifies the time.

Dba_sys_privs: What kind of user or role granted the system permissions.

Dba_tab_privs See what kind of user or role the object permissions are granted.

Dba_role_privs the role that a user or role has been granted. Permissions granted through roles are re-logged in order to function.

Permission pass: System permissions are retracted, the permissions passed are not retracted, object permissions are retracted, and the permissions passed are retracted.

Dba_constraints the constraint information.  Table-level constraint primary (P), Foreign key (R), Unique (U), Check. Column-level constraints are NOT NULL (which can be thought of as one of the check constraints, with the same constraint_type and check constraints in dba_constraints, all ' C '). There is also an alter for NOT NULL Table statement establishes constraints with four other types (ALTER TABLE TB add constraints ...) is different: ALTER TABLE TB MODIFY col_name not NULL.

Dba_priv_audit_opts: The database opens those permission audits. Aduit create session;. Aduit create session whenever not successful;

Dba_obj_audit_opts: The database opens those object audits. Audit select on Tb[by session/access].

Dba_stmt_audit_opts: Database Open statement Audit Audit table by UserA

Dba_audit_trail Audit Information View, part of the information is from the table aud$ audit information query out, table aud$ only with SYSDBA permissions to see.

Dba_fga_audit_trail holds audit information for fine-grained audits created through Dbms_fga.add_policy.

Dba_common_audit_trail saves the type information of the standard and fine-grained audits that are created.

Dba_rollback_segs information for the database fallback segment.

Dba_hist_wr_control awr (auto workload repository) generates control information for database payload information, retention time, and statistical frequency. Dbms_workload_repository. Modify_snapshot_settings (retention=>1440,interval=>30) modify the control.

Dba_hist_snapshot: Database Run snapshot historical information, according to Dba_hist_wr_control control information automatically generated, can be dbms_workload_repository. Create_ Snapshot generation.

Use Db_1\rdbms\admin\awrrpt.sql to generate database run reports. Db_1\rdbms\admin\awrrpt.sql\addmrpt.sql can generate diagnostic recommendation reports that correspond to them.

Dbms_workload_repository.create_baseline can choose to create a baseline from one snapshot to another (this time DB runs well) to make a baseline for subsequent database run information.

Dba_hist_baseline saved snapshot baseline information.

Dba_thresholds the upper value of the warning. How many alarms are used for the tablespace. The background process Mmon monitors this information.

Dba_outstanding_alerts last 10 warning messages. Dba_alert_history historical information about the warning.

Table:

System_privilege_map, system permissions information, such as SYSDBA and sysoper that are frequently used, are system permissions. Also like create Tablespace,drop tablespace, note that the drop table is not a system permission, and the drop any table is System permission. UNLIMITED tablespace permissions cannot be granted to a role, which means that users of the DBA role also need to grant UNLIMITED TABLESPAC privileges separately.

These views of the database, the table definition 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 ';

Some important v$ dynamic performance views, system views, and tables in Oracle