实战:oracle巡检脚本v1(七)
t heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 42.txt
select resource_name,max_utilization,initial_allocation,limit_value from v\$resource_limit;
spool off
exit;
!01
cechon "4.2 resource limit is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""resource_name","max_utilization","initial_allocation","limit_value" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 42.txt
echo
rm -rf 42.txt
#4.3 session status
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 43.txt
select sid,serial#,username,program,machine,status from v\$session;
spool off
exit;
!01
cechon "4.3 session status is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""sid","serial#","username","program" ,"machine","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 43.txt
echo
rm -rf 43.txt
#4.5 check Non-system tables in system tablespace
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 45.txt
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM';
spool off
exit;
!01
cechon "4.5 check Non-system tables in system tablespace is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""owner" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 45.txt
echo
rm -rf 45.txt
#4.6 temp tablespace usage
sqlplus -S "${ora_user}/${ora_pass} as sysdba" <
/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 46.txt
select 'the '||name||' temp tablespaces '||tablespace_name||' idle '||round(100-(s.tot_used_blocks/s.total_blocks)*100,3)||'% at '||to_char(sysdate,'yyyymmddhh24miss')
from
(select d.tablespace_name tablespace_name,
nvl(sum(used_blocks),0) tot_used_blocks,
sum(blocks) total_blocks
from v\$sort_segment v ,dba_temp_files d
where d.tablespace_name=v.tablespace_name(+)
group by d.tablespace_name) s, v\$database;
spool off
exit;
!01
cechon "4.6 check Non-system tables in system tablespace is: " red
echo
cat 46.txt
echo
rm -rf 46.txt
#4.7 database zombie processes
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
s