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 xvalues (1, list_nt ('d', 'c', 'b', 'a'))/insert into xvalues (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_copynested table list store as list_copyasselect *  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.66SQL> 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.03SQL> 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"