ITPub博客

首页 > 数据库 > Oracle > [20200120]ORA-54033 ORA-30556.txt

[20200120]ORA-54033 ORA-30556.txt

原创 Oracle 作者:lfree 时间:2020-01-20 10:50:35 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2674074/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2675
  • 访问量
    6433475