Home » SQL & PL/SQL » SQL & PL/SQL » copy table from 1 database to another (Oracle9i Enterprise Edition Release 9.2.0.4.0)
copy table from 1 database to another [message #307594] Wed, 19 March 2008 04:17 Go to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi All,

I want to copy the entire contents of table from 1 database to another. The database schema is same.


select * from v$version

BANNER

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for HPUX: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

Please provide the query.

Thanks,
Aditi
Re: copy table from 1 database to another [message #307597 is a reply to message #307594] Wed, 19 March 2008 04:25 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
You can create a DBLINK to achive your purpose.


regards,

[Updated on: Wed, 19 March 2008 04:26]

Report message to a moderator

Re: copy table from 1 database to another [message #307602 is a reply to message #307597] Wed, 19 March 2008 04:35 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Please let me know the syntax.
Re: copy table from 1 database to another [message #307605 is a reply to message #307602] Wed, 19 March 2008 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CREATE DATABASE LINK

Regards
Michel
Re: copy table from 1 database to another [message #307653 is a reply to message #307602] Wed, 19 March 2008 06:19 Go to previous messageGo to next message
smrutiranjan
Messages: 3
Registered: March 2008
Location: mumbai
Junior Member
Try this

Create database link <link name>
connect to <user name> identified by <password>
using <database name>


After creating the dblink u can access it by

select * from tablename@dblink name

and in your requirement u can use

create table <tablename>
As select * from <tablename>@dblinkname

It will help u for sure!!!
Re: copy table from 1 database to another [message #307655 is a reply to message #307653] Wed, 19 March 2008 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It will help u for sure!!!

More than the documentation that will give full syntax, prerequisites, restrictions, more examples, pointers to other associated documents...?

Regards
Michel
Re: copy table from 1 database to another [message #307670 is a reply to message #307653] Wed, 19 March 2008 07:07 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
I have created database link as

Create database link "TEST_IBIA"
connect to T2
identified by "xxxxx"
using 'IBIA'

I am getting error as 'CONNECTION DESCRIPTION FOR REMOTE DATABASE NOT FOUND'while executing below select stmt

select * from <tablename>@TEST_IBIA

Plese help me out.

Thanks,
Aditi

Re: copy table from 1 database to another [message #307674 is a reply to message #307670] Wed, 19 March 2008 07:24 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has already been said to you:
Quote:
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Copy and paste your session.

Regards
Michel

Previous Topic: Oracle UTL_FILE exception read_error
Next Topic: SQL HELP
Goto Forum:
  


Current Time: Fri Dec 09 15:43:57 CST 2016

Total time taken to generate the page: 0.21402 seconds