一、巡检项目：

1) 数据库session连接数
select count(*) from v$session;
2) 数据库的并发数
select count(*) from v$session where status='ACTIVE';
3) 是否存在死锁
set linesize 200
column oracle_username for a16
column os_user_name for a12
column object_name for a30
SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,l.process,
l.session_id,s.serial#, l.locked_mode,o.object_name
FROM v$locked_object l,dba_objects o,v$session s
where l.object_id = o.object_id and s.sid = l.session_id;
select t2.username||'   '||t2.sid||'   '||t2.serial#||'   '||t2.logon_time||'   '||t3.sql_text
      from v$locked_object t1,v$session t2,v$sqltext t3
      where t1.session_id=t2.sid 
      and t2.sql_address=t3.address
      order by t2.logon_time;
4) 是否有enqueue等待
select eq_type "lock",total_req# "gets",total_wait# "waits",cum_wait_time from v$enqueue_stat where total_wait#>0;
5) 是否有大量长事务
set linesize 200
column name for a16
column username for a10
select a.name,b.xacts,c.sid,c.serial#,c.username,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn
and b.usn=e.XIDUSN
and c.taddr=e.addr
and c.sql_address=d.ADDRESS
and c.sql_hashvalue=d.hash_value
order by a.name,c.sid,d.piece;
6)表空间使用率
set linesize 150
column file_name format a65
column tablespace_name format a20
select f.tablespace_name tablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g,
round(f.sumbytes/1024/1024/1024,2) free_g,
round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,
round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percent
from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,
(select tablespace_name,sum(bytes) sumbytes from dba_data_files group by tablespace_name) d 
where f.tablespace_name= d.tablespace_name
order by d.tablespace_name;
临时文件：
set linesize 200
column file_name format a55
column tablespace_name format a20
select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,
round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,
round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,
round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_g
from dba_temp_files a,dba_free_space b
where a.file_id = b.file_id(+)
group by a.tablespace_name,a.file_name,a.bytes
order by a.tablespace_name;
select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,
round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,
round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,
round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_g
from dba_temp_files a,dba_free_space b
where a.file_id = b.file_id(+)
group by a.tablespace_name,a.file_name,a.bytes
order by a.tablespace_name;
 
归档的生成频率:
set linesize 120
column begin_time for a26
column end_time for a26
select a.recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') begin_time,
b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,
round((b.first_time - a.first_time)*24*60,2) minutes
from v$log_history a,v$log_history b
where b.recid = a.recid+1;
 
sql读磁盘的频率:
select a.username,b.disk_reads,b.executions,
round((b.disk_reads/decode(b.executions,0,1,b.executions)),2) disk_read_ratio,b.sql_text
from dba_users a,v$sqlarea b
where a.user_id = b.parsing_user_id
and disk_reads > 5000;
Datafile I/O:
col tbs for a12;
col name for a46;
select c.tablespace_name tbs,b.name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrt
from v$filestat a,v$datafile b,dba_data_files c
where b.file# = a.file#
and b.file# = c.file_id
order by tablespace_name,a.file#;
Disk I/O
select substr(b.name,1,13) disk,c.tablespace_name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,
a.phyblkrd,a.phyblkwrt,((a.readtim/decode(a.phyrds,0,1,a.phyblkrd))/100) avg_rd_time,
((a.writetim/decode(a.phywrts,0,1,a.phyblkwrt))/100) avg_wrt_time
from v$filestat a,v$datafile b,dba_data_files c
where b.file# = a.file#
and b.file# = c.file_id
order by disk,c.tablespace_name,a.file#;
select a.username,round(b.buffer_gets/(1024*1024),2) buffer_gets_M,b.sql_text
from dba_users a,v$sqlarea b
where a.user_id = b.parsing_user_id
and b.buffer_gets > 5000000;
 
