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
0 %
Sad
0 %
Excited
0 %
Sleepy
0 %
Angry
0 %
Surprise
0 %
Related posts:
About nulls in the database Death of the End Date – How LAG and LEAD help fight redundancy 
Dinsdag 18 september: Oracle Open World & JavaOne 2012 Preview mini-conferenties – 12 presentaties op 1 avond 
Hotsos 2013 – Presentation material “Creating Structure in Unstructured Data” 
Het Oracle OpenWorld Preview Evenement (5 september 2013) – 15 sprekers & sessies
CREATE TABLE x_copynested NESTED TABLE “LIST” STORE AS list_copyasselect AS SELECT * FROM x
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”
Ehh Alex, my point was that a CTAS creates a different table if used on an XMLType table…
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.
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.