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
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: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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
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: 20896
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).
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
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: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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
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
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: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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
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.
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
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: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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
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!!!
Re: Copy Table [message #289893 is a reply to message #289494] Wed, 26 December 2007 07:45 Go to previous message
joy_division
Messages: 4640
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.
Previous Topic: nested tables
Next Topic: How to find LOB format
Goto Forum:
  


Current Time: Tue Dec 06 10:11:49 CST 2016

Total time taken to generate the page: 0.08581 seconds