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?

[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.

slightwv (䄆 Netminder) 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

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
johnsoneSenior 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
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
Databases

From novice to tech pro — start learning today.