ORACLE 巡检脚本

数据库session连接数

select count(*) from v$session;

数据库的并发数

select count(*) from v$session where status='ACTIVE';

是否存在死锁
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;

是否有enqueue等待

select eq_type "lock",total_req# "gets",total_wait# "waits",cum_wait_time from v$enqueue_stat where total_wait#>0;

是否有大量长事务
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;

表空间使用率
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;

转自：http://www.cnblogs.com/ebs-blog/archive/2011/01/14/2167264.html