Skip to Main Content
  • Questions

  • Coping schema tables from one database to another database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 07, 2023 - 4:25 am UTC

Last updated: October 22, 2023 - 11:26 pm UTC

Version: 19c

Viewed 10K+ times! This question is

You Asked

Hi,


I have a requirement of copying all the tables of a particular schema in one database to another database having same schema name.

Like I have Source database as DB1(its an adbs DB, can't create DB link I believe) and schema tkggu1 and target database DB2(on-prem) having schema tkggu1.
I need to copy all the tables of DB1 from tkggu1 schema to DB2 under tkggu1.

I need to do a bulk copy of all the tables from a program and don't want to iterate each table for copying.

Could you pls suggest a way to get this done.

Thanks,
Subhash

and Connor said...

As long as you are on a recent RU of 19c you should be able to export/import directly to/from object stores. eg

Create a credential to allow access to a bucket you create on ADB

begin
  dbms_cloud.create_credential (
    credential_name => 'bucket_credential',
    username        => 'myuser@mydomain.com',
    password        => '{Auth-Token}'
  ) ;
end;


Then export on ADB

expdp admin/AdminPassword@my_atp_service 
  schema=hr 
  directory=data_pump_dir 
  credential=bucket_credential 
  dumpfile=https://swiftobjectstorage.au-sydney.oraclecloud.com/v1/my-namespace/my-bucket/hr.dmp


and then import directly from it on your local database

impdp system/DBAPassword@my_local_db
  schema=hr 
  credential=bucket_credential 
  dumpfile=https://swiftobjectstorage.au-sydney.oraclecloud.com/v1/my-namespace/my-bucket/hr.dmp
  logfile=mydir:impdp.log


Worst case - download the file from the object bucket and import locally.

Autonomous also supports database links now but I would not recommend that because thats a lot of network back and forth for a schema import

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

regarding dbms_cloud.create_credential in Target system.

Rajeshwaran Jeyabal, October 12, 2023 - 7:44 am UTC

while importing into local database from the above example

do we need to run the "dbms_cloud.create_credential " before invoking "impdp" command ?

if not then how does the "impdp" connecting to local database can recognize "..credential=bucket_credential..." ?


Connor McDonald
October 16, 2023 - 1:49 am UTC

Correct

A reader, October 20, 2023 - 4:34 am UTC

Hi Connor,

I was curious on dblink creation support in ADB so tried creating one and it failed as earlier. I am using DB version 19.21.0.1.0.

I think db link creation sill not supported on ADB

SQL> conn ADMIN/AutoS_Y_S123#@inst1
Connected.
SQL> create public database link target connect to tkggu2 identified by tkggu2 using 'inst2';
create public database link target connect to tkggu2 identified by tkggu2 using 'inst2'
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>
Connor McDonald
October 22, 2023 - 11:26 pm UTC

A public database link is not the same as a database link :-) but in any event, you need to use the API to do this

BEGIN
              DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
              db_link_name => 'SALESLINK', 
              hostname => 'adb.eu-frankfurt-1.oraclecloud.com', 
              port => '1522',
              service_name => 'example_medium.adb.example.oraclecloud.com',
              ssl_server_cert_dn => 'CN=adb.example.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
              credential_name => 'DB_LINK_CRED',
              directory_name => 'DBLINK_WALLET_DIR');
              END;
              /

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.