[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.