1. 表空间使用率
SQL> select a.tablespace_name, 2 round(a.total_size) "total_size M", 3 round(a.total_size)-round(b.free_size,3) "used M", 4 round(b.free_size ,3) "free_size M", 5 round(b.free_size/total_size *100,2) ||'%' free_rate 6 from (select tablespace_name,sum(bytes) /1024/1024 total_size 7 from dba_data_files group by tablespace_name) a, 8 (select tablespace_name,sum(bytes)/1024/1024 free_size 9 from dba_free_space group by tablespace_name) b 10 where a.tablespace_name=b.tablespace_name(+);TABLESPACE_NAME total_size M used M free_size M FREE_RATE------------------------------ ------------ ---------- ----------- -----------------------------------------SYSAUX 530 494.312 35.688 6.73%UNDOTBS1 195 67.625 127.375 65.32%USERS 114 108.562 5.438 4.78%SYSTEM 1060 1002.187 57.813 5.45%EXAMPLE 127 103.562 23.438 18.47%TEST01 300 1 299 99.67%
2. 检查监听程序的配置
#!/bin/bashexport TMP=/tmpexport TMPDIR=$TMPexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;export ORACLE_SID=prodexport ORACLE_TERM=xtermexport PATH=/usr/sbin:$PATHexport PATH=$ORACLE_HOME/bin:$PATH; export PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;lsn=`netstat -an |grep :1521 |head -1 |awk '{print $4}'|cut -c9-12`if [ "$lsn" = '1521' ] ;then echo "Current Listener Port is : $lsn" echo 'listener is up !'else echo "This `hostname` listener is down !" echo "Starting listener ...." lsnrctl startfi3. 检查失效对象
#######################################################################invalid_object_alert.sh######################################################################!/bin/kshEDITOR=vi; export EDITORORACLE_SID=prod; export ORACLE_SIDORACLE_BASE=/u01/app/oracle; export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOMELD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATHTNS_ADMIN=/var/opt/oracle;export TNS_ADMINNLS_LANG=american; export NLS_LANGNLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export NLS_DATE_FORMATORATAB=/etc/oratab;export ORATABPATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; export PATHDBALIST="oracle,root";export DBALISTsqlplus -s '/ as sysdba' <<EOFset feed offset heading offcolumn OWNER format a10column OBJECT_NAME format a35column OBJECT_TYPE format a10column STATUS format a10spool invalid_object.alertSELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;spool offexitEOFif [ `cat invalid_object.alert | wc -l` -gt 0 ] thenmailx -s "INVALID OBJECTS for prod" $DBALIST < invalid_object.alertfi
4. 检查实例是否启动
#!/bin/bash#chkinst.sh##################################################################### ckinstance.ksh #####################################################################ORATAB=/etc/oratabecho "`date` "echo "Oracle Database(s) Status `hostname`"db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "^#" | grep -v "^*"`pslist="`ps -ef | grep pmon`"for i in $dbdoecho "$pslist" | grep "ora_pmon_$i" > /dev/null 2>&1if (( $? )); thenecho "Oracle Instance - $i: Down"elseecho "Oracle Instance - $i: Up"fidone
5.检查死锁
##################################################################### deadlock_alert.sh ######################################################################!/bin/kshEDITOR=vi; export EDITORORACLE_SID=prod; export ORACLE_SIDORACLE_BASE=/u01/app/oracle; export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOMELD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATHTNS_ADMIN=/var/opt/oracle;export TNS_ADMINNLS_LANG=american; export NLS_LANGNLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export NLS_DATE_FORMATORATAB=/etc/oratab;export ORATABPATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; export PATHDBALIST="oracle,root";export DBALISTsqlplus -s '/ as sysdba' <<EOFset feed offset heading offspool deadlock.alertSELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,DECODE(REQUEST, 0, 'NO','YES' ) WAITERFROM V\$LOCKWHERE REQUEST > 0 OR BLOCK > 0ORDER BY block DESC;spool offexitEOFif [ `cat deadlock.alert | wc -l` -gt 0 ]thenmailx -s "DEADLOCK ALERT for prod" $DBALIST < deadlock.alertfi5.检查表空间使用率的 shell脚本
#!/bin/bashsqlplus -s '/as sysdba' <<EOFset feedback offset echo onset linesize 100set pagesize 200column "USED (MB)" format a10column "FREE (MB)" format a10column "TOTAL (MB)" format a10column PER_FREE format a10spool /home/oracle/tablespace.more append select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "TIME:" from dual;select a.name "DB_NAME",b.instance_name "INSTANCE_NAME" from v\$database a,v\$instance b ; SELECT F.TABLESPACE_NAME, TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)", TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)", TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)", TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS*(SELECT VALUE/1024 FROM V\$PARAMETER WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 80;spool offexit;EOF6.取样分析对象set heading offset feedback offset pagesize 200set linesize 100set echo offset time offset timing offset term offset trimspool onspool analyze_table.sqlselect 'ANALYZE TABLE ' || owner || '.' || segment_name || ' ESTIMATE STATISTICS SAMPLE 10 PERCENT;' from dba_segments where segment_type = 'TABLE' and owner not in ('SYS', 'SYSTEM');spool off
转载于:https://www.cnblogs.com/iyoume2008/p/4824207.html
相关资源：sql跟踪语句整理工具