스키마 통계 자동 수집 및 백업 쉘 스크립트

#!/bin/bash

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

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

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

set timing on
spool &filedt..log

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

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

prompt
prompt #####################################
prompt ## backup old statistics
exec dbms_stats.export_schema_stats(ownname=>'$SCHEMA', stattab=>'$BAK_TBL');

prompt
prompt #####################################
prompt ## gather schema statistics
exec dbms_stats.gather_schema_stats(ownname=>'$SCHEMA', degree=>&cpucnt.);

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 new statistics
exec dbms_stats.export_schema_stats(ownname=>'$SCHEMA', stattab=>'$NEW_TBL');

spool off

exit
EOF1