auto_unlock_stats.sh

#############主程序#########################################

#!/usr/sbin
## Get run environment
. /oracle/.profile
## Get the base directory and other directories
DIR_HOME=$(cd "$(dirname "$0")"; pwd)


## Get the configuration
. $DIR_HOME/auto_unlock_stats.ini


## Generate need unlock and lock script for statistics
sqlplus -s "/ as sysdba" < set head off feedback off pagesize 0 linesize 300 long 300


--unlock statistics
spool $AUTO_SQL_UNLK


select distinct 'exec dbms_stats.unlock_table_stats(''TBCS'',''' ||
                table_name || ''');'
  from dba_tab_statistics
 where stattype_locked IS NOT NULL
   and owner = 'TBCS'
   and LAST_ANALYZED is null;


spool off;


--lock statistics
spool $AUTO_SQL_LOCK
select distinct 'exec dbms_stats.lock_table_stats(''TBCS'',''' ||
                table_name || ''');'
  from dba_tab_statistics
 where stattype_locked IS NULL
   and owner = 'TBCS'
   and last_ANALYZED is not null;


spool off;
EOF


## Define sqlplus function for execute unlock/lock
exec_sqlplus()
{
sqlplus -s "/as sysdba"<> ${DIR_HOME}/auto_oper.log
set pagesize 0 linesize 300 long 300
prompt  +---------------------------------Begin  ${OPERATION}: ${OPERATION_DATE}---------------------------------------------------------------------+
start $DIR_HOME/auto_${OPERATION}_stats.sql
prompt "+---------------------------------End   ${OPERATION} :${OPERATION_DATE} ---------------------------------------------------------------------+
exit
EOF
}


## Execute above generate need lock/unlock script
if [ -f ${AUTO_SQL_UNLK} -a -s ${AUTO_SQL_UNLK} ]; then  
OPERATION=unlock
OPERATION_DATE=`date +%y%m%d%h%m`
exec_sqlplus
fi
if [ -f ${AUTO_SQL_LOCK} -a -s ${AUTO_SQL_LOCK} ]; then
OPERATION=lock
OPERATION_DATE=`date +%y%m%d%h%m`
exec_sqlplus
fi


########################################辅助程序################################
#!/usr/sbin
## Get the base directory and other directories
DIR_HOME=$(cd "$(dirname "$0")"; pwd)
export AUTO_SQL_UNLK="${DIR_HOME}/auto_unlock_stats.sql" 
export AUTO_SQL_LOCK="${DIR_HOME}/auto_lock_stats.sql"

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29446986/viewspace-1703674/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29446986/viewspace-1703674/