Oracle 数据库日常健康检查脚本

set pagesize 0;
select tablespace_name ,sum(bytes)/1024/1024/1024 as GB from dba_data_files group by tablespace_name;
select sum(bytes)/1024/1024/1024 as GB from dba_segments;
select bytes/1024/1024 as MB from v$log;

COL NAME FORMAT A50
SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I
WHERE F.FILE#=I.FILE_NO
AND FILETYPE_NAME='Data File';  2    3  
NAME         ASYNCH_IO
-------------------------------------------------- ---------
/u01/app/oracle/oradata/yaoy11G/system01.dbf    ASYNC_ON
/u01/app/oracle/oradata/yaoy11G/sysaux01.dbf    ASYNC_ON
/u01/app/oracle/oradata/yaoy11G/undotbs01.dbf    ASYNC_ON
/u01/app/oracle/oradata/yaoy11G/users01.dbf    ASYNC_ON
/u01/app/oracle/oradata/yaoy11G/example01.dbf    ASYNC_ON

set pages 100;
set lines 150;
col sid format 999999;
col event format a40;
col state format a15;
col waiting head "Waiting(in seconds)" format 9,999.99;
col waited head "Waited" format 9,999.99;
select sid, state, event,
       seconds_in_wait  waiting,
       wait_time/100    waited, p1,p2,p3, BLOCKING_SESSION "Blocker"
from v$session
where event not in
(
  'SQL*Net message from client',
  'SQL*Net message to client',
  'rdbms ipc message'
)
and state = 'WAITING'
and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP');

set lines 120;
set pages 999;
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
where first_time > sysdate - 7 
GROUP by
to_char(first_time,'YYYY-MON-DD');