CTAS on a Table with Nested Tables

Alex Nuijten 5
0 0
Read Time:2 Minute, 16 Second

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"
 
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

5 thoughts on “CTAS on a Table with Nested Tables

  1. 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”

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

  3. 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.

  4. 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.

Comments are closed.

Next Post

How to fix your number-sorting problems in Javascript

Did you ever wanted to sort [1, 10, null, 20] in Javascript and were the results unpredictable? It’s not a bug, but more something that’s not implemented. I came across this problem when testing some table sorting plugins for Dojo and jQuery. First I thought it were bugs, but the […]
%d bloggers like this: