分析表和索引（以得到更好的性能）

    以下我将展示如果传送参数到一个脚本中：

    ##########################################################
    ## analyze_table.sh ##
    ########################################################
    #!/bin/ksh
    # input parameter: 1: password # 2: SID
    if (($#<1)) then echo "Please enter 'oracle' user password as the first parameter !" exit 0
    fi
    if (($#<2)) then echo "Please enter instance name as the second parameter!" exit 0
    fi 

     要传入参数以执行该脚本，输入：

    $ analyze_table.sh manager oradb1 

    脚本的第一部分产生了一个analyze.sql文件，里面包含了分析表用的语句。脚本的第二部分分析全部的表：

    ###########################################################
    ## analyze_table.sh ##
    #########################################################
    sqlplus -s < oracle/$1@$2
    set heading off
    set feed off
    set pagesize 200
    set linesize 100
    spool analyze_table.sql
    select '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
    exit
    !
    sqlplus -s < oracle/$1@$2
    @./analyze_table.sql
    exit
    ! 

    以下是analyze.sql的一个例子：

    $ cat analyze.sql
    ANALYZE TABLE HIRWIN.JANUSAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT;
    ANALYZE TABLE HIRWIN.JANUSER_PROFILE ESTIMATE STATISTICS SAMPLE 10 PERCENT;
    ANALYZE TABLE APPSSYS.HIST_SYSTEM_ACTIVITY ESTIMATE STATISTICS SAMPLE 10 PERCENT;
    ANALYZE TABLE HTOMEH.QUEST_IM_VERSION ESTIMATE STATISTICS SAMPLE 10 PERCENT;
    ANALYZE TABLE JSTENZEL.HIST_SYS_ACT_0615 ESTIMATE STATISTICS SAMPLE 10 PERCENT;
    ANALYZE TABLE JSTENZEL.HISTORY_SYSTEM_0614 ESTIMATE STATISTICS SAMPLE 10 PERCENT;
    ANALYZE TABLE JSTENZEL.CALC_SUMMARY3 ESTIMATE STATISTICS SAMPLE 10 PERCENT;
    ANALYZE TABLE IMON.QUEST_IM_LOCK_TREE ESTIMATE STATISTICS SAMPLE 10 PERCENT;
    ANALYZE TABLE APPSSYS.HIST_USAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT;
    ANALYZE TABLE PATROL.P$LOCKCONFLICTTX ESTIMATE STATISTICS SAMPLE 10 PERCENT;
     

    检查表空间的使用

    以下的脚本检测表空间的使用。如果表空间只剩下10%，它将会发送一个警告email。

    ####################################################
    ## ck_tbsp.sh ##
    ##############################################
    #!/bin/ksh
    sqlplus -s < oracle/$1@$2
    set feed off
    set linesize 100
    set pagesize 200
    spool tablespace.alert
    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)) < 10;
    spool off
    exit
    !
    if [ `cat tablespace.alert|wc -l` -gt 0 ]
    then
    cat tablespace.alert -l tablespace.alert > tablespace.tmp
    mailx -s "TABLESPACE ALERT for ${2}" $DBALIST < tablespace.tmp
    fi

   警告email输出的例子如下：

    TABLESPACE_NAME USED (MB) FREE (MB) TOTAL (MB) PER_FREE
    ------------------- --------- ----------- ------------------- ------------------
    SYSTEM 2,047 203 2,250 9 %
    STBS01 302 25 327 8 %
    STBS02 241 11 252 4 %
    STBS03 233 19 252 8 %

    查找出无效的数据库对象

    以下查找出无效的数据库对象：

    #################################################
    ## ck_tbsp.sh ##
    #################################################
    #!/bin/ksh
    sqlplus -s < oracle/$1@$2
    set feed off
    set linesize 100
    set pagesize 200
    spool tablespace.alert
    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)) < 10;
    spool off
    exit
    !
    if [ `cat tablespace.alert|wc -l` -gt 0 ]
    then
    cat tablespace.alert -l tablespace.alert > tablespace.tmp
    mailx -s "TABLESPACE ALERT for ${2}" $DBALIST < tablespace.tmp
    fi

    监视用户和事务（死锁等）

    以下的脚本在死锁发生的时候发送一个警告e-mail：

  ########################################################
    ## deadlock_alert.sh ##
    ######################################################
    #!/bin/ksh
    .. /etc/oracle.profile
    sqlplus -s < oracle/$1@$2
    set feed off
    set heading off
    spool deadlock.alert
    SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
    DECODE(REQUEST, 0, 'NO','YES' ) WAITER
    FROM V$LOCK
    WHERE REQUEST > 0 OR BLOCK > 0
    ORDER BY block DESC;
    spool off
    exit
    !
    if [ `cat deadlock.alert|wc -l` -gt 0 ]
    then
    mailx -s "DEADLOCK ALERT for ${2}" $DBALIST < deadlock.alert
    fi

    结论

    0,20,40 7-17 * * 1-5 /dba/scripts/ckinstance.sh > /dev/null 2>&1
    0,20,40 7-17 * * 1-5 /dba/scripts/cklsnr.sh > /dev/null 2>&1
    0,20,40 7-17 * * 1-5 /dba/scripts/ckalertlog.sh > /dev/null 2>&1
    30 * * * 0-6 /dba/scripts/clean_arch.sh > /dev/null 2>&1
    * 5 * * 1,3 /dba/scripts/analyze_table.sh > /dev/null 2>&1
    * 5 * * 0-6 /dba/scripts/ck_tbsp.sh > /dev/null 2>&1
    * 5 * * 0-6 /dba/scripts/invalid_object_alert.sh > /dev/null 2>&1
    0,20,40 7-17 * * 1-5 /dba/scripts/deadlock_alert.sh > /dev/null 2>&1

    通过以上的脚本，可大大减轻你的工作。你可以使用这些是来做更重要的工作，例如性能调整。