dbms_redefinition mit virtuellen Spalten

Nach längerer Zeit mal wieder etwas selbst Gebasteltes: im OTN Forum General Database Discussions wurde kürzlich ein Fall vorgestellt, in dem die Verschiebung einer Tabelle in einen anderen Tablespace mit Hilfe von dbms_redefinition.redef_table in einen Fehler führt, weil der vereinfachte automatisierte Prozess versucht, eine virtuelle Spalte durch ein Insert zu befüllen. Dazu ein Beispiel, das den OTN Fall noch mal reduziert:

drop table t;
drop table t_int;

create table t (
    id number
  , col_1 number
  , col_2 number
  , col_virtual number generated always as (nvl(col_1, col_2))
)
tablespace users
;

alter table t add constraint t_pk primary key (id);

insert into t (id, col_1, col_2)
values (1, 42, null);

commit;

select * from t;

        ID      COL_1      COL_2 COL_VIRTUAL
---------- ---------- ---------- -----------
         1         42                     42

select table_name, tablespace_name from user_tables where table_name = 'T';


TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              USERS


exec dbms_redefinition.redef_table (uname=>user, tname=>'t', table_part_tablespace=> 'EXAMPLE');  
*
ERROR at line 1:
ORA-42008: error occurred while instantiating the redefinition
ORA-12018: following error encountered during code generation for "C##TEST"."REDEF$_T92713"
ORA-54013: INSERT operation disallowed on virtual columns
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3385
ORA-06512: at line 1

Zur Erinnerung: rdbms_redefinition überführt die Daten der Originaltabelle in eine Interim-Tabelle und tauscht die beiden Objekte intern gegeneinander aus. Änderungen (also DML-Operationen), die sich nach dem Start der Reorganisation in der Quelltabelle ergeben, werden intern vermerkt und in der Zieltabelle vor Abschluss der Umstellung nachgezogen, so dass die Operation komplett online ablaufen kann. Die Prozedur redef_table ist dabei eine vereinfachte Variante, sie bietet ein:

single interface that integrates several redefinition steps including the CAN_REDEF_TABLE Procedure, the START_REDEF_TABLE Procedure, the COPY_TABLE_DEPENDENTS Procedure and the FINISH_REDEF_TABLE Procedure. This procedure can change data storage properties including tablespaces (for table, partition, subpartition, index, LOB column), compress type (for table, partition, subpartition, index, LOB column) and STORE_AS clause for the LOB column.

Im gegebenen Fall übersieht die Prozedur aber offenbar, dass man eine virtuelle Spalte nicht einfach mit einem Insert füllen kann, sondern ausklammern muss. Um das zu erreichen, kann man die Einzelschritte, die in redef_table zusammengefasst sind, einzeln aufrufen:

create table t_int (
    id number
  , col_1 number
  , col_2 number
  , col_virtual number generated always as (nvl(col_1, col_2))
)
tablespace example
;

declare
    l_colmap varchar(512);
begin
    l_colmap := 'id, col_1, col_2';
    dbms_redefinition.start_redef_table (  
        uname => user
      , orig_table => 't'
      , int_table => 't_int'
      , col_mapping => l_colmap );

end;
/

exec dbms_redefinition.finish_redef_table ( user, 't', 't_int' );

select table_name, tablespace_name from user_tables where table_name = 'T';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              EXAMPLE

Im Rahmen der Prozedur start_redef_table kann man die interim Tabelle t_int explizit angeben (nachdem man sie zuvor passend definiert hat) und über ein col_mapping die Liste der Spalten aufführen, die übertragen werden sollen. Das ist zwar nicht ganz so komfortabel wie der redef_table-Aufruf, führt aber zum gewünschten Ergebnis.