CTAS on a Table with Nested Tables

The other day one of my colleagues asked me if it is possible to create a copy of a table with all the content in it. He wanted to do some experiments with it and didn’t want to mess with the original table. A simple CTAS (Create Table As Select) didn’t work, because the table had a column which was a Nested Table.

I know you could do a CTAS with Index Organized Tables, but with Nested Tables…

Turns out you can do a CTAS even if the table has a Nested Table in it. Just so I won’t forget, or at least know where to find it in case I need it again, here is the syntax:

create type list_nt is table of varchar2(10)
/

create table x
(id number
,list list_nt
)
nested table list store as list_nested
/
insert into x
values (1, list_nt ('d', 'c', 'b', 'a'))
/
insert into x
values (2, list_nt ('a', 'b', 'c', 'd'))
/
commit
/
select id
     , column_value
  from x
     , table (list) l
 order by id, column_value desc
/

create table x_copy
nested table list store as list_copy
as
select *
  from x
/

Marco’s Test with XMLTYPE

As you can see from Marco’s example, a straight CTAS on a table based on the XMLTYPE works. As simple as that… 😉

SQL> select dbms_metadata.get_ddl('TABLE','XMLBIN_BASIC') from dual;
 
DBMS_METADATA.GET_DDL('TABLE','XMLBIN_BASIC')
--------------------------------------------------------------------------------
 
  CREATE TABLE "MARCO"."XMLBIN_BASIC" OF "SYS"."XMLTYPE"
 
  XMLTYPE STORE AS BASICFILE BINARY XML  (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
 DEFAULT))
 XMLSCHEMA "http://localhost/public/xsd/myschema_bin.xsd" 
ELEMENT "ROOT" ID 4454 DISALLOW NONSCHEMA 
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)
  TABLESPACE "USERS"
 
SQL> create table XMLBIN02
  2  as
  3  select * from xmlbin_basic;
 
Table created.
 
Elapsed: 00:00:01.66
SQL> desc XMLBIN02
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SYS_NC_ROWINFO$                                    SYS.XMLTYPE(XMLSchema "http:
                                                    //localhost/public/xsd/mysch
                                                    ema_bin.xsd" Element "ROOT")
                                                     STORAGE BINARY
 
SQL> select count(*) from xmlbin_basic;
 
  COUNT(*)
----------
    100000
 
Elapsed: 00:00:00.03
SQL> select count(*) from XMLBIN02;
 
  COUNT(*)
----------
    100000
 
SQL> select dbms_metadata.get_ddl('TABLE','XMLBIN02') from dual;
 
DBMS_METADATA.GET_DDL('TABLE','XMLBIN02')
--------------------------------------------------------------------------------
 
  CREATE TABLE "MARCO"."XMLBIN02"
   (    "SYS_NC_ROWINFO$" "SYS"."XMLTYPE"
   ) 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)
  TABLESPACE "USERS"