[ORACLE] Object변경 작업(dependency check, lock)

1. Object 변경작업시 고려해야 할 상황

1. 사용량이 많은 Table에 column을 추가하려고 할때 ORA-0054: resource busy and acquire with NOWATI specifed 에러가 발생하거나 행이걸린 상황이 발생할 수 있다.
   
   
2. alter 명령어는 dynamic sql로 밖에 실행을 할 수 없기 떄문에, 이 부분에서 hard parsing을 유발시킨다.
   
   
3. Table 변경의 가장 큰 문제는 작업 테이블과 연관된 Object들이 Invaild 상태로 빠졋을 경우 recompile 해주는 작업이다. 

2. Object 변경 작업 전 (dependency check)

SELECT decode(name, UPPER('&&object_name'), 'REF', 'REF BY') ref, d.*
  FROM dba_dependencies d
 WHERE name=UPPER('&&object_name') OR referenced_name=UPPER('&&object_name')
 ORDER BY ref, owner, type, name, referenced_owner, referenced_type, referenced_name;
 
 SELECT owner, object_name, object_type, status
  FROM dba_objects
 WHERE (owner, object_name) IN ( SELECT referenced_owner, referenced_name
                                   FROM dba_dependencies d
                                  WHERE name=UPPER('&&object_name')
                                  UNION ALL
                                 SELECT owner, name
                                   FROM dba_dependencies d
                                  WHERE referenced_name=UPPER('&&object_name') )
 ORDER BY owner, status desc, object_type desc, object_name;

• Dependcy check query로 관련된 Object를 추릴수 있지만, 연관된 Object는 또 다른 Object들과 얽혀있으므로 정확하게 파악하기는 힘들다.

 

3. 주의사항

1. Table 변경작업은 변경된 테이블 뿐만 아니라 그 테이블을 사용하고 있는 Object(ex> procedure, function), 또 그와 연관된 모은 SQL PLAN이 invaild되므로, Hard parsing이 되어야 하는 부하를 준다.

 

2. Compile당시 누군가 쓰고 있으면 library cache pin경합으로 DB Hang까지 유발시킬 수 있다.

 

4. 빈번한 사용으로 인해 수동으로 Object 변경이 힘들 때

Column 추가를 계속해서 시도하는 작업

procedure나 function에 의해 빈번하게 호출되어 사용되는 Table은 수동으로 변경작업이 힘들다.

DESC SCOTT.TEST ;
set serveroutput on
declare
    nCnt number;
    strSQL varchar2(1000);
begin
    nCnt := 0;

    strSQL := 'alter table SCOTT.TEST add (';
    strSQL := strSQL || '  CUSTOM01 VARCHAR2(100),';
    strSQL := strSQL || '  CUSTOM02 VARCHAR2(100),';
    strSQL := strSQL || '  CUSTOM03 VARCHAR2(100))';
 
    for i in 1 .. 100000 loop
        BEGIN
 
           EXECUTE IMMEDIATE strSQL;
        EXCEPTION
           WHEN others THEN
              if (i mod 500) = 0 then
                 SYS.DBMS_LOCK.SLEEP(1);
                 sys.dbms_system.ksdwrt(2,'ORA-08000 alter error TEST '||SQLERRM);
              end if; 
              NULL;
        END;
 
        select count(*) into nCnt
          from dba_tab_columns
         where table_name='TEST'
           and column_name like 'CUSTOM%';

        if nCnt > 0 then
           sys.dbms_system.ksdwrt(2,'ORA-07000 alter success TEST');
           goto job_end;
           EXIT;
        end if;
    end loop;
    <>
   null;
end;
/

DESC SCOTT.TEST ;