오라클 패키지 백업 스크립트

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;