oracle 动态性能视图

本文深入探讨了Oracle动态性能视图的原理、用途及如何访问不同状态下的动态性能视图,涵盖了从基本概念到具体实例的全面解析。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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
     
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值