col index_name for a16;
col table_name for a18;
col column_name for a18;
select index_name,table_name,column_name,column_position from user_ind_columns
where table_name = '&tbs';
大事务：
select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,message from v$session_longops
where message like '%RMAN%';
select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,message from v$session_longops
where sofar <> totalwork;
where (sofar/totalwork)*100 < 100;
索引检查：
set linesize 200;
column index_name for a15;
column index_type for a10;
column table_name for a15;
column tablespace_name for a16;
select index_name,index_type,table_name,tablespace_name from user_indexes
where table_name ='&t';
set linesize 200;
column index_name for a26;
column table_name for a26;
column column_name for a22;
column column_position for 999;
column tablespace_name for a16;
select table_name,index_name,column_name,column_position from user_ind_columns where table_name = '&tab';
select table_name,index_name,column_name,column_position from user_ind_columns where index_name = '&ind';
select table_name,index_name,index_type,status,TABLESPACE_NAME from user_indexes where table_name = '&tab';
select table_name,index_name,index_type,status,TABLESPACE_NAME from user_indexes where index_name = '&ind';
set linesize 200;
column index_name for a20;
column table_name for a20;
select index_name,index_type,table_name,partitioned from user_indexes where index_name = '&ind';
 
等待事件：
set linesize 200
column username for a12
column program for a30
column event for a28
column p1text for a15
column p1 for 999,999,999,999,999
select s.username,s.program,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait sw
where s.sid=sw.sid and s.status='ACTIVE'
order by sw.p1;
select event,p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait
order by event;
where event = 'buffer busy waits';
select owner,segment_name,segment_type,file_id,block_id from dba_extents
where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
column event for a35;
column p1text for a40;
select sid,event,p1,p1text from v$session_wait order by event;
 
查询相关SQL：
set linesize 200
set pagesize 1000
column username for a8
column program for a36
select s.sid,s.serial#,s.username,s.program,st.sql_text 
from v$session s,v$sqltext st
where s.sql_hashvalue=st.hash_value and s.status='ACTIVE'
order by s.sid,st.piece;
select pid,spid from v$process p,v$session s
where s.sid=&sid and p.addr = s.paddr;
select s.sid,s.serial#,s.username,s.program,st.sql_text 
from v$session s,v$sqltext st,v$process ps
where s.sql_hashvalue=st.hash_value
and ps.spid=&sid and s.paddr=ps.addr
order by s.sid,st.piece;
select sql_text from v$sqltext
where hash_value in (select sql_hash_value from v$session
where paddr in (select addr from v$process
where spid=&sid))
order by piece;
select sql_text from v$sqltext
where address in (select sql_address from v$session
where paddr in (select addr from v$process
where spid=&sid))
order by piece;
select sql_text from v$sqltext
where hash_value in (select sql_hash_value from v$session where sid=&sid)
order by piece;
select sql_text from v$sqltext
where address in (select sql_address from v$session where sid=&sid)
order by piece;
select ps.addr,ps.pid,ps.spid,ps.username,ps.program,s.sid,s.username,s.program
from v$process ps,v$session s
where ps.spid=&pid
and s.paddr=ps.addr;
select s.sid,s.serial#,s.username,s.program,st.sql_text 
from v$session s,v$sqltext st,v$process ps
where s.sql_hashvalue=st.hash_value
and ps.spid='29863' and s.paddr=ps.addr
order by s.sid,st.piece;
 
column username for a12
column program for a20
select s.username,s.program,s.osuser,status
from v$session s
where s.status='ACTIVE';
 
query undotbs used percent:
set linesize 300;
select tablespace_name,segment_name,status,count(*),round(sum(bytes)/1024/1024,2) used_M from dba_undo_extents
group by tablespace_name,segment_name,status;
set linesize 300
column username for a10;
column program for a25;
select s.username,s.program,status,p.spid,st.sql_text from v$session s,v$process p,v$sqltext st where s.status='ACTIVE' and p.addr=s.paddr and st.hashvalue=s.sql_hash_value order by s.sid,st.piece;
select snap_id,dbid,instance_number,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') snap_time from stats$snapshot 
order by INSTANCE_NUMBER,SNAP_ID,SNAP_TIME;
set linesize 120;
column what form a30;
select job,log_user,what,instance from dba_jobs;
set linesize 120;
column owner for a12;
column segment_name for a24;
column segment_type for a18;
select owner,segment_name,segment_type,file_id,block_id from dba_extents 
where file_id=&file and &block between block_id and block_id + blocks - 1;
select file_id,file_name from dba_data_files where file_id = &file_id;
ANALYZE TABLE ICS_ODS_CUST_ICS_CUR partition(ICS_ODS_CUST_ICS_CUR_PART_1) VALIDATE  STRUCTURE CASCADE;
 
