oracle数据库巡检sql脚本

如何查询sga内各组件信息和pga大小

SQL> conn / as sysdba

SQL> show parameter sga_max –查看sga的大小

NAME TYPE V ALUE

------------------------------------ ----------- ------------------------------

sga_max_size big integer 1578706860

SQL> show parameter sga_target –查看sga_target大小(只限10g以上版本)

NAME TYPE V ALUE

------------------------------------ ----------- ------------------------------

sga_target big integer 584M

SQL> show parameter db_cache_size –-查看db_cache大小

NAME TYPE V ALUE

------------------------------------ ----------- --------------------

db_cache_size big integer 1048576000

SQL> show parameter shared_pool_size –查看share_pool大小

NAME TYPE V ALUE

------------------------------------ ----------- ---------------------------

shared_pool_size big integer 318767104

SQL> show parameter pga—查看pga_aggregate_target

NAME TYPE V ALUE

------------------------------------ ----------- ---------------

pga_aggregate_target big integer 314572800

查询数据库版本的sql

SQL> select VERSION from v$instance;

VERSION

-----------------

9.2.0.1.0

SQL> select name, log_mode from v$database; --查看数据库名，归档模式

NAME LOG_MODE

--------- ------------

SKYOTA ARCHIVELOG –NOARCHIVELOG为非归档模式

查看数据库字符集

SQL> select value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

V ALUE

-----------------------------------------------------

ZHS16GBK

查看redo log 信息

查看日至组及成员

SELECT group#,member FROM V$LOGFILE

查看日志如成员大小

SQL> select group# ,bytes ,members from v$log;

GROUP# BYTES MEMBERS

---------- ---------- ----------

1 838860800 1

2 838860800 1

3 838860800 1

近一个月内每天日志切换次数

select to_char(first_time,'yyyy-mm-dd') day,count(*) times from

v$log_history where first_time> sysdate-30 group by

to_char(first_time,'yyyy-mm-dd') ;

查看监控当天高峰期日志切换情况–具体高峰时段根据项目特性修改sql

select count(*) times from v$log_history where first_time between

to_date('2009-04-11 10:00:00','yyyy-mm-dd hh24:mi:ss') and

to_date('2009-04-11 16:00:00','yyyy-mm-dd hh24:mi:ss');

在oracle告警日志中（alertsid.log），也可以查看最近日志切换的情况

表空间使用率：

SELECT UPPER (f.tablespace_name) "表空间名",

d.tot_grootte_mb "表空间大小(M)",

d.tot_grootte_mb - f.total_bytes "已使用空间(M)",

TO_CHAR

(ROUND ( (d.tot_grootte_mb - f.total_bytes)

/ d.tot_grootte_mb

* 100,

2

),

'990.99'

) "以使用占当前表空间大小比例",

f.total_bytes "空闲空间(M)", f.max_bytes "最大块(M)",

d.max_autobytes "表空间扩展的最大尺寸(M)",

TO_CHAR (ROUND ( (d.tot_grootte_mb - f.total_bytes)

/ d.max_autobytes

* 100,

2

),

'990.99'

) "以使用占最大扩展比例"

FROM (SELECT tablespace_name,

ROUND (SUM (BYTES) / (1024 * 1024), 2) total_bytes, ROUND (MAX (BYTES) / (1024 * 1024), 2) max_bytes

FROM SYS.dba_free_space

GROUP BY tablespace_name) f,

(SELECT dd.tablespace_name,

ROUND (SUM (dd.BYTES) / (1024 * 1024),

2) tot_grootte_mb,

TRUNC ( SUM (DECODE (dd.maxbytes,

0, dd.BYTES,

dd.maxbytes

)

)

/ (1024 * 1024)

) max_autobytes

FROM SYS.dba_data_files dd

GROUP BY dd.tablespace_name) d

WHERE d.tablespace_name = f.tablespace_name;