Récemment, deux clients différents en 12.1.0.2 ont rencontré l’erreur ORA-54032 en tentant de renommer une colonne d’une table, alors qu’il n’y avait pas de colonne virtuelle explicite qui utilisait la colonne à renommer.
En version 12.1.0.1 et 12.1.0.2, Oracle considère qu’il s’agit d’un bug (Bug 21070487 – ORA-54032: column to be renamed is used in a virtual column expression (Doc ID 21070487.8)) mais cette erreur se produisait déjà en 11.2.0.4.
Exemple :
SQL> create table t 2 ( 3 x number, 4 y number, 5 z number as (x+y) 6 ); Table T created. SQL> select column_name,data_default from user_tab_columns where table_name = 'T'; COLUMN_NAME DATA_DEFAULT ------------ ------------ X Y Z "X"+"Y" SQL> alter table t rename column x to w; Error starting at line : 15 in command - alter table t rename column x to w Error report - ORA-54032: column to be renamed is used in a virtual column expression 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.
Il est intéressant de noter que le problème ne se pose pas avec les expressions utilisées dans un Function Based Index :
SQL> create table t 2 ( 3 x number, 4 y number 5 ); Table T created. SQL> create index i on t(x+y); Index I created. SQL> select column_name,data_default from user_tab_cols 2 where table_name = 'T'; COLUMN_NAME DATA_DEFAULT ------------ ------------ X Y SYS_NC00003$ "X"+"Y" SQL> select column_expression from user_ind_expressions 2 where table_name = 'T' and index_name = 'I'; COLUMN_EXPRESSION -------------------- "X"+"Y" SQL> alter table t rename column x to w; Table T altered. SQL> select column_name,data_default from user_tab_cols 2 where table_name = 'T'; COLUMN_NAME DATA_DEFAULT ------------ ------------ X Y SYS_NC00003$ "W"+"Y" SQL> select column_expression from user_ind_expressions 2 where table_name = 'T' and index_name = 'I'; COLUMN_EXPRESSION -------------------- "W"+"Y"
La colonne a bien été renommée par Oracle dans l’expression.
En 12.1, la situation est aggravée par le fait que la collecte automatique des statistiques crée des colonnes virtuelles afin d’obtenir des statistiques plus précises lorsqu’il y a une corrélation entre deux colonnes utilisées simultanément dans une clause WHERE. Ces statistiques étendues permettent normalement à l’optimiseur de choisir de meilleurs plans d’exécution, ce qui est a priori une bonne chose.
Mais du coup, les colonnes impliquées dans ces statistiques ne peuvent plus être renommées ; c’est le problème rencontré chez mes deux clients.
Exemple :
SQL> create table t
2 (
3 x number,
4 y number
5 );
Table T created.
SQL> begin
2 dbms_stats.gather_table_stats
3 (
4 ownname => null,
5 tabname => 't',
6 method_opt => 'for columns (x,y) size auto'
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select column_name,data_default from user_tab_cols
2 where table_name = 'T';
COLUMN_NAME DATA_DEFAULT
------------------------------ ------------------------------
X
Y
SYS_STUYPW88OE302TFVBNC6$MMQXE SYS_OP_COMBINED_HASH("X","Y")
SQL> select extension_name, extension from user_stat_extensions
2 where table_name = 'T';
EXTENSION_NAME EXTENSION
------------------------------ ------------------------------
SYS_STUYPW88OE302TFVBNC6$MMQXE ("X","Y")
SQL> alter table t rename column x to w;
Error starting at line : 52 in command -
alter table t rename column x to w
Error report -
ORA-54032: column to be renamed is used in a virtual column expression
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.
Ce problème est corrigé en 12.2, que ce soit pour les colonnes virtuelles créées explicitement dans la table ou implicitement par Oracle lors de la collecte des statistiques. Ce problème est aussi corrigé en 12.1.0.2 par un patch disponible pour différentes plate-formes.
Sinon, il est possible de contourner le bug, en supprimant temporairement la colonne virtuelle explicite ou les statistiques étendues, le temps de renommer la colonne.
Exemple avec une colonne virtuelle explicite :
SQL> create table t 2 ( 3 x number, 4 y number, 5 z number as (x+y) 6 ); Table T created. SQL> alter table t rename column x to w; Error starting at line : 86 in command - alter table t rename column x to w Error report - ORA-54032: column to be renamed is used in a virtual column expression 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. SQL> select column_name,data_default from user_tab_columns where table_name = 'T'; COLUMN_NAME DATA_DEFAULT ------------------------------ ------------------------------ X Y Z "X"+"Y" SQL> alter table t modify z as (0); -- expression provisoire Table T altered. SQL> alter table t rename column x to w; Table T altered. SQL> alter table t modify z as (w+y); -- expression redéfinie avec le nouveau nom Table T altered. SQL> select column_name,data_default from user_tab_columns where table_name = 'T'; COLUMN_NAME DATA_DEFAULT ------------------------------ ------------------------------ W Y Z "W"+"Y"
Exemple avec des statistiques étendues :
SQL> create table t
2 (
3 x number,
4 y number
5 );
Table T created.
SQL> begin
2 dbms_stats.gather_table_stats
3 (
4 ownname => null,
5 tabname => 't',
6 method_opt => 'for columns (x,y) size auto'
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> alter table t rename column x to w;
Error starting at line : 112 in command -
alter table t rename column x to w
Error report -
ORA-54032: column to be renamed is used in a virtual column expression
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.
SQL> select column_name,data_default from user_tab_cols
2 where table_name = 'T';
COLUMN_NAME DATA_DEFAULT
------------------------------ ------------------------------
X
Y
SYS_STUYPW88OE302TFVBNC6$MMQXE SYS_OP_COMBINED_HASH("X","Y")
SQL> select extension_name, extension from user_stat_extensions
2 where table_name = 'T';
EXTENSION_NAME EXTENSION
------------------------------ ------------------------------
SYS_STUYPW88OE302TFVBNC6$MMQXE ("X","Y")
SQL> begin
2 dbms_stats.drop_extended_stats -- suppression des statistiques
3 (
4 ownname => null,
5 tabname => 't',
6 extension => '(x,y)'
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> alter table t rename column x to w;
Table T altered.
SQL> begin
2 dbms_stats.gather_table_stats -- recréation des statistiques
3 (
4 ownname => null,
5 tabname => 't',
6 method_opt => 'for columns (w,y) size auto'
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select column_name,data_default from user_tab_cols
2 where table_name = 'T';
COLUMN_NAME DATA_DEFAULT
------------------------------ ------------------------------
W
Y
SYS_STU121KAA_EH6#$T1$TT0VE6RE SYS_OP_COMBINED_HASH("W","Y")
SQL> select extension_name, extension from user_stat_extensions
2 where table_name = 'T';
EXTENSION_NAME EXTENSION
------------------------------ ------------------------------
SYS_STU121KAA_EH6#$T1$TT0VE6RE ("W","Y")
La collecte des statistiques étendues peut être désactivée à l’aide d’un paramètre caché (_optimizer_enable_extended_stats), mais ce serait dommage de se priver d’une fonctionnalité qui peut être bénéfique pour les performances.
Mon DBA 