Home » SQL & PL/SQL » SQL & PL/SQL » Copy Table
Copy Table [message #287966] Fri, 14 December 2007 02:48 Go to next message
sunidhi
Messages: 36
Registered: January 2006
Location: New Delhi
Member
Dear all,

New to Oracle, need your support !

I want to copy two tables having master-detail relationship.Both the tables having one Long data type field.
While copynig I want to maintain masre-detail relatioship to copied tables as well.

Thnks & Regards,

Sunidhi

Report message to a moderator

Re: Copy Table [message #287968 is a reply to message #287966] Fri, 14 December 2007 02:51 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member
Hi Sunidhi,

I din't really understand your question.
Can you give one test case(Create and insert table scripts) with your expected output.

Regards,
Kiran.

[Updated on: Fri, 14 December 2007 02:52]

Report message to a moderator

Re: Copy Table [message #287970 is a reply to message #287966] Fri, 14 December 2007 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You want to copy from where to where?
From a tablespace to another one?
From a schema to another one?
From a database to another one?
...

Regards
Michel

Report message to a moderator

Re: Copy Table [message #287976 is a reply to message #287966] Fri, 14 December 2007 03:20 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you might consider using Export and Import utilities; these will create a "copy" of those tables and maintain integrity constraints, indexes etc. (however, you might have problems if you want to create an exact copy in the same schema as you can not duplicate object names).

Report message to a moderator

Re: Copy Table [message #287979 is a reply to message #287970] Fri, 14 December 2007 03:35 Go to previous messageGo to next message
sunidhi
Messages: 36
Registered: January 2006
Location: New Delhi
Member
Hello Michel,

Thhanks for your reply.

I want to copy tables in same schema and to maintain Integrity Constaints as well.Both the tables having Long type field as well.

Thanks & Regards,

Sunidhi

Report message to a moderator

Re: Copy Table [message #287982 is a reply to message #287979] Fri, 14 December 2007 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

I want to copy tables in same schema

Do you want to move them or copy and rename them?
You can't have 2 tables with same name in the same schema.

Regards
Michel

Report message to a moderator

Re: Copy Table [message #287995 is a reply to message #287982] Fri, 14 December 2007 04:48 Go to previous messageGo to next message
sunidhi
Messages: 36
Registered: January 2006
Location: New Delhi
Member
Dear Michel,

I want to copy them with different name in same schema but have to maintain same integrity Constraints.

Kindly give me syntax for that.

Thanks

Report message to a moderator

Re: Copy Table [message #287999 is a reply to message #287995] Fri, 14 December 2007 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There is no syntax.
You have to use dbms_metadata to get the tables definion with their constraints, then modify the names, and execute it.
Finally you insert the data.

Maybe you can do it with datapump but I admit I don't know all its renaming capabilities.

Regards
Michel

Report message to a moderator

Re: Copy Table [message #289492 is a reply to message #287999] Sat, 22 December 2007 09:13 Go to previous messageGo to next message
0551373
Messages: 6
Registered: December 2007
Junior Member
Try Select * into newtable from source. this will allow you to copy the Schema with the data, you can use the truncate to delete all the data if you need to.

Report message to a moderator

Re: Copy Table [message #289494 is a reply to message #287966] Sat, 22 December 2007 09:26 Go to previous messageGo to next message
0551373
Messages: 6
Registered: December 2007
Junior Member
I forgot, There is another way you can do this and that is with the following syntax create table newtable as (Select * from tablesource); this will avoid the error ORA-905.
hope this will help. Smile

Report message to a moderator

Re: Copy Table [message #289498 is a reply to message #289494] Sat, 22 December 2007 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

create table newtable as (Select * from tablesource);

And how this handles the following part: "have to maintain same integrity Constraints"?

Regards
Michel

Report message to a moderator

Re: Copy Table [message #289876 is a reply to message #289498] Wed, 26 December 2007 06:26 Go to previous messageGo to next message
ora_zhp
Messages: 10
Registered: September 2007
Junior Member
if you want to copy them with different name in same schema but have to maintain same integrity Constraints,you can only first copy table,after recreate Constraints!!!

Report message to a moderator

Re: Copy Table [message #289893 is a reply to message #289494] Wed, 26 December 2007 07:45 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
0551373 wrote on Sat, 22 December 2007 10:26

I forgot, There is another way you can do this and that is with the following syntax create table newtable as (Select * from tablesource); this will avoid the error ORA-905.
hope this will help. Smile


In addition to Michel's post, this will also not work for LONG datatypes.

Report message to a moderator

Previous Topic: nested tables
Next Topic: How to find LOB format
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Feb 18 10:52:46 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.