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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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.

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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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)

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
sleepinglukeAuthor Commented:
understood - so maybe I do not have permission.... let me check with DBA...
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bhadlikarCommented:
The best option is to create a DB link & then execute below statment
insert into TargetSchma.TargetTable Select * from SourceSchema.SourceTable@DBlink

sventhanCommented:
You do not have to create the DBlink at all if you want to use the SQLPlus copy method that I posted above.
sleepinglukeAuthor Commented:
thank you very much, sorry for delay respond was be in sick - this works appreciated
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.