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)<br />/<br /><br />create table x<br />(id number<br />,list list_nt<br />)<br />nested table list store as list_nested<br />/<br />insert into x<br />values (1, list_nt ('d', 'c', 'b', 'a'))<br />/<br />insert into x<br />values (2, list_nt ('a', 'b', 'c', 'd'))<br />/<br />commit<br />/<br />select id<br />     , column_value<br />  from x<br />     , table (list) l<br /> order by id, column_value desc<br />/<br /><br /><strong>create table x_copy<br />nested table list store as list_copy<br />as<br />select *<br />  from x</strong><br />/<br />

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