#!/bin/bash
# This script. is used to make healthcheck for **** DB everyday by manual
# for **** DB sever keep running normally purpose .
# The log file of running script. need to keep 7 days
# 2012-03-07
#----------by liguoyin------
export ORACLE_BASE=/home/oraprod
export ORACLE_HOME=${ORACLE_BASE}/product/10.2.0
export ORACLE_SID=CILCPROD
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
LOG_DATE=`date +%Y%m%d`
DEL_DATE=`date +%Y%m%d -d "7 day ago"`
CHECK_LOG=/home/oraprod/healthcheck/log/healthcheck_poms_${LOG_DATE}.log
rm -f /home/oraprod/healthcheck/log/healthcheck_poms_${DEL_DATE}.log
${SQLPLUS} / as sysdba< spool ${CHECK_LOG} col NAME for a30 desc v\$instance col HOST_NAME for a30 desc v\$database col name for a60 desc v\$logfile desc dba_tablespaces; desc v\$datafile desc dba_data_files desc dba_objects select owner,object_name,object_type from dba_objects where status = 'INVALID'; desc dba_rollback_segs; desc v\$resource_limit; select count(*) from v\$session; desc v\$session desc dba_tables desc all_tables set pages 80 select sql_text from (select * from v\$sqlarea order by disk_reads ) where rownum <=5; select * from (select parsing_user_id executions ,sorts,command_type,disk_reads,sql_text from v\$sqlarea order by disk_reads desc)where rownum<10; select * from (select * from v\$system_event where event not like 'SQL%' order by total_waits desc)where rownum<=5; col username for a12 set line 240 select segment_name table_name,count(*) extents from dba_segments where owner not in ('SYS','SYSTEM') group by segment_name having count(*)=(select max(count(*)) from dba_segments group by segment_name); col tablespace_name for a20 SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME", A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTS FROM V\$DATAFILE A, V\$FILESTAT B WHERE A.FILE# = B.FILE#; col sid for 999999 select spid from v\$process where addr not in (select paddr from v\$session); select table_name,num_rows,chain_cnt From dba_tables Where wner='CTAIS2' And chain_cnt<>0; Select table_name,num_rows,last_analyzed From user_tables where table_name ='DJ_NSRXX'; SELECT a.VALUE + b.VALUE logical_reads, select sum(pinhits)/sum(pins)*100 from v\$librarycache; select name,value from v\$sysstat where name like '%sort%'; select name,value from v\$sysstat where name in ('redo entries','redo buffer allocation retries'); select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE'; select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent select index_name,table_name,tablespace_name,status From dba_indexes Where wner='CTAIS2' And status<>'VALID'; SELECT owner, constraint_name, table_name, constraint_type, status SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED'; spool off
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24558279/viewspace-718724/,如需转载,请注明出处,否则将追究法律责任。
set linesize 300
set termout off
col TYPE for a50
col INSTANCE_NAME for a20
select instance_name ,host_name,startup_time,status,database_status from v\$instance;
select name , log_mode ,open_mode from v\$database;
select status ,name from v\$controlfile;
col member for a60
select group#,status ,type ,member from v\$logfile;
select tablespace_name,status from dba_tablespaces;
col name for a60
select name ,status from v\$datafile;
col file_name for a60
select file_name ,status from dba_data_files;
col owner for a20
col object_name for a25
col object_type for a20
select owner ,object_name ,object_type from dba_objects where status != 'VALID'and owner != 'SYS' and owner != 'SYSTEM';
select segment_name ,status from dba_rollback_segs;
col recource_name for a20
col limit_value for a20
col initial_allocation for a20
select resource_name ,max_utilization,initial_allocation,limit_value from v\$resource_limit;
col username for a20
col program for a30
col machine for a20
select sid ,serial#,username ,program ,machine ,status from v\$session;
col owner for a20
select distinct(owner) from dba_tables where tablespace_name ='SYSTEM' and owner != 'SYS' and owner != 'SYSTEM' and owner != 'SYS' and owner != 'SYSTEM';
col talbe_name for a20
col table_name for a20
col tablespace_name for a20
select a.table_name ,a.next_extent,a.tablespace_name from all_tables a,
(select tablespace_name,max(bytes)as big_chunk from dba_free_space group by tablespace_name)f
where f.tablespace_name = a.tablespace_name
and a.next_extent >f.big_chunk
union
select a.index_name,a.next_extent,a.tablespace_name from all_indexes a,
(select tablespace_name ,max(bytes) as big_chunk from dba_free_space
group by tablespace_name)f
where f.tablespace_name = a.tablespace_name
and a.next_extent>f.big_chunk;
set lines 120
col event for a40
select sid,event,p1,p2,p3,wait_time,seconds_in_wait from v\$session_wait where event not like 'SQL%' and event not like 'rdbms%';
col opname for a16
col progress for a8
select username,sid,opname,round(sofar*100/totalwork,0)||'%' as progress,time_remaining,sql_text from v\$session_longops,v\$sql where time_remaining <> 0 and sql_address=address and sql_hash_value=hash_value;
set verify off
col sid format 999
col pid for 999
col s_# format 999
col username for a9 heading "ora user"
col program for a29
col sql for a60
col osname format a9 heading "OS USER"
select p.pid pid,s.sid sid ,p.spid spid ,s.username username ,s.osuser osname ,p.serial# s_#,p.terminal,p.program program ,p.background , s.status ,rtrim(substr(a.sql_text,1,80)) sql from v\$process p ,v\$session s,v\$sqlarea a where p.addr=s.paddr and s.sql_address = a.address(+) and p.spid like '%&1%';
col file_name for a60
select df.tablespace_name name ,df.file_name "FILE",F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V\$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME;
col username for a10
col schemaname for a10
col osuser for a16
col machine for a16
col terminal for a20
col owner for a10
col object_name for a30
col object_type for a10
select sid,serial#,username,SCHEMANAME,osuser,MACHINE,
terminal,PROGRAM,owner,object_name,object_type,o.object_id
from dba_objects o,v\$locked_object l,v\$session s
where o.object_id=l.object_id and s.sid=l.session_id;
c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),4) hit_ratio
FROM v\$sysstat a,v\$sysstat b,v\$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads' ;
from (select tablespace_name,sum(bytes) total
from dba_free_space group by tablespace_name) A,
(select tablespace_name,sum(bytes) total
from dba_data_files group by tablespace_name) B
where A.tablespace_name=B.tablespace_name;
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';
exit
EOF