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

#!/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博客

auto_unlock_stats.sh

最新文章