1.动态性能视图
用于记录当前例程的活动信息。当启动例程时,Oracle会自动建立动态性能视图;
当停止例程时,Oracle会自动删除动态性能视图。数据字典信息是从数据文件中取得,
而动态性能视图信息则是从SGA和控制文件中取得。
通过查询动态性能视图,一方面可以获得性能视图,另一方面还可以取得与磁盘
和内存结构相关的其他信息。
所有的动态性能视图都是以V_$开始的,Oracle为每个动态性能视图都提供了相应的同义词
(以V$开始)。例如,V_$DATAFILE的同义词为V$DATAFILE。
当数据库处于不同状态时,可以访问的动态性能视图有所不同。
1.1.NOMOUNT
启动例程时,Oracle会打开参数文件,分配SGA并启动后台进程。因此,当例程处于NOMOUNT
状态时,只能访问从SGA中获取信息的动态性能视图:
V$Parameter,v$Sga,v$Option,v$Process,v$Session,v$Version,v$Instance
1.2.MOUNT
装载数据库时,Oracle会根据初始化参数control_files打开所有控制文件。当例程处于
MOUNT状态时,不仅可以访问从SGA中获取信息的动态性能视图,而且可以访问从控制文
件中获取信息的动态性能视图:
v$Thread,v$Controlfile,v$Database,v$Datafile,v$Datafile_header,v$Logfile
1.3.OPEN
打开数据时,Oracle会按照控制文件所记载的信息打开所有数据文件和重做日志。除了
可以访问从SGA和控制文件中获取信息的动态性能视图外,还可以用于访问与Oracle性能
相关的动态性能视图,如:v$Filestat,v$Session_wait,v$Waitstat。
只有处于OPEN状态时,才能访问数据字典视图。
2.常用动态性能视图
2.1.v$Fixed_table
列出所有可用的动态性能视图和动态性能表。
SQL> select *
2 from v$fixed_table
3 where name like 'V$%' and rownum <=5;
NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
V$WAITSTAT 4294950915 VIEW 65537
V$BH 4294951406 VIEW 65537
V$GC_ELEMENT 4294951794 VIEW 65537
V$CR_BLOCK_SERVER 4294951796 VIEW 65537
V$CURRENT_BLOCK_SERVER 4294952095 VIEW 65537
2.2.v$Instance
取得当前例程的详细信息。
SQL> select *
2 from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PARALLEL THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE
--------------- ---------------- ---------------------------------------------------------------- ----------------- ------------ ------------ -------- ---------- -------- --------------- ---------- ---------------- ----------------- ------------------ ------------
1 orcl LINGLONG-MES 10.1.0.2.0 2013/11/11 1 OPEN NO 1 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL
2.3.v$Sga
显示SGA主要组成部分(共享池、数据高速缓存和重做日志缓冲区)
SQL> select *
2 from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 788028
Variable Size 143915460
Database Buffers 33554432
Redo Buffers 2097152
2.4.v$Sgainfo
取得SGA更详细的信息。
SQL> select *
2 from v$sgainfo;
NAME BYTES RESIZEABLE
-------------------------------- ---------- ----------
Fixed SGA Size 788028 No
Redo Buffers 2097152 No
Buffer Cache Size 33554432 Yes
Shared Pool Size 83886080 Yes
Large Pool Size 8388608 Yes
Java Pool Size 50331648 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 180355072 No
Startup overhead in Shared Pool 29360128 No
Free SGA Memory Available 0
11 rows selected
2.5.v$Parameter
取得初始化参数的详细信息。
SQL> select *
2 from v$parameter;
NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE ISMODIFIED ISADJUSTED ISDEPRECATED DESCRIPTION UPDATE_COMMENT HASH
---------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------- ---------------- ---------------- --------------------- ---------- ---------- ------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
2 tracefile_identifier 2 TRUE TRUE FALSE FALSE FALSE FALSE FALSE trace file custom identifier 978134115
19 lock_name_space 2 TRUE FALSE FALSE FALSE FALSE FALSE TRUE lock name space used for generating lock names for standby/clone database 1022980314
20 processes 3 150 150 FALSE FALSE FALSE FALSE FALSE FALSE FALSE user processes 4162014761
21 sessions 3 170 170 TRUE FALSE FALSE FALSE FALSE FALSE FALSE user and system sessions 3194028855
22 timed_statistics 1 TRUE TRUE TRUE TRUE IMMEDIATE TRUE FALSE FALSE FALSE maintain internal timing statistics 2224114877
23 timed_os_statistics 3 0 0 TRUE TRUE IMMEDIATE TRUE FALSE FALSE FALSE internal os statistic gathering interval in seconds 2425331770
24 resource_limit 1 FALSE FALSE TRUE FALSE IMMEDIATE TRUE FALSE FALSE FALSE master switch for resource limit 19363908
25 license_max_sessions 3 0 0 TRUE FALSE IMMEDIATE TRUE FALSE FALSE FALSE maximum number of non-system user sessions allowed 2409810571
26 license_sessions_warning 3 0 0 TRUE FALSE IMMEDIATE TRUE FALSE FALSE FALSE warning level for number of non-system user sessions 4168462818
42 cpu_count 3 8 8 TRUE FALSE IMMEDIATE TRUE FALSE FALSE FALSE number of CPUs for this instance 1095434542
44 instance_groups 2 TRUE FALSE FALSE FALSE FALSE FALSE FALSE list of instance group names 714080904
45 event 2 TRUE FALSE FALSE FALSE FALSE FALSE FALSE debug event control - default null string 557769962
52 sga_max_size 6 180355072 172M TRUE FALSE FALSE FALSE FALSE FALSE FALSE max total SGA size internally adjusted 3640569645
56 pre_page_sga 1 FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE pre-page sga for process 4157293338
57 shared_memory_address 3 0
2.6.v$Version
取得Oracle版本的详细信息。
SQL> select *
2 from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
2.7.v$Option
显示已安装的Oracle选项,其中TRUE表示该选项已经安装,而FALSE则表示该选项没有
安装。
SQL> select *
2 from v$option;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
Change Data Capture TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
2.8.v$Session
SQL> select *
2 from v$session;
SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND OWNERID TADDR LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS MACHINE TERMINAL PROGRAM TYPE SQL_ADDRESS SQL_HASH_VALUE SQL_ID SQL_CHILD_NUMBER PREV_SQL_ADDR PREV_HASH_VALUE PREV_SQL_ID PREV_CHILD_NUMBER MODULE MODULE_HASH ACTION ACTION_HASH CLIENT_INFO FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIME LAST_CALL_ET PDML_ENABLED FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER RESOURCE_CONSUMER_GROUP PDML_STATUS PDDL_STATUS PQ_STATUS CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER BLOCKING_SESSION_STATUS BLOCKING_SESSION SEQ# EVENT# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE SERVICE_NAME
-------- ---------- ---------- ---------- -------- ---------- ------------------------------ ---------- ---------- -------- -------- -------- --------- ---------- ------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------- ---------------------------------------------------------------- ---------- ----------- -------------- ------------- ---------------- ------------- --------------- ------------- ----------------- ------------------------------------------------ ----------- -------------------------------- ----------- ---------------------------------------------------------------- -------------------- ------------- -------------- --------------- ------------- ----------- ------------ ------------ ------------- --------------- ----------- -------------------------------- ----------- ----------- --------- ---------------------- ---------------------------------------------------------------- ----------------------- ---------------- ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- -------- ------------- ----------- ---------------------------------------------------------------- ---------- --------------- ------------------- ----------------------------------------------------------------
6CDAE9DC 131 33502 6206 6CCF5004 54 SYSMAN 0 2147483644 INACTIVE DEDICATED 54 SYSMAN 1234 linglong-mes OMS USER 6ACAC68C 388996924 bqsnq88bkz7tw 0 6ACAC68C 388996924 bqsnq88bkz7tw 0 OEM.CacheModeWaitPool 796036576 0 linglong-mes_Management_Service 206270 48217 3 23028 0 2013/11/12 61369 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NO HOLDER 105 590 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 2723168908 6 Idle 0 61369 WAITING orcl
6CDB0DBC 133 28094 7070 6CCF5904 5 SYSTEM 3 2147483644 ACTIVE DEDICATED 5 SYSTEM Administrator 3552:2380 WORKGROUP\P6EGCFOBGPKTMRP P6EGCFOBGPKTMRP plsqldev.exe USER 69F08B40 3066501299 3h5px3fvcf65m 0 6AE16EF0 356401299 9m7787camwh4m 0 PL/SQL Developer 1190136663 命令窗口 - 新建 1316471608 298407 4 1 42350 0 2013/11/13 0 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NO HOLDER 1065 586 SQL*Net message to client driver id 1413697536 54435000 #bytes 1 00000001 0 00 2000153315 7 Network 0 0 WAITING SYS$USERS
6CDB1FAC 134 48363 6205 6CCF4B84 54 SYSMAN 0 2147483644 INACTIVE DEDICATED 54 SYSMAN 1234 linglong-mes OMS USER 690C3C64 3039427182 bn30dmqukmymf 0 690C3C64 3039427182 bn30dmqukmymf 0 OEM.CacheModeWaitPool 796036576 0 linglong-mes_Management_Service 206273 47919 3 20717 0 2013/11/12 61369 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NO HOLDER 239 590 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 2723168908 6 Idle 0 61369 WAITING orcl
6CDB319C 135 36533 0 6CCF3504 0 0 2147483644 ACTIVE DEDICATED 0 SYS USER 00 0 0 00 0 0 0 0 263561 -1 0 0 0 2013/11/13 10970 NO NONE NONE NO DISABLED ENABLED ENABLED 0 UNKNOWN 1 532 jobq slave wait 0 00 0 00 0 00 2723168908 6 Idle 0 10970 WAITING SYS$USERS
6CDB557C 137 20 5947 6CCF3984 54 SYSMAN 0 2147483644 INACTIVE DEDICATED 54 SYSMAN 1234 linglong-mes OMS USER 00 0 0 00 0 0 OMS 0 0 63 -1 0 0 0 2013/11/11 186676 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NO HOLDER 4 590 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 2723168908 6 Idle 0 186676 WAITING orcl
6CDB676C 138 4 5948 6CCF3E04 54 SYSMAN 0 2147483644 INACTIVE DEDICATED 54 SYSMAN 1234 linglong-mes OMS USER 6A23D54C 3275117642 43c5ykm1mcp2a 0 6A23D54C 3275117642 43c5ykm1mcp2a 0 OEM.BoundedPool 3809405486 0 linglong-mes_Management_Service 298396 48488 3 36670 0 2013/11/11 97 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NO HOLDER 55159 590 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 2723168908 6 Idle 0 97 WAITING orcl
6CDB795C 139 19 5949 6CCF4284 22 DBSNMP 0 2147483644 INACTIVE DEDICATED 22 DBSNMP NT AUTHORITY\SYSTEM 1512:4024 LINGLONG-MES emagent.exe USER 00 0 69F48B90 2075519412 cca61npxvbudn 0 emagent.exe 0 0 298397 8840 3 4637 0 2013/11/11 71 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NO HOLDER 59765 590 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 71 WAITING SYS$USERS
6CDB9D3C 141 11 5950 6CCF4704 22 DBSNMP 0 2147483644 6C7A188C ACTIVE DEDICATED 22 DBSNMP NT AUTHORITY\SYSTEM 1512:1060 LINGLONG-MES emagent.exe USER 69608E9C 1744635340 c1599tjmzu1fc 0 69608E9C 1744635340 c1599tjmzu1fc 0 emagent.exe 0 0 298405 0 2 83 0 2013/11/11 0 NO NONE NONE NO DISABLED ENABLED ENABLED 0 UNKNOWN 58125 602 queue messages queue id 8552 00002168 process# 1825523948 6CCF48EC wait time 5 00000005 2723168908 6 Idle 0 0 WAITING SYS$USERS
6CDC1ACC 148 49864 7061 6CCF5484 5 SYSTEM 0 2147483644 INACTIVE DEDICATED 5 SYSTEM Administrator 3552:2380 WORKGROUP\P6EGCFOBGPKTMRP P6EGCFOBGPKTMRP plsqldev.exe USER 00 0 6948E560 2564406738 cf06fwacdmgfk 0 PL/SQL Developer 1190136663 Primary Session 206085144 298405 48 1 6708 0 2013/11/13 0 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NO HOLDER 364 590 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 0 WAITING SYS$USERS
6CDC2CBC 149 4 5945 6CCF2C04 54 SYSMAN 0 2147483644 INACTIVE DEDICATED 54 SYSMAN 1234 linglong-mes OMS USER 00 0 6A03A624 312242989 cydnuss99swtd 0 1715534958 1715534958 linglong-mes_Management_Service 298405 0 2 1001 0 2013/11/11 1 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NO HOLDER 15662 590 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 2723168908 6 Idle 0 1 WAITING orcl
6CDC3EAC 150 28 5946 6CCF3084 54 SYSMAN 0 2147483644 INACTIVE DEDICATED 54 SYSMAN 1234 linglong-mes OMS USER 00 0 6A18334C 729575269 8507xv0prsvv5 0 OEM.SystemPool 2960518376 0 linglong-mes_Management_Service 298405 0 2 174 0 2013/11/11 26 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NO HOLDER 15595 590 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 2723168908 6 Idle 0 26 WAITING orcl
6CDC509C 151 2 5944 6CCF2784 54 SYSMAN 47 2147483644 6C780324 ACTIVE DEDICATED 54 SYSMAN 1234 linglong-mes OMS USER 6A1240C4 4281219134 2b064ybzkwf1y 0 6A1240C4 4281219134 2b064ybzkwf1y 0 OEM.SystemPool 2960518376 0 linglong-mes_Management_Service 298405 47740 3 19908 0 2013/11/11 28 NO NONE NONE NO DISABLED ENABLED ENABLED 0 UNKNOWN 29908 74 wait for unread message on broadcast channel channel context 1827550684 6CEE35DC channel handle 1827519692 6CEDBCCC 0 00 2723168908 6 Idle 0 28 WAITING orcl
6CDC628C 152 2 5943 6CCF2304 54 SYSMAN 0 2147483644 INACTIVE DEDICATED 54 SYSMAN 1234 linglong-mes OMS USER 00 0 6A197B5C 4052273390 b9huk6zssjk7f 0 OEM.SystemPool 2960518376 XMLLoader0 76787928 linglong-mes_Management_Service 298397 47841 3 27523 0 2013/11/11 80 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NO HOLDER 34133 590 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 2723168908 6 Idle 0 80 WAITING orcl
6CDC747C 153 19 5942 6CCF1E84 54 SYSMAN 0 2147483644 INACTIVE DEDICATED 54 SYSMAN
2.9.v$Process
显示与Oracle相关的所有进程的信息(包括后台进程和服务器进程)。
SQL> select *
2 from v$process;
ADDR PID SPID USERNAME SERIAL# TERMINAL PROGRAM TRACEID BACKGROUND LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
-------- ---------- ------------ --------------- ---------- ---------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- --------- --------- ------------ ------------- ---------------- -----------
6CCED204 1 0 PSEUDO 0 0 0 0
6CCED684 2 312 SYSTEM 1 LINGLONG-MES ORACLE.EXE (PMON) 1 217870 581734 0 581734
6CCEDB04 3 2224 SYSTEM 1 LINGLONG-MES ORACLE.EXE (MMAN) 1 217170 581734 0 581734
6CCEDF84 4 728 SYSTEM 1 LINGLONG-MES ORACLE.EXE (DBW0) 1 270250 1631758 0 1631758
6CCEE404 5 3728 SYSTEM 1 LINGLONG-MES ORACLE.EXE (LGWR) 1 4443618 10151930 0 10151930
6CCEE884 6 1136 SYSTEM 1 LINGLONG-MES ORACLE.EXE (CKPT) 1 236806 1616514 0 1616514
6CCEED04 7 3708 SYSTEM 1 LINGLONG-MES ORACLE.EXE (SMON) 1 67350 1237094 0 1761382
6CCEF184 8 4020 SYSTEM 1 LINGLONG-MES ORACLE.EXE (RECO) 1 225778 581734 0 778342
6CCEF604 9 1036 SYSTEM 1 LINGLONG-MES ORACLE.EXE (CJQ0) 1 443210 778342 0 1171558
6CCEFA84 10 1008 SYSTEM 1 LINGLONG-MES ORACLE.EXE (D000) 669706 767954 0 1433702
6CCEFF04 11 564 SYSTEM 1 LINGLONG-MES ORACLE.EXE (S000) 224002 309202 0 516198
6CCF0384 12 696 SYSTEM 2 LINGLONG-MES ORACLE.EXE (J000) 82168 1030098 0 13547474
6CCF0804 13 2884 SYSTEM 1 LINGLONG-MES ORACLE.EXE (QMNC) 1 222674 581734 0 581734
6CCF0C84 14 1048 SYSTEM 1 LINGLONG-MES ORACLE.EXE (MMON) 1 2312670 3465258 0 3727402
6CCF1104 15 4088 SYSTEM 1 LINGLONG-MES ORACLE.EXE (MMNL) 1 222546 581734 0 581734
6CCF1584 16 2136 SYSTEM 211 LINGLONG-MES ORACLE.EXE (q000) 1 231322 581734 0 581734
6CCF1A04 17 4064 SYSTEM 1 LINGLONG-MES ORACLE.EXE (SHAD) 453418 909414 0 1040486
6CCF1E84 18 1916 SYSTEM 1 LINGLONG-MES ORACLE.EXE (SHAD) 445498 1030098 0 1554386
6CCF2304 19 2256 SYSTEM 1 LINGLONG-MES ORACLE.EXE (SHAD) 1288302 1685458 0 2727394
6CCF2784 20 3500 SYSTEM 1 LINGLONG-MES ORACLE.EXE (SHAD)
2.10.v$Bgprocess
用于显示后台进程的详细信息。
SQL> select *
2 from v$bgprocess;
PADDR PSERIAL# NAME DESCRIPTION ERROR
-------- ---------- ----- ---------------------------------------------------------------- ----------
6CCED684 1 PMON process cleanup 0
00 0 DIAG diagnosibility process 0
00 0 FMON File Mapping Monitor Process 0
00 0 LMON global enqueue service monitor 0
00 0 LMD0 global enqueue service daemon 0 0
00 0 LMS0 global cache service process 0 0
00 0 LMS1 global cache service process 1 0
00 0 LMS2 global cache service process 2 0
00 0 LMS3 global cache service process 3 0
00 0 LMS4 global cache service process 4 0
00 0 LMS5 global cache service process 5 0
00 0 LMS6 global cache service process 6 0
00 0 LMS7 global cache service process 7 0
00 0 LMS8 global cache service process 8 0
00 0 LMS9 global cache service process 9 0
00 0 LMSa global cache service process 10 0
00 0 LMSb global cache service process 11 0
00 0 LMSc global cache service process 12 0
00 0 LMSd global cache service process 13 0
2.11.v$Database
取得当前数据库的详细信息(如数据库名、日志操作模式以及建立时间等)。
SQL> select *
2 from v$database;
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_TYPE CONTROLFILE_CREATED CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLFILE_TIME OPEN_RESETLOGS VERSION_TIME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL REMOTE_ARCHIVE ACTIVATION# SWITCHOVER# DATABASE_ROLE ARCHIVELOG_CHANGE# ARCHIVELOG_COMPRESSION SWITCHOVER_STATUS DATAGUARD_BROKER GUARD_STATUS SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI FORCE_LOGGING PLATFORM_ID PLATFORM_NAME RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN FLASHBACK_ON SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL DB_UNIQUE_NAME STANDBY_BECAME_PRIMARY_SCN
---------- --------- ----------- ----------------- -------------- ----------------------- -------------------- ------------ ------------------ --------------- ---------------- ------------------- --------------------- ------------------- ---------------- -------------- ------------ ---------- -------------------- -------------------- -------------- ----------- ----------- ---------------- ------------------ ---------------------- -------------------- ---------------- ------------ ------------------------- ------------------------ ------------------------ ------------- ----------- -------------------------------------------------------------------------------- ---------------------------- ---------------------- ----------- ------------ ------------------------ ------------------------- ------------------------------ --------------------------
1358052075 ORCL 2013/10/31 318842 2013/10/31 14: 1 2004/3/9 23:57:28 NOARCHIVELOG 1612969 1605611 CURRENT 2013/10/31 14:55:39 1971 1613249 2013/11/14 11:18 NOT ALLOWED 2013/10/31 1 READ WRITE MAXIMUM PERFORMANCE UNPROTECTED ENABLED 1358008555 1358008555 PRIMARY 0 DISABLED SESSIONS ACTIVE DISABLED NONE NO NO NO NO 7 Microsoft Windows IA (32-bit) 2 2 1614725 NO NO NO orcl 0
2.12.v$controlfile
取得当前数据库所有控制文件的信息。
SQL> select *
2 from v$controlfile;
STATUS NAME IS_RECOVERY_DEST_FILE
------- -------------------------------------------------------------------------------- ---------------------
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL NO
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL NO
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL NO
2.13.v$datafile
取得当前数据库所有数据文件的详细信息。
SQL> select *
2 from v$datafile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- --------------------------------------------------------------------------------
1 10 2004/3/9 23:5 0 1 SYSTEM READ WRITE 1612969 2013/11/14 11:1 0 318841 318842 2013/10/31 471859200 57600 0 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF 0 8192 NONE
2 317561 2004/3/10 1:0 1 2 ONLINE READ WRITE 1612969 2013/11/14 11:1 0 318841 318842 2013/10/31 31457280 3840 0 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF 0 8192 NONE
3 5833 2004/3/9 23:5 2 3 ONLINE READ WRITE 1612969 2013/11/14 11:1 0 318841 318842 2013/10/31 325058560 39680 0 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF 0 8192 NONE
4 8840 2004/3/9 23:5 4 4 ONLINE READ WRITE 1612969 2013/11/14 11:1 0 318841 318842 2013/10/31 5242880 640 0 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF 0 8192 NONE
5 341980 2013/10/31 14 6 5 ONLINE READ WRITE 1612969 2013/11/14 11:1 0 0 0 157286400 19200 157286400 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF 0 8192 NONE
2.14.v$dbfile
用于取得数据文件编号及名称。
SQL> select *
2 from v$dbfile;
FILE# NAME
---------- --------------------------------------------------------------------------------
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
5 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF
2.15.v$logfile
显示重做日志成员的信息。
SQL> select *
2 from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG NO
2 ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG NO
1 ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG NO
2.16.v$log
显示日志组的详细信息。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 374 10485760 1 NO CURRENT 1612969 2013/11/14
2 1 372 10485760 1 NO INACTIVE 1605611 2013/11/14
3 1 373 10485760 1 NO INACTIVE 1610015 2013/11/14
2.17.v$thread
取得重做线程的详细信息。当使用RAC结构时,每个例程都对应一个重做线程,并且
每个重做线程会包含独立的重做日志组。
SQL> select * from v$thread;
THREAD# STATUS ENABLED GROUPS INSTANCE OPEN_TIME CURRENT_GROUP# SEQUENCE# CHECKPOINT_CHANGE# CHECKPOINT_TIME ENABLE_CHANGE# ENABLE_TIME DISABLE_CHANGE# DISABLE_TIME
---------- ------ -------- ---------- -------------------------------------------------------------------------------- ----------- -------------- ---------- ------------------ --------------- -------------- ----------- --------------- ------------
1 OPEN PUBLIC 3 orcl 2013/11/11 1 374 1612969 2013/11/14 11:1 318842 2013/10/31 0
2.18.v$lock
显示锁信息。通过与v$session执行连接查询,可以显示占有锁的会话,以及等待锁的会话。
SQL> select a.username,a.machine,b.lmode,b.request
2 from v$session a,v$lock b
3 where a.sid=b.sid and a.type='SYSTEM'
4 ;
USERNAME MACHINE LMODE REQUEST
2.19.v$locked_object
显示被加锁的数据库对象。通过与DBA_OBJECTS进行连接查询,可以显示具体的对象名
及执行加锁操作的Oracle用户。
SQL> select a.oracle_username,b.owner||'.'||b.object_name object
2 from v$locked_object a,dba_objects b
3 where a.OBJECT_ID = b.OBJECT_ID;
ORACLE_USERNAME OBJECT
------------------------------ --------------------------------------------------------------------------------
2.20.v$rollname、v$rollstat
v$rollname动态性能视图用于显示处于ONLINE状态的UNDO段,而v$rollstat则用于显示
UNDO段统计信息。通过二者之间执行连接查询,可以显示UNDO段的详细统计信息。
SQL> select a.name,b.xacts from v$rollname a,v$rollstat b
2 where a.usn=b.usn;
NAME XACTS
------------------------------ ----------
SYSTEM 0
_SYSSMU1$ 0
_SYSSMU2$ 0
_SYSSMU3$ 0
_SYSSMU4$ 0
_SYSSMU5$ 0
_SYSSMU6$ 0
_SYSSMU7$ 0
_SYSSMU8$ 0
_SYSSMU9$ 0
_SYSSMU10$ 0
11 rows selected
2.21.v$tablespace
显示表空间信息。
SQL> select *
2 from v$tablespace;
TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON
---------- ------------------------------ --------------------------- ------- ------------
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP YES NO YES
6 EXAMPLE YES NO YES
6 rows selected
2.22.v$tempfile
显示当前数据库所包含的临时文件。
SQL> select *
2 from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
1 0 3 1 ONLINE READ WRITE 20971520 2560 20971520 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP01.DBF