테이블 통계 자동 수집 및 백업 쉘 스크립트

#!/bin/bash

## 변수 입력 : 수행 환경에 맞게 수정
DATE=$(date +%Y%m%d)
SCHEMA=[[스키마명]]
TBL_TBS=[[테이블용 테이블스페이스명]]
IDX_TBS=[[인덱스용 테이블스페이스명]]
OLD_TBL=STAT_TABLE_"$DATE"_BAK
NEW_TBL=STAT_TABLE_"$DATE"_NEW

## 실행부 : 오라클 system 계정 암호 필요
sqlplus -s  << EOF1
system/oracle
set heading off
set verify off
set lines 200
set pages 10000

-- 스풀 파일명 & 병렬 처리를 위한 75%의 CPU 수 추출
col td_date new_value filedt
col td_cpu new_value cpucnt
select 'stat_table_'||to_char(sysdate,'RRMMDD_HH24MI') td_date, round(value*.75,0) td_cpu from v\$parameter where name = 'cpu_count';

spool table_stats.sql

-- 통계가 없거나 stale 상태인 테이블의 통계 수집을 위한 SQL 스크립트 생성
select 'prompt -- gather table stats for '||OWNER||'.'||TABLE_NAME||CHR(10)||
       'exec dbms_stats.export_table_stats(ownname=>'''||OWNER||''', tabname=>'''||TABLE_NAME||''', stattab=>''$OLD_TBL'');'||CHR(10)||
       'exec dbms_stats.gather_table_stats(ownname=>'''||OWNER||''', tabname=>'''||TABLE_NAME||''', degree=>&cpucnt.);'||CHR(10)|| 
       'exec dbms_stats.export_table_stats(ownname=>'''||OWNER||''', tabname=>'''||TABLE_NAME||''', stattab=>''$NEW_TBL'');'
  from DBA_TAB_STATISTICS
 where OWNER = '$SCHEMA'
   and (LAST_ANALYZED is null or STALE_STATS = 'YES');

spool off

/*
-- prompt
-- prompt #####################################
-- prompt ## drop existing backup tables
-- drop table $SCHEMA.$OLD_TBL purge;
-- drop table $SCHEMA.$NEW_TBL purge;
*/

spool &filedt..log

prompt
prompt #####################################
prompt ## create old statistics backup table
exec dbms_stats.create_stat_table(ownname=>'$SCHEMA', stattab=>'$OLD_TBL', tblspace=>'$TBL_TBS');
alter index $SCHEMA.$OLD_TBL rebuild tablespace $IDX_TBS;

prompt
prompt #####################################
prompt ## create new statistics backup table
exec dbms_stats.create_stat_table(ownname=>'$SCHEMA', stattab=>'$NEW_TBL', tblspace=>'$TBL_TBS');
alter index $SCHEMA.$NEW_TBL rebuild tablespace $IDX_TBS;

prompt
prompt #####################################
prompt ## backup and gather table statistics
prompt

@table_stats.sql

spool off

exit
EOF1