오라클 패키지 백업 스크립트
1. pkg_backup.sh 파일내용
### 오라클 환경 설정(.profile 참조)
###
sqlplus -SILENT 아뒤/패스워드
@/oracle/backup_pkg/get_pkg_name.sql > names.txt
yd=`date +%Y%m%d`
mkdir -p /oracle/backup_pkg/pkgs/$yd
for get_name in `cat names.txt`
do
echo "$get_name Package Header Backup"
echo "set heading off ;" > get_pkg.sql
echo "set linesize 10000;" >> get_pkg.sql
echo "set pagesize 50000; " >> get_pkg.sql
echo "set feedback off; " >> get_pkg.sql
echo "set verify off;" >> get_pkg.sql
echo "SELECT TEXT FROM ALL_SOURCE " >> get_pkg.sql
echo "WHERE TYPE='PACKAGE'" >> get_pkg.sql
echo "AND OWNER=''" >> get_pkg.sql
echo "AND NAME='$get_name';" >> get_pkg.sql
echo "exit; " >> get_pkg.sql
chmod +x get_pkg.sql
echo "CREATE OR REPLACE " > /oracle/backup_pkg/pkgs/$yd/$get_name.pks
sqlplus -SILENT 아뒤/패스워드 @get_pkg.sql >> /oracle/backup_pkg/pkgs/$yd/$get_name.pks
echo "$get_name Package Body Backup"
echo "set heading off ;" > get_pkg.sql
echo "set linesize 10000;" >> get_pkg.sql
echo "set pagesize 50000; " >> get_pkg.sql
echo "set feedback off; " >> get_pkg.sql
echo "set verify off;" >> get_pkg.sql
echo "SELECT TEXT FROM ALL_SOURCE " >> get_pkg.sql
echo "WHERE TYPE='PACKAGE BODY'" >> get_pkg.sql
echo "AND OWNER=''" >> get_pkg.sql
echo "AND NAME='$get_name';" >> get_pkg.sql
echo "exit; " >> get_pkg.sql
chmod +x get_pkg.sql
echo "CREATE OR REPLACE " > /oracle/backup_pkg/pkgs/$yd/$get_name.pkb
sqlplus -SILENT 아뒤/패스워드 @get_pkg.sql >> /oracle/backup_pkg/pkgs/$yd/$get_name.pkb
done
cd -
2. get_pkg_name.sql 파일내용
-- 헤더 정보 출력되지 않게 설정
set heading off;
-- 페이지 사이즈를 최대한으로
set pagesize 10000;
-- 출력되는 행수 삭제
set feedback off;
set verify off;
-- 패키지의 이름을 가지고 옴
SELECT NAME FROM ALL_SOURCE
WHERE TYPE='PACKAGE'
AND OWNER=''
GROUP BY NAME;
-- 종료
exit;