세이자바

프로젝트에서 프로그램이나 데이터베이스 오브젝트들의 명명규칙이 정해져있는데 누군가가 그 명명규칙을 지키지 않고 개발을 했고 그것을 나중에 상당히 여러 개를 발견하면 참 답답한 마음이 든다. 더우기 그것이 데이터베이스 오브젝트라면..
자바와 같은 객체지향언어로 작성된 프로그램은 리팩토링이라는 기법을 이용해서 그런 것들을 고쳐볼 수 있지만 데이터베이스는 툴이 별로 없다. 뿐만아니라 데이터베이스 오브젝트를 아예 드롭하고 새로 만들어야 한다. 그 중에서도 테이블은 어디서 그 이름을 쓰고 있을 지 모르므로 쉬운 일이 아니다.
그에 비해 인덱스는 손을 대기가 좀 쉽다.
하지만 인덱스의 초기 크기도 적정값으로 하고 테이블 스페이스와 같은 것들을 정하면서 하려면 인덱스 하나 새로 만드는 데도 적지 않은 노력이 들어간다.
그런데 DBA 비슷한 역할을 겸하다가 감리를 앞두니 예전에 그냥 넘어가버렸던 오브젝트들이 영 신경이 쓰이는거다. ㅡㅡ;
테이블을 손대기는 무리이고 인덱스라도 손을 대기로 했다. 쩝...
하나하나 새로 만드는 스크립트를 만들까 하다가 그런 단순반복에 시간을 투자할 수는 없다는 생각에 그냥 저장프로시저를 만들어버렸다.
사실 일일이 인덱스 생성문을 만드는 것 보다 시간이 더 들었을지도 모르겠지만...

인덱스를 생성하는 것은 Primary Key 와 같이 제약조건(Constraint)과 함께 생성되는 것과 성능만을 위해 부가적으로 생성되는 것은 약간 다르게 만들어주어야 한다. 즉 제약조건으로 만들어진 것은 제약조건을 새로 만들어 주어야 하고 일반 인덱스는 인덱스 생성문을 만들어서 실행해주어야 한다. 그리고 인덱스는 컬럼의 순서도 맞게 새로 만들어주어야 한다.
그래서 우선은 인덱스에 들어가는 컬럼을 나열해주는 함수를 먼저 만들었다.

create or replace function getIndexColumns (
   in_user_name  in string,
       in_index_name in string
)
return string
is
   v_user_name  string(50);
   v_index_name string(50);
   v_col_name   string(50);
   v_desc       string(5);
   v_col_names  string(2000);
   v_comma      string(3);
   cursor c_cols is
           select column_name, descend
           from dba_ind_columns
           where index_owner = v_user_name
             and index_name  = v_index_name
           order by column_position;
begin
       v_user_name := upper(in_user_name);
       v_index_name  := upper(in_index_name);
       v_comma := '';
       OPEN c_cols;
               LOOP
                       FETCH c_cols INTO v_col_name, v_desc;
                       EXIT WHEN c_cols%NOTFOUND;
                       v_col_names := v_col_names||v_comma;
                       v_comma := ', ';
                       v_col_names := v_col_names||v_col_name;
                       IF (v_desc = 'DESC') THEN
                               v_col_names := v_col_names||' '||v_desc;
                       END IF;
               END LOOP;
       CLOSE c_cols;
       return v_col_names;
end getIndexColumns;
/

그리고 다음은 이전 인덱스 정보를 가져와서 새 이름으로 다시 만들어주는 저장프로시저를 만들었다.

CREATE OR REPLACE PROCEDURE rebuildIndex (
   in_user_name  in dba_indexes.owner%type,
   in_old_name   in dba_indexes.index_name%type,
   in_new_name   in dba_indexes.index_name%type,
   in_tablespace in dba_indexes.tablespace_name%type
)
IS
   v_user_name   dba_indexes.owner%type;
   v_old_name    dba_indexes.index_name%type;
   v_new_name    dba_indexes.index_name%type;
   v_tablespace  dba_indexes.tablespace_name%type;
   v_initext     string(20);
   v_nextext     string(20);
   v_index_cols  string(2000);
   v_table_name  string(50);
   v_cons_type   string(5);
   v_cons_name   string(50);
   v_query       string(2000);
   v_uniqueness  string(10);
