This discussion is archived
2 Replies Latest reply: May 7, 2012 6:54 AM by MarcoGralike RSS

Online Redefinition of Binary XML column not possible?

Michiel Weggen Newbie
Currently Being Moderated
DB version 11.2.0.1 x64 (still working on upgrading to 11.2.0.3)
 create table redef_test (
        id number(19,0) not null,
        xml XMLType,
        primary key (id)
    )
    XMLTYPE COLUMN "XML" STORE AS CLOB;

table REDEF_TEST created.

exec dbms_redefinition.can_redef_table( 'USER', 'redef_test' );

anonymous block completed


drop table redef_test;
 create table redef_test (
        id number(19,0) not null,
        xml XMLType,
        primary key (id)
    )
    XMLTYPE COLUMN "XML" STORE AS BINARY XML;

table REDEF_TEST dropped.
table REDEF_TEST created.

exec dbms_redefinition.can_redef_table( 'USER', 'redef_test' );

Error starting at line 1 in command:
exec dbms_redefinition.can_redef_table( 'USER', 'redef_test' )
Error report:
ORA-12090: Kan de tabel "USER"."REDEF_TEST" online niet opnieuw definiëren.
ORA-06512: in "SYS.DBMS_REDEFINITION", regel 139
ORA-06512: in "SYS.DBMS_REDEFINITION", regel 1782
ORA-06512: in regel 1
12090. 00000 -  "cannot online redefine table \"%s\".\"%s\""
*Cause:    An attempt was made to online redefine a table that is either a
           clustered table, AQ table, temporary table, IOT overflow table
           or table with FGA/RLS enabled.
*Action:   Do not attempt to online redefine a table that is  a
           clustered table, AQ table, temporary table, IOT overflow table
           or table with FGA/RLS enabled.
Also came across ORA-42040 on the internet while searching for a solution:

ORA-42040     cannot online redefine table "string"."string" with column of binary XML type
Cause:     Do not attempt to online redefine a table with a column of binary XML type.
Action:     An attempt was made to redefine a table with a column of binary XML type.

Why this limitation to binary xml? I was under the impression that binary xml was put forward by oracle as the 'format of the future'; yet an important feature as online redefinition cannot be used anymore it seems.

My reason for this question: I have a table that should have been partitioned (based on a virtual column derived from an xpath in the xml column) but isn't at this moment. I'm trying to use online redefinition to partition the existing table, but all the suggested ways of doing this don't seem to work.

Another thing i tried was a 'CTAS', but those seem to have problems with virtual columns.
CREATE TABLE d_new (
    id,
    xml,
    PRIMARY KEY (id)
)
XMLTYPE COLUMN xml STORE AS SECUREFILE BINARY XML
VIRTUAL COLUMNS
(
    timestamp AS (TO_TIMESTAMP(extractvalue(xml,'/e:d/c:dHeader/c:creationTime',
        'xmlns:e="http://www.example.com/myproject/schema/e/nl"
         xmlns:c="http://www.example.com/myunidoc/schema/common"'),'YYYY-MM-DD"T"HH24:MI:SS'))
)
PARTITION BY RANGE (timestamp)
(
    PARTITION p2010_09 VALUES LESS THAN (TO_DATE('1-10-2010','DD-MM-YYYY')),
    PARTITION p2012_05 VALUES LESS THAN (TO_DATE('1-6-2012','DD-MM-YYYY')),
    PARTITION px VALUES LESS THAN (MAXVALUE)
)
as
select id,xml from d_table;

Error at Command Line:40 Column:19
Error report:
SQL Error: ORA-54014: De resultatentabel van een CTAS-bewerking bevat een of meer virtuele kolommen.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points