ANALYZE TABLE ODSDATA.&object VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
analyze index SYS_C00311764 validate structure cascade;
column owner for a12;
column segment_name for a26;
column segment_type for a16;
column tablespace_name for a20;
column bytes for 999,999,999,999;
 
select owner,segment_name,segment_type,tablespace_name,bytes,blocks,buffer_pool from dba_segments 
where segment_name='&seg'
order by bytes desc;
select segment_name,segment_type,tablespace_name,partition_name,bytes from user_segments 
where segment_name='ODSV_REC_FILE'
and segment_name in (select distinct table_name from user_part_col_statistics where table_name='ODSV_REC_FILE')
order by bytes desc;
col object_name for a26;
select object_name,object_type,status,temporary from user_objects 
where object_name = '&o';
 
set linesize 180
break on hash_value skip 1 dup
col child_number format 999 heading 'CHILD'
col operation format a82
col cost format 999999
col Kbytes format 999999
col object format a25
select hash_value,
       child_number,
       lpad(' ', 2 * depth) || operation || ' ' || options ||
       decode(id, 0, substr(optimizer, 1, 6) || ' Cost=' || to_char(cost)) operation,
       object_name object,
       cost,
       cardinality,
       round(bytes / 1024) kbytes
from v$sql_plan
where hashvalue=&hash_value
/*in
    (select a.sql_hash_value
          from v$session a, v$session_wait b
         where a.sid = b.sid and b.event = 'db file scattered read')*/
order by hash_value, child_number, id;
 
---
实例状态，表空间使用。
prompt ##### select * from v$version##################################################
select * from v$version;
---set heading on
prompt ##### select status from v$instance ###########################################
 select status from v$instance;
prompt ##### tablespace surplus proportion ###########################################
 select a.tablespace_name, substr((f.free_space / a.total) * 100,1,6)  free
 from (select tablespace_name, sum(bytes / (1024 * 1024)) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes / 1024 / 1024) free_space
from dba_free_space a
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
order by to_number(free);
 
prompt ##### temp tablespace ##########################################################
 
select b.bytes/a.bytes*100 ||'%' from 
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a ,
( select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) b;
 
prompt ##### invalid objects###########################################################
SELECT owner , object_name, object_type,status ,LAST_DDL_TIME  FROM dba_objects WHERE status like 'INVALID';
 
数据库用户状态：
prompt ##### database user status #####################################################
 
col username for a15
col account_status for a20
col default_tablespace for a10
col temporary_tablespace for a10
select a.username,
  a.account_status,
 a.default_tablespace,
 a.temporary_tablespace
from dba_users a, dba_objects b
where a.username = b.owner(+) and a.account_status='OPEN'
 group by a.username,
 a.account_status, 
 a.default_tablespace,
a.temporary_tablespace;
 
