Home » SQL & PL/SQL » SQL & PL/SQL » Copy table with index.
Copy table with index. [message #265617] Thu, 06 September 2007 13:57 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Hello all.

Would you please to show me the way to copy table with index
When I copied table by using this statement:

create table c_emp as select * from emp;

It did not copy the index over to my c_emp table.

Thanks and your answer is appreciated.
Re: Copy table with index. [message #265622 is a reply to message #265617] Thu, 06 September 2007 14:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbms_metadata.get_ddl to get ddl statements and change the table name.
There is no direct statement to do what you want.

Regards
Michel
Re: Copy table with index. [message #265626 is a reply to message #265622] Thu, 06 September 2007 14:21 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thanks much, Michel.
However, I don't know any thing about dmbs_metadate.get_ddl. Would you please to show me some sources or website about this. I would appreciate that.

Re: Copy table with index. [message #265627 is a reply to message #265626] Thu, 06 September 2007 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#sthref4253
Re: Copy table with index. [message #265628 is a reply to message #265627] Thu, 06 September 2007 14:31 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thank you very much.
Re: Copy table with index. [message #265782 is a reply to message #265617] Fri, 07 September 2007 06:57 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Good Morning Michel.

I used dbms_metadata.get_ddl to get a statement then I changed the name to the new table name and create the new table by using that statement as you told me. But It coppied only the primary key to the new table none of the indexes which I created for the original table had been coppied over to my new table.

There is anyway that I can copy the indexes to my new table?
Thanks.


Re: Copy table with index. [message #265798 is a reply to message #265782] Fri, 07 September 2007 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the statement you executed.
Don't forget to format it and keep your line size with less than 80 characters.

Regards
Michel
Re: Copy table with index. [message #265811 is a reply to message #265617] Fri, 07 September 2007 08:11 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
This is the statement:
It gets primary key and partitions but index
select dbms_metadata.get_ddl('TABLE','CUSTOMERS') from dual;
                                                                
  CREATE TABLE "NGUYEN"."CUSTOMERS"                                            
   (    "CUSTID" NUMBER(9,0),                                                       
    "STORE_ID" NUMBER(9,0),                                                       
    "CUST_NAME" VARCHAR2(33),                                                         
    ....................
    .................... 
    ....................
    ....................                                          
    "SEQNUM" VARCHAR2(5),                                                          
     PRIMARY KEY ("CUSTID")                                                         
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS             
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS"  ENABLE                                                    
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                              
  STORAGE(                                                                      
  BUFFER_POOL DEFAULT)                                                          
  TABLESPACE "USERS"                                                            
  PARTITION BY LIST ("STATE")                                                     
 (PARTITION "STATE_27"  VALUES ('27')                                         
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS" NOCOMPRESS ,                                               
 PARTITION "STATE_28"  VALUES ('28')                                          
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS" NOCOMPRESS )   
  
 
*****************************************************************
I used this statement for index

  
  select dbms_metadata.get_ddl('INDEX','STOREID_IDX') from dual;
  
  CREATE INDEX "NGUYE326"."STOREID_IDX" ON "NGUYEN"."CUSTOMERS" ("STORE_ID")        
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS                         
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS"                                             
                                                                  


When I check the statement to create table which I got from dbms_metadata. I did not see the index 'STOREID_IDX'

Thanks
Re: Copy table with index. [message #265818 is a reply to message #265811] Fri, 07 September 2007 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use 2 statements one for the table, one for the indexes.
select dbms_metadata.get_ddl('TABLE','CUSTOMERS') from dual;
select dbms_metadata.get_ddl('INDEX',index_name) from user_indexes where table_name='CUSTOMERS';

You can combine these 2 queries in one. I let you this as an exercise.

Regards
Michel

[Updated on: Fri, 07 September 2007 08:34]

Report message to a moderator

Re: Copy table with index. [message #265825 is a reply to message #265818] Fri, 07 September 2007 09:11 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Hi Michel.

I tried to combine these to statements before sending you a message and tried again after receiving you reply. But I got an error:

CREATE TABLE "NGUYEN"."CUSTOMERS"                                            
   (    "CUSTID" NUMBER(9,0),                                                       
    "STORE_ID" NUMBER(9,0),                                                       
    "CUST_NAME" VARCHAR2(33),                                                         
    ....................
    .................... 
    ....................
    ....................                                          
    "SEQNUM" VARCHAR2(5),                                                          
     PRIMARY KEY ("CUSTID")                                                         
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS             
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS" ENABLE,
 
  USING INDEX "NGUYE326"."STOREID_IDX" ON "NGUYEN"."CUSTOMERS" ("STORE_ID")        
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS                         
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS" ENABLE
                                                     
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                              
  STORAGE(                                                                      
  BUFFER_POOL DEFAULT)                                                          
  TABLESPACE "USERS"                                                            
  PARTITION BY LIST ("STATE")                                                     
 (PARTITION "STATE_27"  VALUES ('27')                                         
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS" NOCOMPRESS ,                                               
 PARTITION "STATE_28"  VALUES ('28')                                          
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS" NOCOMPRESS )      


The error I got is :

ORA-00907: missing right parenthesis

I could not find where the right parenthesis is missing. Please help. Thanks much.
Re: Copy table with index. [message #265826 is a reply to message #265825] Fri, 07 September 2007 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the statement you used (omit the result).

Regards
Michel
Re: Copy table with index. [message #265836 is a reply to message #265826] Fri, 07 September 2007 09:31 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
I used this statement (I combined two of them).

CREATE TABLE "NGUYEN"."CUSTOMERS"                                            
   (    "CUSTID" NUMBER(9,0),                                                       
    "STORE_ID" NUMBER(9,0),                                                       
    "CUST_NAME" VARCHAR2(33),                                                         
    ....................
    .................... 
    ....................
    ....................                                          
    "SEQNUM" VARCHAR2(5),                                                          
     PRIMARY KEY ("CUSTID")                                                         
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS             
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS" ENABLE,
  USING INDEX "NGUYE326"."STOREID_IDX" ON "NGUYEN"."CUSTOMERS" ("STORE_ID")        
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS                         
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
TABLESPACE "USERS" ENABLE                                               
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                              
  STORAGE(                                                                      
  BUFFER_POOL DEFAULT)                                                          
  TABLESPACE "USERS"                                                            
  PARTITION BY LIST ("STATE")                                                     
 (PARTITION "STATE_27"  VALUES ('27')                                         
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS" NOCOMPRESS ,                                               
 PARTITION "STATE_28"  VALUES ('28')                                          
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "USERS" NOCOMPRESS )   
Re: Copy table with index. [message #265848 is a reply to message #265836] Fri, 07 September 2007 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you have an error on the statement to get the DDL or on the statement Oracle returns you as the DDL?
Do you add a terminator to this latter statement? (I don't any ; or / in what you posted)

Regards
Michel
Re: Copy table with index. [message #265861 is a reply to message #265848] Fri, 07 September 2007 12:04 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
No. I don't add the terminator at the end of the statement. However, I stop and highline at

USING INDEX "NGUYE326"."STOREID_IDX" ON "NGUYEN"."CUSTOMERS" ("STORE_ID")

and gave me an error message:

ORA-00907: missing right parenthesis
Re: Copy table with index. [message #265868 is a reply to message #265861] Fri, 07 September 2007 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste the query you used to generate the ddl. Just the query not the output.

Regards
Michel
Re: Copy table with index. [message #265876 is a reply to message #265868] Fri, 07 September 2007 13:31 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Sorry. I misunderstood. Here is my query:

select dbms_metadata.get_ddl('TABLE','UCM08_AD'),(('INDEX',index_name) 
from user_indexes where table_name='CUSTOMERS') from dual;
Re: Copy table with index. [message #265877 is a reply to message #265876] Fri, 07 September 2007 13:32 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Sorry. the previous query is with the wrong table name:
Here is the correction:

select dbms_metadata.get_ddl('TABLE','CUSTOMERS'),(('INDEX',index_name) 
from user_indexes where table_name='CUSTOMERS') from dual;
Re: Copy table with index. [message #265881 is a reply to message #265877] Fri, 07 September 2007 13:46 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
select dbms_metadata.get_ddl('TABLE','CUSTOMERS') from dual
union all
select dbms_metadata.get_ddl('INDEX',index_name) from user_indexes where table_name='CUSTOMERS'
/
Re: Copy table with index. [message #265890 is a reply to message #265877] Fri, 07 September 2007 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query does not work:
SQL> select dbms_metadata.get_ddl('TABLE','CUSTOMERS'),(('INDEX',index_name) 
  2  from user_indexes where table_name='CUSTOMERS') from dual;
select dbms_metadata.get_ddl('TABLE','CUSTOMERS'),(('INDEX',index_name)
                                                           *
ERROR at line 1:
ORA-00907: missing right parenthesis

Use joy_division's query, for instance.
Before, execute:
Begin
  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 
                                     'PRETTY', true);
  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 
                                     'SQLTERMINATOR', true);
End;
/

Regards
Michel
Re: Copy table with index. [message #265892 is a reply to message #265881] Fri, 07 September 2007 14:07 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thanks much.
Re: Copy table with index. [message #266247 is a reply to message #265892] Mon, 10 September 2007 05:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you can use DBMS_METADATA.GET_DEPENDANT_DDL to generate all the DDL for all the indexes for a table:
select dbms_metadata.get_ddl('TABLE','CUSTOMERS') from dual
union all
select dbms_metadata.get_dependent_ddl('INDEX','CUSTOMERS') from dual
Previous Topic: Selecting from a XLS file (merged)
Next Topic: Problem with NOWAIT error when trying to drop index
Goto Forum:
  


Current Time: Wed Dec 07 09:05:54 CST 2016

Total time taken to generate the page: 0.09646 seconds