XMLTYPE and DBMS_REDEFINITION

This post is based on the following OTN XML DB thread: Migrating XMLType from STORE AS CLOB to STORE AS BINARY XML.

When Oracle first introduced the XMLType datatype (9.2.0.3), the default storage mechanism for it was a CLOB (aka BasicFile LOB). Note: I'm ignoring Object Relational storage since it is a table based format instead of a single column. With the release of 11.1.0.6, Oracle added a new storage type of SECUREFILE BINARY XML but left the default storage as CLOB. With the release of 11.2.0.2, Oracle changed the default storage to be SECUREFILE BINARY XML (What's New in Oracle XML DB?).

So what are the options for going from CLOB to BINARY XML storage? One is via DBMS_REDEFINITION. This may be the best option as it allows for the redefinition of the table without any system downtime. The downside to this option is that you need to have free space equal to or greater than the size of the new table as both the old and new will exist at the same time in the system. Assuming you can, here is what a simple example looks like (borrowed from myself from the OTN thread)

SQL> CREATE TABLE HOLDS_XML  -- old table structure
  2    (n_col   NUMBER(5) NOT NULL PRIMARY KEY,
  3     xml_col XMLTYPE)
  4  XMLTYPE xml_col STORE AS BASICFILE CLOB;
 
Table created
SQL> INSERT INTO holds_xml VALUES
  2  ('1',XMLTYPE('val1'));
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> CREATE TABLE HOLDS_XML_TMP  -- new table structure
  2    (n_col   NUMBER(5) NOT NULL PRIMARY KEY,
  3     xml_col XMLTYPE)
  4  XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;
 
Table created
SQL> exec DBMS_REDEFINITION.start_redef_table('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
 
PL/SQL procedure successfully completed
 
SQL>    exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
 
PL/SQL procedure successfully completed
SQL>    exec DBMS_REDEFINITION.finish_redef_table('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
 
PL/SQL procedure successfully completed
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','HOLDS_XML')
  2  FROM   dual;
 
DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
snipped...
 XMLTYPE COLUMN "XML_COL" STORE AS SECUREFILE BINARY XML (
...snipped

As you can see from the above DDL, the column is now a SECUREFILE BINARY XML. This also means that all the XML in that column has been converted to the more efficient (and smaller storage requirements) BINARY XML.

While researching this post, I came across someone mentioning the use of an ALTER TABLE statement to switch the storage formats. Here is what happens on 11.1.0.6 if we use the above table with a row in it.

SQL> ALTER TABLE JASON.HOLDS_XML
  2     MODIFY (XML_COL)
  3       XMLTYPE COLUMN XML_COL STORE AS SECUREFILE BINARY XML;
 
Table altered
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','HOLDS_XML')
  2    FROM   dual;

DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
snipped...
 XMLTYPE COLUMN "XML_COL" STORE AS BASICFILE CLOB (
...snipped

So the command completed successfully but did nothing still the column still uses CLOB storage. It was worth a try at least.

There are also the standard methods of

• Exporting data, dropping table, creating new table, importing data
• Exporting data, creating new table, importing data, dropping old table, renaming new table
• Create new table, Insert Select From old table, dropping old table, renaming table

I'm sure there are plenty others, but all those method involve re-doing grants and privileges for the new table. Each has a place though, depending upon the situation and environment.

The downside to this is that once the table contains a SECUREFILE BINARY XML based XMLType column, you can no longer use DBMS_REDEFINITION as shown by

SQL> CREATE TABLE HOLDS_XML  -- new table structure
  2     (n_col   NUMBER(5) NOT NULL PRIMARY KEY,
  3      xml_col XMLTYPE)
  4   XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;
 
Table created
 
SQL> exec dbms_redefinition.can_redef_table( 'JASON', 'HOLDS_XML' );
 
begin dbms_redefinition.can_redef_table( 'JASON', 'HOLDS_XML' ); end;
 
ORA-12090: cannot online redefine table "JASON"."HOLDS_XML"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1766
ORA-06512: at line 2

The ORA-12090 means

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.

I searched on My Oracle Support but did not turn up any reason why a BINARY XML storage would cause this issue. Hopefully it is a limitation Oracle removes in the future.