copies database tables from one database to another

i have a requirement of copying three tables from an Oracle database and dump it into another Oracle database.

Copy tables A, B, C from the source database and put it into Destination database as copies of master table A1, B1, C1...next day A2, B2, C2..and so on. This needs to happen daily.

The destination database may have these tables as unique copies for each day, to prevent overwriting..

What is the best way to do it? Should we use datapump exp/imp? if so how to write the query so that the destination database table will not be overwritten.

I am totally new to Oracle sorry.. I can share the table details if needed.
DevSupportAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
How much data?
Are the database connected (can you use a database link)?


I would look at datapump export and import.  It allows you to REMAP_TABLE:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/datapump-import-utility.html#GUID-55EA54B5-BC13-48F1-AF14-485C16170274

It will take some scripting of some type to generate the "New" table names

If you can link the databases and it isn't a LOT of data, I would probably use "Create Table As Select" (CTAS) across a database link.
0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
One other thing to consider is the SQL*Plus COPY command documented here.

Make sure that you read everything in that section.  The command has not been updated since 9i, so anything new since there isn't supported.  It has a very strict list of what is supported.  It may not be around for much longer as they could remove it from any release, but it has been "deprecated" since 9i and it is still referenced in the 18 documentation, so it hasn't gone anywhere yet.

It may be easier to script with names than other solutions, it may not.  I always found it handy for copying tables from one database to another.  No need to create the database link, and it will commit buffers as it goes.
0
 
DevSupportAuthor Commented:
200-300MB data, I am planning to create a job using Oracle grid to create table and copy as @slightwv suggested.

Thanks!
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.