该脚本适用于新环境数据库的初步检测。
数据库检查：check_db_stats.sql
Rem
Rem check_db_stats_v1.2.sql
Rem modify by ludatou
Rem
Rem only for single instance database
Rem
Rem
Rem This script do health check .
Rem Must run under system or user has dba privilege.
Rem
set pages 0
set lines 1000
set trimspool on
set head off
set feedback off
set echo off
set verify off
Rem get report name based on database name and report date
Rem
col logname noprint new_value log_name
select lower(name)||to_char(sysdate,'yyyymmddhh24mi')||'.txt' logname
from v$database;
spool &log_name
Rem
Rem report header
Rem
prompt
select 'Report produced at '||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
prompt
prompt Basic information:
prompt --------------------------------------------------------------------------------
Rem
Rem Check database information
Rem
set head on
set pages 45
col dbid heading "Database|ID" format a11
col name heading "Database|Name"
col open_mode heading "Open |Mode"
col force_logging heading "Force|Logging" format a7
select
to_char(dbid,9999999999) dbid
, name
, open_mode
, force_logging
from v$database
;
Rem
Rem Check instance information
Rem
col instance_name heading "Instance|Name" format a10
col host_name heading "Host|Name" format a10
col status heading "Instance|Status" format a8
col archiver heading "Archiver|Status" format a8
col up_time heading "Running Time" format a30
select
host_name
, instance_name
, status
, archiver
, trunc(sysdate - startup_time) || ' Days '
|| trunc(mod(sysdate-startup_time, 1) *24) || ' Hours '
|| trunc(mod((sysdate-startup_time)*24,1)*60) || ' Minutes '
up_time
from v$instance
;
prompt
prompt
prompt Check hit ratio
prompt These value expected higher than 90%
prompt --------------------------------------------------------------------------------
Rem
Rem Check buffer cache hit ratio
Rem
col pr heading "Physical|Reads" format 999,999,999
col prd heading "Phy_Reads|Direct" format 999,999,999
col prl heading "Phy_Reads|Direct_LOB" format 999,999,999
col bg heading "Block|Gets" format 999,999,999,999
col cg heading "Consistent|Gets" format 999,999,999,999
col ht heading "Buffer|Hit Ratio"
select
to_char((1-((pr - prd - prl) / (bg + cg - prd - prl))) * 100, '999.9')||'%' ht
, bg, cg, pr, prd, prl
from
(select value pr from v$sysstat where name = 'physical reads') pr
, (select value prd from v$sysstat where name = 'physical reads direct') prd
, (select value prl from v$sysstat where name = 'physical reads direct (lob)') prl
, (select value bg from v$sysstat where name = 'db block gets') bg
, (select value cg from v$sysstat where name = 'consistent gets') cg
;
Rem
Rem check library hit ratio
Rem
col ht heading "Libray|Hit Ratio" format a10
select
to_char(sum(pinhits) / sum(pins) * 100, 999.9)||'%' ht
from v$librarycache
;
prompt
prompt Check session informations
prompt --------------------------------------------------------------------------------
Rem
Rem Check session high water mark
Rem
col sessions_current heading "Sessions|Current" format 999,999,999
col sessions_highwater heading "Sessions|High Water" format 999,999,999
select
sessions_current
, sessions_highwater
from v$license
;
Rem
Rem Check session wait events
Rem
col username heading "User Name" format a15
col program heading "Program" format a35
col event heading "Wait Event" format a25
prompt
prompt Session wait events, excluding waiting for user's message
prompt _________________
col event format a32
col wait_class format a18
col segment_name format a30
select event, count(*)
from v$session_wait
where event not in (select name from v$event_name where wait_class = 'Idle')
group by event order by 2 desc;
Rem
Rem Check session status
Rem
col status heading "Session|Status"
col nu heading "Number|of Sessions"
compute sum of nu on report
break on report
prompt
prompt Session status
prompt -----------------
select
status, count(*) nu
from v$session
where username is not null
group by status
;
clear break;
Rem
Rem detail information for sessions which idle for more than 4 hours
Rem
col lc heading "Idle Time|(Hours)" format a8
col username format a10 heading "Database|Username"
col machine format a17 heading "Machine"
col osuser format a10 heading "OS|Username"
col prg format a35 heading "Program"
prompt
prompt Idle sessions
prompt -----------------
select
username, machine, osuser, program prg
, to_char(trunc(last_call_et/3600, 1), 99999.9) lc
from v$session
where last_call_et > 14400
and username is not null
order by last_call_et desc
;
prompt
prompt Redo log files information
prompt --------------------------------------------------------------------------------
Rem
Rem Online redo logfile information
Rem
col grp format 99 heading "Log Group|Number"
col bytes format 999,999.99 heading "Bytes|(M)"
col status heading "Status"
col member heading "Log File|Members" format a45
break on grp on bytes on status skip 1
prompt
prompt Online redo log file
prompt -----------------
select
l.group# grp
, l.bytes/1024/1024 bytes
, l.status
, lf.member
from v$log l, v$logfile lf
where l.group# = lf.group#
order by 1
;
clear break;
Rem
Rem Online redo logfile switch frequency
Rem
col dt heading "Begin Time (1 hour)" format a25
col cnt heading "Switch times" format 999
prompt
prompt Switch frequency
prompt -----------------
select
to_char(trunc(first_time, 'hh'), 'yyyy-mm-dd hh24:mi') dt
, count(*) cnt
from
v$loghist
where first_time > sysdate - 30
group by trunc(first_time,'hh')
order by 1
;
Rem
Rem Space usage check
Rem
prompt
prompt Tablespace usage
prompt --------------------------------------------------------------------------------
col tbsn heading "Tablespace|Name" format a20
col bytes heading "Current|Size(M)" format 999,999.99
col max_b heading "Maximum|Size(M)" format 999,999.99
col fre_b heading "Free Space|Size(M)" format 999,999.99
col usg heading "Free Space|persentage" format a10
col em heading "Extent|Management" format a10
col ssm heading "Segment|Management" format a10
select
tb.tbsn
, bytes
, fre_b
, lpad(to_char(nvl(fre_b,0)/bytes*100, 999.99)||'%',10) usg
, max_b
from
(select tablespace_name tbsn, sum(bytes)/1024/1024 bytes
, sum( decode(AUTOEXTENSIBLE,
'YES', greatest(bytes, maxbytes),
bytes))/1024/1024 max_b
from dba_data_files
group by tablespace_name
) tb,
(select tablespace_name tbsn, sum(bytes)/1024/1024 fre_b
from dba_free_space
group by tablespace_name
) fre
where tb.tbsn = fre.tbsn (+)
order by 4
;
prompt
prompt Table usage
prompt -----------------
set serveroutput on
exec dbms_output.enable(100000000);
declare
type seg is record (
seg_owner dba_segments.owner%type,
seg_name dba_segments.segment_name%type,
par_name dba_segments.PARTITION_NAME%type,
seg_type dba_segments.segment_type%type,
tbs_name dba_segments.TABLESPACE_NAME%type
);
type usg is record (
t_bck number,
t_byt number,
u_bck number,
u_byt number,
luefi number,
luebi number,
lub number);
v_seg seg;
v_usg usg;
v_tbsname dba_tablespaces.tablespace_name%type;
v_tbsseg dba_tablespaces.SEGMENT_SPACE_MANAGEMENT%type;
cursor c_tbs is
select
tablespace_name, SEGMENT_SPACE_MANAGEMENT
from
dba_tablespaces
where
CONTENTS = 'PERMANENT'
order by 1
;
cursor c_seg is
select
owner, segment_name, PARTITION_NAME, segment_type
from
dba_segments
where
owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'WMSYS')
and tablespace_name = v_tbsname
order by 1, 2, 3
;
begin
open c_tbs;
fetch c_tbs into v_tbsname, v_tbsseg;
while c_tbs%found loop
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line('Tablespace: '||v_tbsname);
dbms_output.put_line (
rpad('Owner',15)
||rpad('Segment Name', 30)
||rpad('Par', 8)
||rpad('Seg Type', 10)
||rpad('Bytes(K)',10)
||'Used(K)'
);
dbms_output.put_line (
rpad('-',14, '-')||' '
||rpad('-',29, '-')||' '
||rpad('-',7, '-')||' '
||rpad('-', 9, '-')||' '
||rpad('-',9, '-')||' '
||' ------------'
);
open c_seg;
fetch c_seg into v_seg.seg_owner, v_seg.seg_name, v_seg.par_name, v_seg.seg_type;
while c_seg%found loop
if v_seg.seg_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',
'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'CLUSTER','LOB') then
dbms_space.unused_space (
v_seg.seg_owner, v_seg.seg_name, v_seg.seg_type
, v_usg.t_bck, v_usg.t_byt, v_usg.u_bck, v_usg.u_byt
, v_usg.luefi, v_usg.luebi, v_usg.lub, v_seg.par_name);
dbms_output.put_line(rpad(v_seg.seg_owner,15)
||rpad(v_seg.seg_name, 30)
||rpad(substr(nvl(v_seg.par_name,'NULL'),1,7), 8)
||rpad(substr(v_seg.seg_type, 1, 9), 10)
||to_char(v_usg.t_byt/1024, '9,999,999')
||to_char((v_usg.t_byt-v_usg.u_byt)/1024, '9,999,999')
);
end if;
fetch c_seg into v_seg.seg_owner, v_seg.seg_name, v_seg.par_name, v_seg.seg_type;
end loop;
close c_seg;
fetch c_tbs into v_tbsname, v_tbsseg;
end loop;
close c_tbs;
end;
/
spool off;
exit;
脚本效果：
[ora10g@ludatou ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 28 17:25:00 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @check_db_heath.sql
Report produced at 2014-07-28 17:25:04
Basic information:
-------------------------------------------------------------------------------
Database Database Open Force
ID Name Mode Logging
----------- --------- ---------- -------
2580936880 LU10G READ WRITE NO
Host Instance Instance Archiver
Name Name Status Status Running Time
---------- ---------- -------- -------- ------------------------------
ludatou lu10g OPEN STOPPED 1 Days 18 Hours 9 Minutes
Check hit ratio
These value expected higher than 90%
-------------------------------------------------------------------------------
Buffer Block Consistent Physical Phy_Reads Phy_Reads
Hit Rat Gets Gets Reads Direct Direct_LOB
------- ---------------- ---------------- ------------ ------------ ------------
98.8% 221,523 1,181,248 16,972 34 0
Libray
Hit Ratio
----------
95.3%
Check session informations
-------------------------------------------------------------------------------
Sessions Sessions
Current High Water
------------ ------------
1 10
Session status
----------------
Session Number
Status of Sessions
-------- -----------
ACTIVE 1
-----------
sum 1
Idle sessions
----------------
Redo log files information
-------------------------------------------------------------------------------
Online redo log file
----------------
Log Group Bytes Log File
Number (M) Status Members
--------- ----------- -------- ---------------------------------------------
1 50.00 CURRENT /oradata/lu10g/redo01.log
2 50.00 INACTIVE /oradata/lu10g/redo02.log
3 50.00 INACTIVE /oradata/lu10g/redo03.log
Switch frequency
----------------
Begin Time (1 hour) Switch times
------------------------- ------------
2014-07-24 13:00 2
2014-07-26 23:00 1
Tablespace usage
-------------------------------------------------------------------------------
Tablespace Current Free Space Free Space Maximum
Name Size(M) Size(M) persentage Size(M)
-------------------- ----------- ----------- ---------- -----------
SYSTEM 480.00 4.25 .89% 32,767.98
SYSAUX 260.00 9.38 3.61% 32,767.98
UNDOTBS1 25.00 21.88 87.50% 32,767.98
USERS 5.00 4.56 91.25% 32,767.98
Table usage
----------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tablespace: SYSAUX
Owner Segment Name Par Seg Type Bytes(K) Used(K)
-------------- ----------------------------- ------- --------- --------- ------------
CTXSYS DR$CLASS NULL TABLE 64 64
CTXSYS DR$INDEX NULL TABLE 64 64
CTXSYS DR$INDEX_ERROR NULL TABLE 64 24
CTXSYS DR$INDEX_PARTITION NULL TABLE 64 24
CTXSYS DR$INDEX_SET NULL TABLE 64 64
CTXSYS DR$INDEX_SET_INDEX NULL TABLE 64 24
CTXSYS DR$INDEX_VALUE NULL TABLE 64 64
CTXSYS DR$NUMBER_SEQUENCE NULL TABLE 64 64
CTXSYS DR$NVTAB NULL TABLE 64 24
CTXSYS DR$OBJECT NULL TABLE 64 64
CTXSYS DR$OBJECT_ATTRIBUTE NULL TABLE 64 64
CTXSYS DR$OBJECT_ATTRIBUTE_LOV NULL TABLE 64 64
CTXSYS DR$POLICY_TAB NULL TABLE 64 24
CTXSYS DR$PREFERENCE NULL TABLE 64 64
CTXSYS DR$PREFERENCE_VALUE NULL TABLE 64 64
CTXSYS DR$SECTION NULL TABLE 64 24
CTXSYS DR$SECTION_GROUP NULL TABLE 64 64
CTXSYS DR$STATS NULL TABLE 64 24
CTXSYS DR$STOPLIST NULL TABLE 64 64
CTXSYS DR$SUB_LEXER NULL TABLE 64 24
CTXSYS DR$THS NULL TABLE 64 24
CTXSYS DR$THS_BT NULL TABLE 64 24
CTXSYS DR$THS_PHRASE NULL TABLE 64 24
CTXSYS DR$WAITING NULL TABLE 64 24
CTXSYS DRC$DEL_KEY NULL INDEX 64 32
CTXSYS DRC$IDX_COLSPEC NULL INDEX 64 32
CTXSYS DRC$IDX_COLUMN NULL INDEX 64 32
CTXSYS DRC$IDX_KEY NULL INDEX 64 32
CTXSYS DRC$IXO_KEY NULL INDEX 64 32
CTXSYS DRC$IXP_KEY NULL INDEX 64 32
CTXSYS DRC$IXS_KEY NULL INDEX 64 32
CTXSYS DRC$IXS_NAME NULL INDEX 64 32
CTXSYS DRC$IXX_KEY NULL INDEX 64 32
CTXSYS DRC$OAT_KEY NULL INDEX 64 32
CTXSYS DRC$OAT_NAME NULL INDEX 64 32
...............
