[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