[20200120]ORA-54033 ORA-30556.txt

[20200120]ORA-54033 ORA-30556.txt  $ oerr ora 54033 54033, 0000, "column to be modified is used in a virtual column expression" // *Cause:  Attempted to modify the data type of a column that was used in a //          virtual column expression. // *Action: Drop the virtual column first or change the virtual column //          expression to eliminate dependency on the column to be modified.  $ oerr ORA 30556 30556, 00000, "either functional or bitmap join index is defined on the column to be modified" // *Cause : An ALTER TABLE MODIFY COLUMN was issued on a column on which //          either a functional index or bitmap join index exists. // *Action: Drop the functional or bitmap join index before attempting to modify the column. --//如果定义虚拟列或者函数索引，要修改列属性时可能遇到ORA-54033 ORA-30556错误，通过例子说明： 1.环境： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> create table tx ( id1 number(10),id2 number(10),flag1 varchar2(1),flag2 varchar2(1)); Table created. SCOTT@book> insert into tx select rownum id1,rownum id2, lpad('1',1) flag1, lpad('0',1) flag2 from dual connect by level<=1e4; 10000 rows created. SCOTT@book> commit ; Commit complete. SCOTT@book> @ desc tx Name   Null? Type ------ ----- ----------- ID1          NUMBER(10) ID2          NUMBER(10) FLAG1        VARCHAR2(1) FLAG2        VARCHAR2(1) 2.测试函数索引： SCOTT@book> create index if_tx_id1 on tx(to_char(id1)); Index created. SCOTT@book> alter table tx modify id1 number (12); alter table tx modify id1 number (12)                       * ERROR at line 1: ORA-30556: either functional or bitmap join index is defined on the column to be modified SCOTT@book> drop index if_tx_id1; Index dropped. SCOTT@book> alter table tx modify id1 number (12）; Table altered. SCOTT@book> create index if_tx_id1 on tx(to_char(id1)); Index created. --//另外函数还会导致shrink space失败。 SCOTT@book> alter table tx enable ROW MOVEMENT; Table altered. SCOTT@book> alter table tx shrink space cascade; alter table tx shrink space cascade * ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object 3.测试虚拟列: SCOTT@book> @ column_group '' tx flag1,flag2 SELECT dbms_stats.create_extended_stats (ownname=> nvl('',user) ,tabname=> 'tx' ,extension => '(flag1,flag2)') c30 FROM dual exec dbms_stats.drop_extended_stats (ownname=> nvl('',user) ,tabname=> 'tx' ,extension => '(flag1,flag2)') old   1: SELECT dbms_stats.create_extended_stats (ownname=> nvl('&&1',user) ,tabname=> '&&2' ,extension => '(&&3)') c30 FROM dual new   1: SELECT dbms_stats.create_extended_stats (ownname=> nvl('',user) ,tabname=> 'tx' ,extension => '(flag1,flag2)') c30 FROM dual C30 ------------------------------ SYS_STUGVNB7PTIYWAVPJX#YT77WGD SCOTT@book> alter table tx modify flag1 varchar2(2); Table altered. --//如果修改字段类型。 SCOTT@book> alter table tx modify flag1 number(2); alter table tx modify flag1 number(2)                       * ERROR at line 1: ORA-54033: column to be modified is used in a virtual column expression SCOTT@book> exec DBMS_STATS.DROP_EXTENDED_STATS( user, 'TX','(flag1, flag2)' ); PL/SQL procedure successfully completed. SCOTT@book> alter table tx modify flag1 number(2); alter table tx modify flag1 number(2)                       * ERROR at line 1: ORA-01439: column to be modified must be empty to change datatype --//类型发生变化，不能这样修改。 4.增加一列date类型看看： SCOTT@book> alter table tx  add (c  date); Table altered. SCOTT@book> @ column_group '' tx flag1,c SELECT dbms_stats.create_extended_stats (ownname=> nvl('',user) ,tabname=> 'tx' ,extension => '(flag1,c)') c30 FROM dual exec dbms_stats.drop_extended_stats (ownname=> nvl('',user) ,tabname=> 'tx' ,extension => '(flag1,c)') old   1: SELECT dbms_stats.create_extended_stats (ownname=> nvl('&&1',user) ,tabname=> '&&2' ,extension => '(&&3)') c30 FROM dual new   1: SELECT dbms_stats.create_extended_stats (ownname=> nvl('',user) ,tabname=> 'tx' ,extension => '(flag1,c)') c30 FROM dual C30 ------------------------------ SYS_STU3J7YBQL_AJTVPTCQJKBASQ_ SCOTT@book> alter table tx modify c timestamp; alter table tx modify c timestamp                       * ERROR at line 1: ORA-54033: column to be modified is used in a virtual column expression SCOTT@book> exec DBMS_STATS.DROP_EXTENDED_STATS( user, 'TX','(flag1, c)' ); PL/SQL procedure successfully completed. SCOTT@book> alter table tx modify c timestamp; Table altered.