procedures 백업하기

먼저 오라클 클럽에 올라와 있는 글을 조금 참조했음을 알려드립니다.

 ==> 본인의 글 입니다. 이 글을 다른곳에 게재하는 경우 본문의 출처를 밝혀주시기 바람니다. 

procedures_backup.sql

---------------------------------------

SET SERVEROUTPUT ON SIZE 1000000
SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON PAGES 0 LINES 512
SET TERMOUT OFF
SET TERMOUT ON
!rm -rf './proc_backup';
!mkdir './proc_backup';
PROMPT
PROMPT PROCEDURES export Start...!!!!
PROMPT
SET TERMOUT OFF
SPOOL procedures_exp.sql
PROMPT SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF PAGES 0
--PROMPT SET LINESIZE 512;
--LINES 512

DECLARE
    TYPE objectElements IS VARRAY(5) OF VARCHAR2(20);
    VObjectType objectElements	:= objectElements('PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'TYPE');
    vObjectExt  objectElements	:= objectElements('prc', 'spc', 'bdy', 'fnc', 'tps');
	vObjectName	VARCHAR2(50)	:= '';
	vExportDir	VARCHAR2(20)	:= './proc_backup/';
	--vFileHandle	UTL_FILE.FILE_TYPE;
    
    CURSOR cur_procedures(v_object_type VARCHAR2)
    IS
        SELECT  name  --, DBMS_METADATA.GET_DDL(v_object_type, name) text
        FROM    USER_SOURCE
        WHERE   TYPE = v_object_type
        GROUP   BY name;

	--
	-- DBA 권한시 수정하여 사용.(procedures_dump.sql 파일도 수정) 
	--
	/*
    CURSOR cur_procedures(v_owner VARCHAR2, v_object_type VARCHAR2)
    IS
		SELECT  object_name name --, DBMS_METADATA.GET_DDL(v_object_type, object_name, owner) 
		FROM	dba_objects
		WHERE	owner	 	= v_owner
		AND		object_type = v_object_type
		ORDER BY owner
	*/
        
BEGIN
	--DBMS_OUTPUT.ENABLE('10000000000');
     
	FOR i IN VObjectType.FIRST .. VObjectType.LAST
    LOOP
        FOR list_procedures IN cur_procedures(VObjectType(i))
        LOOP
			vObjectName := LOWER(list_procedures.name || '.' || vObjectExt(i));
			DBMS_OUTPUT.put_line('SET TERMOUT ON');
			DBMS_OUTPUT.put_line('PROMPT ' || VObjectType(i) || ' -->> ' || vExportDir || vObjectName);
			DBMS_OUTPUT.put_line('SET TERMOUT OFF');
			DBMS_OUTPUT.put_line('spool ' || vExportDir || vObjectName);
			DBMS_OUTPUT.put_line('@procedures_dump ' || VObjectType(i) || ' ' || list_procedures.name);
			DBMS_OUTPUT.put_line('spool off');

			--vFileHandle := UTL_FILE.fopen(vExportDir, list_procedures.name || '.' || vObjectExt(i), 'W'); 
			--UTL_FILE.put_line(vFileHandle, list_procedures.text); 
			--UTL_FILE.fclose(vFileHandle); 
        END LOOP;
	END LOOP;
	
/*
EXCEPTION 
	WHEN NO_DATA_FOUND THEN 
		DBMS_OUTPUT.put_line('NO_DATA_FOUND');
		UTL_FILE.fclose(vFileHandle); 
		NULL;
	WHEN UTL_FILE.INVALID_PATH THEN 
		DBMS_OUTPUT.put_line('UTL_FILE.INVALID_PATH'); 
		UTL_FILE.fclose(vFileHandle); 
	WHEN UTL_FILE.READ_ERROR THEN 
		DBMS_OUTPUT.put_line(' UTL_FILE.READ_ERROR'); 
		UTL_FILE.fclose(vFileHandle); 
	WHEN UTL_FILE.WRITE_ERROR THEN 
		DBMS_OUTPUT.put_line('UTL_FILE.WRITE_ERROR'); 
		UTL_FILE.fclose(vFileHandle); 
	WHEN OTHERS THEN 
		DBMS_OUTPUT.put_line('OTHER STUFF'); 
		UTL_FILE.fclose(vFileHandle); 
*/
END;
/
SPOOL OFF;
@procedures_exp.sql;

SET TERMOUT ON
PROMPT
PROMPT PROCEDURES export End...!!!!
PROMPT
SET TERMOUT OFF

QUIT;

procedures_dump.sql 

---------------------------------------

SET SERVEROUTPUT ON SIZE 1000000
SET HEAD OFF VERIFY OFF
SET LONG 1000000
COL TEXT FORMAT A132;
	SELECT  TRIM(DBMS_METADATA.GET_DDL('&1', name)) text
	FROM    USER_SOURCE
	WHERE   type = '&1'
	AND		name = '&2'
	GROUP   BY name;

/*
	SELECT  TRIM(DBMS_METADATA.GET_DDL('&2', object_name, '&1')) text
	FROM    dba_objects
	WHERE   owner       = '&1'
	AND     object_type = '&2'
	ORDER BY owner
*/
PROMPT /

procedures_backup.zip