Analyze table oracle

vi Analyze_table.sh
#################################################################################################
## This for normal analyze,Added only after each step the following operations,
## the default is 40% of the table, all indexes of the table.
#--First enter user\/passwd\@instance,if you gotta some irregular characters,you should transfer.
#For demo enter : scott\/\"scott\@\#\$331804\"\@ucc
#--Second enter tables
#--Or nothing or add table1 or add table1,table2,......
#--Third enter tables percent?
#--Or nothing or add ESTIMATE STATISTICS SAMPLE percent? for table
#--Fourth enter indexes percent?
#--Or nothing or add ESTIMATE STATISTICS SAMPLE percent? for index
# AUTHOR : Clement Ge Mail:clement.gejun@gmail.com
#################################################################################################
#!/bin/sh
source /home/oracle/.bash_profile
paths=`pwd`
today=Analyzed_`date +%Y%m%d`
nowtime=`date +%Y_%m_%d`
sx_mail=" clement.gejun@gmail.com "
nums=$#
t1=`expr index "$1" /`
let "t2=$t1-1"
t4="'${2//,/'),UPPER('}'"
t5=${3:-40}
t6=${4:-100}
if [ $nums -lt 1 ]
  then
  "Please enter 'Oracle' user,like format: oracle\/passwd@instance as the first parameter !"
  exit 0
elif [ $nums -gt 1 ]
  then
    t7="TABLE_NAME IN (UPPER($t4))"
else
  t7="1=1"
fi
sqlplus $1 << EOF
col AC format a900
SET LINESIZE 1000
SET TERM OFF VERIFY OFF FEEDBACK OFF PAGESIZE 1000;
SPOOL $paths/ANALYTAB.sql
SELECT 'ANALYZE TABLE ' || UPPER('${1:0:$t2}') || '.' || TABLE_NAME || ' ESTIMATE STATISTICS SAMPLE ' || nvl($t5,40) || ' PERCENT FOR TABLE;'  AC FROM USER_TABLES WHERE $t7;
SPOOL OFF;
SPOOL $paths/ANALYIND.sql
SELECT 'ANALYZE TABLE ' || UPPER('${1:0:$t2}') || '.' || TABLE_NAME || ' ESTIMATE STATISTICS SAMPLE ' || nvl($t6,100) || ' PERCENT FOR ALL INDEXES;' AC FROM USER_TABLES WHERE $t7;
SPOOL OFF;
SPOOL $paths/ANALYZE_$nowtime.LOG
set timing on
set serveroutput on
@$paths/ANALYTAB.sql
@$paths/ANALYIND.sql
SPOOL OFF;
exit
!
rm -rf $paths/ANALYZE_`date -d '5 days ago' +%Y_%m_%d`.LOG
mail $sx_mail -s $today_'Table Analyzed : ' < $paths/ANALYZE_$nowtime.LOG

作者“Clement Ge 的专栏”