XMLTYPE on Autonomous

January 8, 2024

2–3 minutes

I had a customer ask me recently why XMLTYPE is disallowed on Autonomous Database. This surprised me, because I had not heard anything along those lines. But they sent me the following test case, where they simply extracted the DDL from their existing on-premises database and could not run it on Autonomous.



SQL> create table some_xml
  2        (pk      number(5) primary key,
  3         xml_col xmltype)
  4      xmltype xml_col store as basicfile clob;
create table some_xml
*
ERROR at line 1:
ORA-01031: insufficient privileges

So I fired up my Always Free database (and me being the lazy developer I am) I just used “xmltype” and things seemed to be just fine.


SQL>  create table some_xml_new
  2         (pk      number(5) primary key,
  3          xml_col xmltype);

Table created.

That led to the cause of the issue here. Way back in 12c, we deprecated the usage of CLOB for XMLTYPE in favour of the newer, better, faster binary XML format. Explicitly nominating the binary storage mimics the default storage on Autonomous.


SQL>  create table some_xml_new
  2         (pk      number(5) primary key,
  3          xml_col xmltype)
  4     xmltype xml_col store as securefile binary xml;

Table created.

The challenge for this customer then was: How to convert to the new binary type so that they could migrate their on-premises database to Autonomous to pick up all of the benefits of doing do.

Unfortunately “ALTER TABLE MOVE” does not allow this conversion (or more accurately, I should say that I could not successfully come up with a DDL syntax that was allowed by the database on my 19c instance). However, a little DBMS_REDEFINTION lets us do the conversion with minimal downtime as long as we have the space for a second copy of the data. Here’s a CLOB based XMLTYPE table:


SQL> create table some_xml
  2    (pk      number(5) primary key,
  3     xml_col xmltype)
  4  xmltype xml_col store as basicfile clob;
 
Table created

SQL> insert into some_xml values
  2  ('1',XMLTYPE('<tag>val1</tag>'));
 
1 row inserted

SQL> commit;
 
Commit complete

Now in the normal way, we simply build a template table for our desired end state of the conversion



SQL> create table some_xml_new
  2    (pk      number(5) primary key,
  3     xml_col xmltype)
  4  xmltype xml_col store as securefile binary xml;
 
Table created

and then we run DBMS_REDEFINITION in the usual way.


SQL> exec dbms_redefinition.start_redef_table('SCOTT', 'SOME_XML', 'SOME_XML_NEW');
 
PL/SQL procedure successfully completed
 
SQL>    exec dbms_redefinition.sync_interim_table('SCOTT', 'SOME_XML', 'SOME_XML_NEW');
 
PL/SQL procedure successfully completed

SQL>    exec dbms_redefinition.finish_redef_table('SCOTT', 'SOME_XML', 'SOME_XML_NEW');
 
PL/SQL procedure successfully completed

If I generate the DDL for the SOME_XML table now, you can see that we have successfully converted to the binary XML storage type, and thus this table (and the database) can be migrated to Autonomous.


SQL> select dbms_metadata.get_ddl('TABLE','SOME_XML','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','SOME_XML','SCOTT')
---------------------------------------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."SOME_XML"
   (    "PK" NUMBER(5,0),
        "XML_COL" "SYS"."XMLTYPE" ,
         PRIMARY KEY ("PK")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 XMLTYPE COLUMN "XML_COL" STORE AS SECUREFILE BINARY XML (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW 4000 CHUNK 8192
  CACHE  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 262144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA

Thus XMLTYPE is fine on Autonomous, but you’ll need to migrate to a supported storage format before bringing those tables over.

4 responses to “XMLTYPE on Autonomous”

Amin Adatia

January 8, 2024 at 10:18 pm

Could the problem be XMLTYPE COLUMN “XML_COL” STORE AS BASICFILE instead of SECUREFILE

Reply
1. Rajeshwaran Jeyabal
  
  January 9, 2024 at 9:56 am
  
  No.
  basicfile binary xml is still supported in ADB. but still securefile got many features (like deduplication, compression etc) which is not possible in basic file
  
  demo@ATP19C> create table some_xml_new 2 (pk number(5) primary key, 3 xml_col xmltype) 4 xmltype xml_col store as basicfile binary xml;
  Table created. demo@ATP19C> insert into some_xml_new values( 1, xmltype('Hello') ); 1 row created.
  demo@ATP19C>
  
  Reply
sydoracle1

January 9, 2024 at 1:42 pm

After the create, look at the COLUMN_NAME recorded in the USER_LOBS table, and use that in the ALTER TABLE

alter table some_xml MOVE LOB (SYS_NC00003$) STORE AS SECUREFILE;

Reply
1. Connor McDonald
  
  January 9, 2024 at 1:51 pm
  
  Unfortunately this only gets us halfway there because we need the binary XML
  
  SQL> create table some_xml
  2 (pk number(5) primary key,
  3 xml_col xmltype)
  4 xmltype xml_col store as basicfile clob;
  
  Table created.
  
  SQL>
  SQL> insert into some_xml values
  2 (‘1’,XMLTYPE(‘val1’));
  
  1 row created.
  
  SQL>
  SQL> alter table some_xml MOVE LOB (SYS_NC00003$) STORE AS SECUREFILE binary xml;
  alter table some_xml MOVE LOB (SYS_NC00003$) STORE AS SECUREFILE binary xml
  *
  ERROR at line 1:
  ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
  
  Reply