oracle巡检脚本 sh,ORACLE 巡检脚本

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_hash_value=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_hash_value=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_hash_value=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_hash_value=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.hash_value=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 hash_value=&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;