[Oracle] DB12c 에서 ORA-54032, ORA-54033 컬럼 이름변경 및 삭제가 안되는 현상

1. 현상

  - DB12c 이상에서, 특정 Column 에 대한 rename column 혹은 drop column 시, ORA-54032 or ORA-54033 에러 메시지 출력

SQL> alter table OWNER.USER_MST rename column USR_ID to USER_ID;  >아래 메시지 출력  "ORA-54032: column to be renamed is used in a virtual column expression" "ORA-54032: 이름을 바꿀 열이 가상 열 표현식에서 사용되었습니다." "ORA-54033: column to be modified is used in a virtual column expression" "ORA-54033: 이름을 바꿀 열이 가상 열 표현식에서 사용되었습니다."

---

 

2. 원인

  - 해당 컬럼이 virtual column으로 만들어져 있음. 사용자가 명시적으로 안만들었더라도 12c 버전부터 새로 생긴 기능인 extended statistics 기능 중에 하나가 다수의 column에 대한 SQL optimizer statistics를 (자동으로) 생성 수집하는 것으로 이 기능 때문에 virtual column이 자동으로 생성됨.

  - extended statistics 에 대해서 기술된 내용 : This feature enables to Oracle calculate statistics on a group of columns. It uses this information to improve row estimates. This is useful when there's a correlation between the values of two (or more) columns in a table.

 

  - SYS_OP_COMBINED_HASH 은 an undocumented feature 라고 합니다. The name implies Oracle is merging the arguments together to form a hash 라고 기술되어 있습니다.

  

  - 확인 방법

SQL> select OWNER, table_name, column_name, data_type, hidden_column, data_default         from dba_tab_cols         where table_name ='[TABLE_NAME]' and virtual_column = 'YES' ;

data_default 컬럼에 해당 column 이 한개라도 포함되어 있다면, ORA-54032, ORA-54033 메시지와 함께 변경 되지 않음.

 

---

 

3. 조치방법

  3-1. 해당 extended statistics 컬럼을 삭제

 

  - 아래 쿼리를 통해 삭제 가능

SQL> exec dbms_stats.drop_extended_stats( ownname => 'OWNER', tabname => '[TABLE_NAME]', extension => '("USER_NM","USER_PWD","USR_ID")' ) ;

 

SQL> exec dbms_stats.drop_extended_stats(OWNER, '[TABLE_NAME]', '(USER_NM, USER_PWD, USR_ID)');

 

  - 아래 쿼리를 통해 생성 가능 (필요하다고 판단할 경우, DB 가 다시 생성)

SQL> exec dbms_stats.create_extended_stats( ownname => 'OWNER', tabname => '[TABLE_NAME]', extension => '("USER_NM","USER_PWD","USR_ID")' ) ;

 

  3-2. 해당 Table 을 재생성 

SQL> alter table OWNER.USER_MST drop primary key cascade ;   SQL> drop table OWNER.USER_MST cascade constraints ; SQL> create table OWNER.USER_MST ( ---- ) ;