BEGIN
       v_user_name := upper(in_user_name);
       v_old_name  := upper(in_old_name);
       v_new_name  := upper(in_new_name);
       select a.tablespace_name, b.bytes / 1024 ||'K' as indexsize, nvl(a. next_extent,0) as next_extent, a.table_name
         into v_tablespace, v_initext, v_nextext, v_table_name
       from dba_indexes a, dba_segments b
       where a.owner = v_user_name
         and a.index_name = v_old_name
         and a.owner = b.owner
         and a.index_name = b.segment_name
         and b.segment_type  = 'INDEX'
       order by a.index_name;

       if in_tablespace is null or in_tablespace = '' then
               v_tablespace := upper(v_tablespace);
       else
               v_tablespace := upper(in_tablespace);
       end if;
       DBMS_OUTPUT.PUT_LINE('...................................');
       v_index_cols := getIndexColumns(v_user_name, in_old_name);

       BEGIN
               select constraint_name, constraint_type
                 into v_cons_name, v_cons_type
               from dba_constraints
               where owner = v_user_name
                 and index_name = in_old_name;

               if v_cons_type = 'P' then
                       v_query := 'alter table '||v_user_name||'.'||v_table_name||' drop primary key cascade';
                       DBMS_OUTPUT.PUT_LINE(v_query);
                       execute immediate v_query;

                       v_query :=      'alter table '||v_user_name||'.'||v_table_name||' add constraint '||in_new_name||' primary key ('||v_index_cols||') '||'using index storage (initial '||v_initext||' next '||v_nextext||' MAXEXTENTS UNLIMITED) '||'tablespace '||v_tablespace;
                       DBMS_OUTPUT.PUT_LINE(v_query);
                       execute immediate v_query;
                       DBMS_OUTPUT.PUT_LINE(v_old_name||' > '||v_new_name||' changed successfully.');
               elsif v_cons_type = 'U' then
                       DBMS_OUTPUT.PUT_LINE(v_old_name||' > UNIQUE constraint not supported.');
               else
                       DBMS_OUTPUT.PUT_LINE(v_old_name||' > Not supported constraint type : '||v_cons_type);
               end if;
       EXCEPTION
               WHEN NO_DATA_FOUND THEN
                 BEGIN
                       select uniqueness into v_uniqueness
                       from dba_indexes
                       where owner = v_user_name
                         and index_name = in_old_name;

                       if(v_uniqueness = 'UNIQUE') then
                               DBMS_OUTPUT.PUT_LINE(v_old_name||' > UNIQUE index not supported.');
                       else
                               v_query := 'drop index '||v_user_name||'.'||v_old_name;
                               DBMS_OUTPUT.PUT_LINE(v_query);
                               execute immediate v_query;

                               v_query := 'CREATE INDEX '||v_user_name||'.'||in_new_name||' ON '||v_user_name||'.'||v_table_name||'  ('||v_index_cols||') TABLESPACE '||v_tablespace||' STORAGE ( INITIAL '||v_initext||' NEXT '||v_nextext||' MAXEXTENTS UNLIMITED) NOLOGGING';
                               DBMS_OUTPUT.PUT_LINE(v_query);
                               execute immediate v_query;
                               DBMS_OUTPUT.PUT_LINE(v_old_name||' > '||v_new_name||' changed successfully.');
                       end if;
                 END;
       END;
END rebuildIndex;
/

위의 두 오브젝트는 sys 로 로그인을 해야 컴파일이 되었다.
내 환경은 Oracle 9.2.X.X 였다.
system 계적으로 컴파일을 하려고 했더니 몇 몇 system 에서 허가가 난 sys 계정의 딕셔너리 뷰들이 인식이 안되는 것이었다. 그래서 어쩔 수 없이 sys 계정으로 컴파일을...

아무튼 만든 내용은 이것이다.