[20170628]12C ORA-54032.txt
--//重复测试:
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