[20170628]12C ORA-54032.txt

[20170628]12C ORA-54032.txt

https://hourim.wordpress.com/2017/06/24/ora-54032-column-to-be-renamed-is-used-in-a-virtual-column-expression/

--//重复测试:

SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t1(x number, y number, z number);
Table created.

SCOTT@test01p> alter table t1 rename column x to x_bis;
Table altered.

SCOTT@test01p> SELECT dbms_stats.create_extended_stats (ownname=> user ,tabname=> 't1' ,extension => '(x_bis,y)') ext FROM dual;
EXT
------------------------------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0

--//删除column group.
--//SCOTT@test01p> exec dbms_stats.drop_extended_stats (ownname=> user ,tabname=> 't1' ,extension => '(x_bis,y)')
--//PL/SQL procedure successfully completed.

--//顺便建立脚本放入脚本库中:
--// owner tablename column_group(using , delimiter).
SELECT dbms_stats.create_extended_stats (ownname=> nvl('&1',user) ,tabname=> '&&2' ,extension => '(&&3)') ext FROM dual;

SCOTT@test01p> alter table t1 rename column x_bis to x;
alter table t1 rename column x_bis to x
                             *
ERROR at line 1:
ORA-54032: column to be renamed is used in a virtual column expression

--//实际上这样已经是虚拟列的一部分.

D:\tools\rlwrap>oerr ora 54032
54032, 0000, "column to be renamed is used in a virtual column expression"
// *Cause:  Attempted to rename 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 renamed.

SCOTT@test01p> select column_name ,data_default ,data_length from user_tab_cols where table_name = 'T1' and hidden_column = 'YES';
COLUMN_NAME                    DATA_DEFAULT                             DATA_LENGTH
------------------------------ ---------------------------------------- -----------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0 SYS_OP_COMBINED_HASH("X_BIS","Y")                 22

--//而在12.2.0.1.0 版本这个问题消失.我没有12.2以上版本无法测试!!

SCOTT@test01p> insert into t1 values (1,2,3);
1 row created.

SCOTT@test01p> commit;
Commit complete.

SCOTT@test01p> set numw 18
SCOTT@test01p> select x_bis,y,z,SYS_STU3L40_B_DWBMIA8PMJRGR$S0 from t1;
             X_BIS                  Y                  Z SYS_STU3L40_B_DWBMIA8PMJRGR$S0
------------------ ------------------ ------------------ ------------------------------
                 1                  2                  3             298332787864732998

--//在11g下执行:
SCOTT@book> @ &r/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> select SYS_OP_COMBINED_HASH(1,2) from dual ;
SYS_OP_COMBINED_HASH(1,2)
-------------------------
       298332787864732998

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

转载于:http://blog.itpub.net/267265/viewspace-2141323/