prompt ###### controlfile status ####################################################
col name for a45
col status for a4
col block_size for 999999
select status,name,block_size from v$controlfile;
prompt ##### logfile status #########################################################
col status for a7
col type for a8
col member for a45 
col is_recovery_dest_file for a5
select status,type,member,is_recovery_dest_file from v$logfile;
prompt ##### database dbid,log_mode#################################################
col name for a10
Select dbid,name,created,current_scn,log_mode From V$Database;
prompt ##### database constraint####################################################
Select constraint_type,constraint_name,table_name, status From dba_constraints Where status<>'ENABLED' AND owner not in ('SYSTEM','O
LAPSYS');
prompt ##### disabled trigger#######################################################
SELECT owner ,trigger_name,status FROM dba_triggers where status = 'DISABLED' and owner not in ('SYS','EXFSYS') AND TABLE_OWNER='SYS
';
prompt ##### disabled index ########################################################
SELECT owner ,index_name,status FROM dba_indexes where status = 'UNUSABLE';
prompt ##### temp tablespace used G ################################################
select sum(blocks)*8192/1024/1024/1024 from  v$sort_usage;
prompt ##### SGA ###################################################################
select * from 
(SELECT 1 - (phy.value-phyd.value)/( (cur.value + con.value-phyd.value)) "cache hit>90" 
   FROM v$sysstat cur, v$sysstat con, v$sysstat phy,v$sysstat phyd
  WHERE cur.name = 'db block gets' AND con.name = 'consistent gets'
    AND phy.name = 'physical reads' and phyd.NAME = 'physical reads direct' ),
(select sum(pinhits)/sum(pins) "sql library>95" from v$librarycache) ,
(select 1-SUM(GETMISSES)/SUM(GETS) "rowcache>85" from V$ROWCACHE);
 
prompt ##### tablespace sum########################################################
select nvl(t.owner,'total:') "USER",
       sum(bytes)/1024/1024/1024 "percentage"
  from dba_segments  t
 group by rollup(t.owner)
 order by 2  ;
prompt ##### memory consumption ###################################################
 col sql_txt for a45  
col buffer/exes for 99999999
col executions for 999999
   select * from (
    select trunc(t.BUFFER_GETS/t.EXECUTIONS) "buffer/exes",
    t.EXECUTIONS ,t.SQL_TEXT
    from v$sql t  where t.EXECUTIONS > 0     order by 1 desc )
where rownum <=2;
prompt ##### table don't have index###############################################
col owner for a7
col segment_name for a15
col segment_type for a10
col tablespace_name for a10
select  owner, segment_name, segment_type, tablespace_name  FROM dba_segments t  WHERE NOT EXISTS ( SELECT 'x'  FROM dba_indexes i  
WHERE t.owner = i.table_owner   AND t.segment_name = i.table_name)  AND t.segment_type IN ('TABLE', 'TABLE PARTITION')  AND t.owner 
='WHX' ORDER BY 2 DESC;
prompt #####  lock table##########################################################
col username for a10
col sid for 9999999
col serial for 99999
col logon_time for a15 
 select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logo
n_time;
spool off
 
用户和事务的死锁：
sqlplus -s < oracle/$1@$2
set feed off
set heading off
spool deadlock.alert
SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
DECODE(REQUEST, 0, 'NO','YES' ) WAITER
FROM V$LOCK 
WHERE REQUEST > 0 OR BLOCK > 0 
ORDER BY block DESC; 
spool off
 
---
linux系统资源使用情况：
echo "########################### linux###############################################" >>$FILE_NAME
echo "##### listener status ##########################################################" >>$FILE_NAME
lsnrctl status >>$FILE_NAME
echo "##### system model######################################################uname -a" >>$FILE_NAME
uname -a >>$FILE_NAME
cat /etc/redhat-release >>$FILE_NAME
echo "##### linux"
echo "##### hostname#########################################################hostname">>$FILE_NAME
hostname >>$FILE_NAME
echo "##### oracle emergency################################alert_whx.log|ora-err-fail" >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i ora- >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i err- >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i fail- >>$FILE_NAME
echo "##### db_recovery_file_dest_size used###############################alert_whx.log" >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i db_recovery_file_dest_size >>$FILE_NAME
echo "##### system space##########################################################df -k" >>$FILE_NAME
 
df -k >>$FILE_NAME
echo "##### runtime##############################################################uptime" >>$FILE_NAME
uptime >>$FILE_NAME
echo "##### system user# #################################################cat/etc/passwd" >>$FILE_NAME
tail -10  /etc/passwd >>$FILE_NAME
echo "#####  disk I/O ##########################################################sar 3 4" >>$FILE_NAME
sar 3 4 >>$FILE_NAME
echo "##### system log in###########################################################who" >>$FILE_NAME
who >>$FILE_NAME
echo "##### scheduled tast ##################################################crontab -l" >>$FILE_NAME
crontab -l >>$FILE_NAME
echo "##### cpu status#######################################################vmstat 2 4" >>$FILE_NAME
vmstat 2 4 >>$FILE_NAME
echo "##### free status ###########################################################free" >>$FILE_NAME
free >>$FILE_NAME
echo "##### disk status######################################################iostat 2 3" >>$FILE_NAME
iostat 2 3 >iostat23
head -10 iostat23 >>$FILE_NAME
echo "##### memory status###########################################################top" >>$FILE_NAME
top -b -n 1 -d 1 >top10
head -10 top10 >>$FILE_NAME
echo "##### system user group ###########################################cat /etc/group" >>$FILE_NAME
tail -10 /etc/group >>$FILE_NAME
echo "##### route ##########################################################netstat -rn" >>$FILE_NAME
netstat -rn >>$FILE_NAME
echo "#####          THE     END         ##############################################" >>$FILE_NAME