oracle - copy data from one to the other

there are two oracle database, same version 10 G and having same tables.

I like to copy data from one to the other, there are multiple tables, what is easy approach to copy table data? I do not need to copy table structure but data only.
sleepinglukeAsked:
Who is Participating?
 
sventhanCommented:
Try this from SQL Plus
copy from username/pwd@source_db to username/passwd@dest_db -
insert to_your_src_table using select * from from_your_dest_table
0
 
Alexey KomarovChief Project EngineerCommented:
Hi,
You may use import, export tools (imp.exe, exp.exe).
Or microsoft DTS import\export from ms sql server 2000.

0
 
k_murli_krishnaCommented:
I think export(exp), import(imp), data pump (expdp & impdp) do not handle only data & no definition.

Best is to establish a database link between the 2 databases and while connected to one, you can do:

INSERT INTO Local_Table SELECT * FROM Remote_Table@DBLink;

Refer:
CREATE DATABASE LINK
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5005.htm
http://searchoracle.techtarget.com/tip/How-to-create-a-database-link-in-Oracle

One more easy way is generate INSERT script using built-in tool if available else you can use TOAD or DBARTISAN tools and run the script in correct relational ascending order of tables in target.

You can also do:
Export from Oracle table to CSV file
http://kb.yarmakconsulting.com/2008/04/export-from-oracle-table-to-csv-file.html
http://en.allexperts.com/q/Oracle-1451/2008/6/creating-CSV-file-oracle-1.htm

You can also use microsoft DTS import\export from ms sql server 2000 as suggested by alexeykomarov OR even SQL Server 2005 SSIS if available.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
k_murli_krishnaCommented:
One thing you can do with exp and expdp is export definition as well as data as a whole or based on a SELECT query & using IGNORE=y instructs Oracle to ignore any creation errors during the import. Make sure whatever you are exporting should match/be compatible with what exists in target. During exp/expdp, better to use ROWS = Y option in the command.
0
 
sleepinglukeAuthor Commented:
thanks for many suggestions.

I am stuck in the begining though - I have this error doing export..
EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 15:
PLS-00201: identifier 'SYS.DBMS_EXPORT_EXTENSION' must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully


command was like this , on Solaris...
exp user/password@server tables=(table1)

0
 
Alexey KomarovChief Project EngineerCommented:
In 10gR2, import uses DBMS_EXPORT_EXTENSION package and you need to grant execute on DBMS_EXPORT_EXTENSION to the schema user being imported into.

Grant execute on DBMS_EXPORT_EXTENSION to public
or
Grant execute on DBMS_EXPORT_EXTENSION to the user/schema
0
 
sleepinglukeAuthor Commented:
understood - so maybe I do not have permission.... let me check with DBA...
0
 
bhadlikarCommented:
The best option is to create a DB link & then execute below statment
insert into TargetSchma.TargetTable Select * from SourceSchema.SourceTable@DBlink

0
 
sventhanCommented:
You do not have to create the DBlink at all if you want to use the SQLPlus copy method that I posted above.
0
 
sleepinglukeAuthor Commented:
thank you very much, sorry for delay respond was be in sick - this works appreciated
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.