当前位置:数据库 > Oracle >>

管理Oracle时常用的数据字典和动态性能视图

管理Oracle时常用的数据字典和动态性能视图
 
1.v$version
查询数据库版本信息
[sql] 
select * from v$version;  
[html] 
BANNER  
--------------------------------------------------------------------------------  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
PL/SQL Release 11.2.0.3.0 - Production  
CORE    11.2.0.3.0  Production  
TNS for Solaris: Version 11.2.0.3.0 - Production  
NLSRTL Version 11.2.0.3.0 - Production  

 

2.v$sgainfo
查询sga中的相关信息
[sql] 
select name, bytes/1024/1024"Size(M)"from v$sgainfo;  
[html] 
NAME                                Size(M)  
-------------------------------- ----------  
Fixed SGA Size                   2.12744903  
Redo Buffers                     2.19921875  
Buffer Cache Size                       300  
Shared Pool Size                        216  
Large Pool Size                           4  
Java Pool Size                            4  
Streams Pool Size                         4  
Shared IO Pool Size                       0  
Granule Size                              4  
Maximum SGA Size                 816.328125  
Startup overhead in Shared Pool  68.5404663  
Free SGA Memory Available               284  

 

3.v$parameter 查询初始化参数相关信息
[sql] 
select name,valuefrom v$parameterwhere name like'%pool%';  
[html] 
NAME                                                                             VALUE  
-------------------------------------------------------------------------------- ----------------------------------------------------  
shared_pool_size                                                                 0  
large_pool_size                                                                  0  
java_pool_size                                                                   0  
streams_pool_size                                                                0  
shared_pool_reserved_size                                                        10905190  
buffer_pool_keep                                                                   
buffer_pool_recycle                                                                
global_context_pool_size                                                           
olap_page_pool_size                                                              0  
   
9 rows selected  
4.v$sysstat和v$statname
v$sysstat是统计信息,v$statname用于显示从v$sesstat和v$sysstat解析过的统计名。比如,确定系统总的事务量,可以:
[sql] 
select a.value as "TransactionCount" from v$sysstat a, v$statname b  
where a.statistic# = b.statistic# and b.name = 'user commits';  
[html] 
TransactionCount  
----------------  
            6954  
5.v$instance
这个v$视图显示当前实例的状态。比如,要确定系统总的运行时间:
[sql] 
select (sysdate - startup_time)*24*60*60 as seconds from v$instance;  
[html] 
SECONDS  
-------  
  34183  
6.v$session
查询会话信息。比如:
[sql] 
select username, sid, serial# from v$session where username is not null;  
[html] 
USERNAME                              SID    SERIAL#  
------------------------------ ---------- ----------  
SYS                                     1          7  
SYSMAN                                 32        165  
SYSMAN                                 33         25  
SYSMAN                                 35         55  
SYS                                    37       1065  
SYS                                    38       3205  
SYSMAN                                 39          3  
SYS                                    42       5039  
SYSMAN                                 43         49  
SYSMAN                                 44         97  
SYSMAN                                 45         29  
DBSNMP                                 46         95  
SYSMAN                                 47        359  
DBSNMP                                 48        893  
SYSMAN                                 51         13  
SYS                                    57       2345  
7.v$sql
查询sql相关信息。如下:

[sql] 
select sql_text, parse_calls, loads, executions from v$sql  
where upper(sql_text)like'。。。';  
8.v$process和v$bgprocess
进程和后台进程相关信息,比如:
[sql] 
select p.pid, p.spid, b.name, b.description from v$process p, v$bgprocess b  
where p.addr = b.paddr and b.paddr <>'00';  
[html] 
       PID SPID                     NAME  DESCRIPTION  
---------- ------------------------ ----- ----------------------------------------------------------------  
         2 808                      PMON  process cleanup  
         3 810                      PSP0  process spawner 0  
         4 812                      VKTM  Virtual Keeper of TiMe process  
         5 816                      GEN0  generic0  
         6 818                      DIAG  diagnosibility process  
         7 820                      DBRM  DataBase Resource Manager  
         8 822                      DIA0  diagnosibility process 0  
         9 824                      MMAN  Memory Manager  
        10 826                      DBW0  db writer process 0  
        11 828                      LGWR  Redo etc.  
        12 830                      CKPT  checkpoint  
        13 832                      SMON  System Monitor Process  
        14 834                      RECO  distributed recovery  
        15 836                      MMON  Manageability Monitor Process  
        16 838                      MMNL  Manageability Monitor Process 2  
        20 852                      ARC0  Archival Process 0  
        21 854                      ARC1  Archival Process 1  
        22 856                      ARC2  Archival Process 2  
        23 858                      ARC3  Archival Process 3  
        24 860                      QMNC  AQ Coordinator  
   
       PID SPID                     NAME  DESCRIPTION  
---------- ------------------------ ----- ----------------------------------------------------------------  
        25 874                      CJQ0  Job Queue Coordinator  
        28 908                      SMCO  Space Manager Process  
   
22 rows selected  

9.dba_tablespaces
[sql] 
select tablespace_name, status, contents from dba_tablespaces;  
[html] 
TABLESPACE_NAME                STATUS    CONTENTS  
------------------------------ --------- ---------  
SYSTEM                         ONLINE    PERMANENT  
SYSAUX                         ONLINE    PERMANENT  
UNDOTBS1                       ONLINE    UNDO  
TEMP                           ONLINE    TEMPORARY  
USERS                          ONLINE    PERMANENT  
EXAMPLE                        ONLINE    PERMANENT  
STU                            ONLINE    PERMANENT  
   
7 rows selected  
10.user_segments
查看段信息。比如:
[sql] 
select segment_name, segment_type, tablespace_name, bytes/1024"Size(KB)", extents, blocks  
from user_segments where segment_name ='DEPT'  
[html] 
SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                  Size(KB)    EXTENTS     BLOCKS  
------------------------------ ------------------ ------------------------------ ---------- ---------- ----------  
DEPT                           TABLE              USERS                                  64          1          8  
11.user_extents
查看区段信息。比如:
[sql] 
select segment_name, tablespace_name, extent_id, bytes/1024"Size(KB)", blocks   
from user_extents where segment_name ='DEPT';  
[html] 
SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID   Size(KB)     BLOCKS  
------------------------------ ------------------------------ ---------- ---------- ----------  
DEPT                           USERS                                   0         64          8  
12.dba_dat
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,