Home » RDBMS Server » Server Utilities » How to load data from one table in a database to another table in another database (oracle 10g)
How to load data from one table in a database to another table in another database [message #283694] Wed, 28 November 2007 00:12 Go to next message
navenis4u
Messages: 3
Registered: November 2007
Location: India
Junior Member
Hi all,

I want to know how can we insert a data from table that exists in one database to another table in another database.

Ex:- Suppose i have a table x in a db abc. I have another table y in a db pqr. The column name in the two tables differ. How can we insert the data in x to y . Do we need to make database link?

Thanks in advance..

Naveen Nishad.R
Re: How to load data from one table in a database to another table in another database [message #283696 is a reply to message #283694] Wed, 28 November 2007 00:15 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

1.Database link
sql>conn u1/u1@db2
sql>create table a as select * from all_objects where rownum <= 10; table created. For database link you have "database link" or "create session" privs. sql>conn u1/u1@db1
connected
sql>create database link db2
connect to u1
identified by u1
using 'db2';
database link created
sql>create table t as select * from a@db2;
table created
sql>select count(*) from t;
----------------------------------------------------------
2.Through Export or Import Utilities.
------------------------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table d as select * from all_objects where rownum <= 10; Table created. SQL> host exp a1/a1@db1 tables=d file=d:\oracle\d.dmp log=d:\oracle\d.log
Export: Release 10.1.0.2.0 - Production on Sat Dec 23 18:24:52 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table D 10 rows exported
Export terminated successfully without warnings.
SQL> conn a1/a1@db2
Connected.
SQL> host imp a1/a1@db2 fromuser=a1 touser=a1 file=d:\oracle\d.dmp log=d:\oracle
\d.log ignore=y
Import: Release 10.1.0.2.0 - Production on Sat Dec 23 18:25:58 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "D" 10 rows imported
Import terminated successfully without warnings.
------------------------------------------------------
3.Through Copy Command. 
----------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table m as select * from all_objects where rownum <= 10; Table created. SQL> con a1/a1@db2
Connected.
SQL> copy from a1/a1@db1 to a1/a1@db2 -
> create m -
> using select * from m;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table M created.
10 rows selected from a1@db1.
10 rows inserted into M.
10 rows committed into M at a1@db2.


hope this help
http://dbataj.blogspot.com/2006/12/from-onedb-to-anotherdb.html
Re: How to load data from one table in a database to another table in another database [message #283754 is a reply to message #283696] Wed, 28 November 2007 01:39 Go to previous messageGo to next message
navenis4u
Messages: 3
Registered: November 2007
Location: India
Junior Member
Thanks for the quick reply my friend..

Is there any problem while creating a database link from a 10g database to 8i database...

Thanks,
Naveen Nishad.R
Re: How to load data from one table in a database to another table in another database [message #283759 is a reply to message #283754] Wed, 28 November 2007 01:45 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>Is there any problem while creating a database link from a 10g database to 8i database...

NO, but try if any problem come then post here
Re: How to load data from one table in a database to another table in another database [message #283763 is a reply to message #283754] Wed, 28 November 2007 01:51 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, it is no more supported and only 8.1.7.4 was supported.

Regards
Michel
Previous Topic: Sqlldr query
Next Topic: Update Oracle table data from data in Excel file
Goto Forum:
  


Current Time: Tue Dec 24 23:18:27 CST 2024