OraFAQ Forum: SQL & PL/SQL » Copy table with index.

Home » SQL & PL/SQL » SQL & PL/SQL » Copy table with index.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Copy table with index. [message #265617]

Thu, 06 September 2007 13:57

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.

Report message to a moderator

Re: Copy table with index. [message #265622 is a reply to message #265617]

Thu, 06 September 2007 14:11

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Copy table with index. [message #265626 is a reply to message #265622]

Thu, 06 September 2007 14:21

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.

Report message to a moderator

Re: Copy table with index. [message #265627 is a reply to message #265626]

Thu, 06 September 2007 14:24

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#sthref4253

Report message to a moderator

Re: Copy table with index. [message #265628 is a reply to message #265627]

Thu, 06 September 2007 14:31

lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia

Senior Member

Thank you very much.

Report message to a moderator

Re: Copy table with index. [message #265782 is a reply to message #265617]

Fri, 07 September 2007 06:57

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.

Report message to a moderator

Re: Copy table with index. [message #265798 is a reply to message #265782]

Fri, 07 September 2007 07:34

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Copy table with index. [message #265811 is a reply to message #265617]

Fri, 07 September 2007 08:11

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

Report message to a moderator

Re: Copy table with index. [message #265818 is a reply to message #265811]

Fri, 07 September 2007 08:34

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.

Report message to a moderator

Re: Copy table with index. [message #265826 is a reply to message #265825]

Fri, 07 September 2007 09:14

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post the statement you used (omit the result).

Regards
Michel

Report message to a moderator

Re: Copy table with index. [message #265836 is a reply to message #265826]

Fri, 07 September 2007 09:31

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 )

Report message to a moderator

Re: Copy table with index. [message #265848 is a reply to message #265836]

Fri, 07 September 2007 10:39

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Copy table with index. [message #265861 is a reply to message #265848]

Fri, 07 September 2007 12:04

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

Report message to a moderator

Re: Copy table with index. [message #265868 is a reply to message #265861]

Fri, 07 September 2007 12:57

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Copy and paste the query you used to generate the ddl. Just the query not the output.

Regards
Michel

Report message to a moderator

Re: Copy table with index. [message #265876 is a reply to message #265868]

Fri, 07 September 2007 13:31

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;

Report message to a moderator

Re: Copy table with index. [message #265877 is a reply to message #265876]

Fri, 07 September 2007 13:32

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;

Report message to a moderator

Re: Copy table with index. [message #265881 is a reply to message #265877]

Fri, 07 September 2007 13:46

joy_division
Messages: 4963
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'
/

Report message to a moderator

Re: Copy table with index. [message #265890 is a reply to message #265877]

Fri, 07 September 2007 14:02

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Copy table with index. [message #265892 is a reply to message #265881]

Fri, 07 September 2007 14:07

lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia

Senior Member

Thanks much.

Report message to a moderator

Re: Copy table with index. [message #266247 is a reply to message #265892]

Mon, 10 September 2007 05:00

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

Report message to a moderator

Previous Topic:	Selecting from a XLS file (merged)
Next Topic:	Problem with NOWAIT error when trying to drop index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 04 12:09:59 CDT 2026