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 shoshould 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
The author's "Clement Ge's column"