Copying all tables in a local database to a remote database

[ee97aio]

Hi John,

Thanks for the reply, I have tried the code but I got this error message

ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database
ORA-06512: at line 3

I look forward to your reply. thanks.

On the local database as the user whose tables you want to copy do:

CREATE DATABASE LINK sales2
CONNECT TO remote_user IDENTIFIED BY password
USING 'sales2'
 
Then
 
BEGIN
   FOR t_rec IN (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'CREATE TABLE '||t_rec.table_name||
                        '@sales2 AS SELECT * FROM '||t_rec.table_name;
   END LOOP;
END;

HTH
John