오라클 PL/SQL 수정 시 자동 백업방법(형상관리)

오라클에서 PL/SQL 사용 시

이클립스와 같은 History 기능을 지원하지 않기 때문에

 

함수, 프로시저, 패키지 만지다가

이전 내용 까먹어서 CTRL + Z로 복원이 안되면

시원하게 코드 날려먹은 건데

 

트리거를 하나 만들어두면

테이블에 변경 내용을 쌓아주기 때문에

테이블 몇개로 버전관리가 가능해진다

 

drop trigger plsql_trigger;
drop table plsql_params purge;
drop table plsql_history cascade constraints purge;
drop table plsql_history_source cascade constraints  purge;
drop table plsql_log cascade constraints purge;

 

먼저 이전에 테이블, 트리거를 만들어 놨는데

다 갖다 버리고 새로 작성하려면

위와 같이 drop을 먼저 시켜주고

 

신규 작성의 경우에는

아래의 테이블 목록을 차례로 create 시켜준다

 

create table plsql_params (
   VERSIONS_KEPT NUMBER
);

create table plsql_history (
   TSTAMP     TIMESTAMP
  ,OWNER      VARCHAR2(128)
  ,NAME       VARCHAR2(128)
  ,TYPE       VARCHAR2(12)
  ,constraint plsql_history_pk primary key ( tstamp,owner,name,type)
)
organization index;

create table plsql_history_source (
   TSTAMP     TIMESTAMP
  ,OWNER      VARCHAR2(128)
  ,NAME       VARCHAR2(128)
  ,TYPE       VARCHAR2(12)
  ,LINE       NUMBER
  ,TEXT       VARCHAR2(4000)
  ,constraint plsql_history_source_pk primary key ( tstamp,owner,name,type,line)
  ,constraint plsql_history_source_fk foreign key ( tstamp,owner,name,type) references plsql_history ( tstamp,owner,name,type )
);

create table plsql_log
(
   TSTAMP     TIMESTAMP
  ,MSG        varchar2(1000)
);

 

테이블을 작성한 후에는 트리거를 작성해야 하는데

 

트리거명은 PLSQL_TRIGGER

트리거 유형은 SCHEMA, CREATE를 선택한 후

아래 코드를 복사 붙여넣기 후 컴파일한다

(계정명은 SCOTT 등을 말한다)

 

create or replace trigger plsql_trigger
before create on 계정명.SCHEMA
declare
  l_owner   varchar2(128) := ora_dict_obj_owner;
  l_name    varchar2(128) := ora_dict_obj_name;
  l_type    varchar2(128) := ora_dict_obj_type;
  l_archived timestamp := systimestamp;
  l_tstamp_to_clear timestamp;

  procedure logger(m varchar2) is
    pragma autonomous_transaction;
  begin
    insert into plsql_log values (systimestamp,m);
    commit;
  end;
begin
  if l_type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') then
     insert into plsql_history values (l_archived, l_owner, l_name, l_type);

     insert into plsql_history_source
     select l_archived, owner, name, type, line, text
     from   all_source
     where  owner = l_owner
     and    name = l_name
     and    type = l_type;

     logger('Archived '||l_type||'-'||l_owner||'.'||l_name);

     select max(case when tot > versions_kept and seq = tot - versions_kept then tstamp end)
     into   l_tstamp_to_clear
     from   plsql_params,
            ( select ph.*,
                     row_number() over ( order by tstamp ) as seq,
                     count(*) over () as tot
              from plsql_history ph
            )
     where  owner = l_owner
     and    name = l_name
     and    type = l_type;

     if l_tstamp_to_clear is not null then
       logger('Clearance timestamp for '||l_type||'-'||l_owner||'.'||l_name||' is '||l_tstamp_to_clear);

       delete from plsql_history_source where tstamp <= l_tstamp_to_clear;
       delete from plsql_history        where tstamp <= l_tstamp_to_clear;
	   logger('Cleared '||sql%rowcount||' versions for '||l_type||'-'||l_owner||'.'||l_name);
     end if;
  end if;
end;

 

트리거까지 작성한 후 PL/SQL을 생성, 저장해보면

PLSQL_HISTORY, PLSQL_HISTORY_SOURCE, PLSQL_LOG

세 테이블에 데이터가 삽입되게 되는데

 

PLSQL_HISTORY는 특정 시간에

어느 함수, 프로시저, 패키지를 수정했는지

 

PLSQL_HISTORY_SOURCE는

수정한 내용이 쌓이게 되는데

 

실제 버전 관리 시에는

PLSQL_HISTORY 테이블에서 시간과 이름을 확인한 뒤에

 

PLSQL_HISTORY_SOURCE 테이블에서

아까 확인한 시간으로 WHERE 조건을 넣어주면

특정 시간에 어떻게 변경했는지 버전 관리가 가능해지게 된다

 

예전 내용으로 변경하려면

text 값을 모두 복사한 뒤에 메모장으로 옮기면

앞에 "가 생긴 것이 보이는데

이건 드래그해서 지우거나 공백으로 변환시켜 사용하면 된다