CTAS on a Table with Nested Tables

Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

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
/

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

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;
 
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&gt; create table XMLBIN02
  2  as
  3  select * from xmlbin_basic;
 
Table created.
 
Elapsed: 00:00:01.66
SQL&gt; desc XMLBIN02
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SYS_NC_ROWINFO$                                    SYS.XMLTYPE(XMLSchema "http:
                                                    //localhost/public/xsd/mysch
                                                    ema_bin.xsd" Element "ROOT")
                                                     STORAGE BINARY
 
SQL&gt; select count(*) from xmlbin_basic;
 
  COUNT(*)
----------
    100000
 
Elapsed: 00:00:00.03
SQL&gt; select count(*) from XMLBIN02;
 
  COUNT(*)
----------
    100000
 
SQL&gt; 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"
 
Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page
  1. CREATE TABLE x_copynested NESTED TABLE “LIST” STORE AS list_copyasselect AS SELECT * FROM x

  2. Ah,… that was your point?… I was amazed that you could do a CTAS on a table based on an XMLTYPE without doing anything “special”

  3. Ehh Alex, my point was that a CTAS creates a different table if used on an XMLType table…

  4. As you already figure what the deal is with a table with an XMLTYPE. I figured I’d include your example in the post for all to see.

  5. Alex, as far as I know (tested a year ago), although XMLType is a “type” has the posibility of IOT’s and nested table storage etc (using create table test of xmltype,etc), can not be copied via CTAS. Could you have a check via your method? THX.