Copying all tables in a local database to a remote database

[ee97aio]

Hi guys,

I need help in writing a pl/sql code that will copy all tables of a current user from a local database to another user in a remote database.

I have managed to generate this code which generates an error. Please help rectify the error or if you have another way of writing this please suggest one. Cheers

set copycommit 1

DECLARE
v_Table USER_TABLES.TABLE_NAME%TYPE; /* store table name */
v_Counter NUMBER := 1; /* counter for loop */
v_TotalRow NUMBER := 0; /* store total row number */

BEGIN

/* return to number of rows into v_TotalRow */
SELECT COUNT(*)
INTO v_TotalRoW
FROM USER_TABLES;

LOOP
/* select the table name from user tables and store it in v_table */
SELECT TABLE_NAME
INTO v_Table
FROM USER_TABLES
WHERE ROWNUM = v_Counter;

COPY TO PRACTICE/PRACTICE@SALES2 -
REPLACE v_Table -
USING SELECT * FROM v_Table

v_Counter := v_Counter + 1;
COMMIT;

IF v_Counter > v_TotalRow THEN
EXIT;
END IF;

END LOOP; **/
END;
/

[tamilselvan]

And what is the error you got?

Tamil

[Bonker]

COPY TO PRACTICE/PRACTICE@SALES2 -
REPLACE v_Table -
USING SELECT * FROM v_Table

I am not sure about this but I think Copy is SQLPLUS syntax and is not valid in PL/SQL

Also following query in the code will not work when v_counter increments to 2 and above.


SELECT TABLE_NAME
INTO v_Table
FROM USER_TABLES
WHERE ROWNUM = v_Counter;

[John Spencer]

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

[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

[John Spencer]

Sorry, I really must test before posting .

Just do it the other way around. Create the dblink on the sales2 database, as the user who wants to copy the tables. Connect to the user who has the tables you want to copy, then, assuming the main database is sales, the procedure becomes:

BEGIN
   FOR t_rec IN (SELECT table_name FROM user_tables@sales) LOOP
      EXECUTE IMMEDIATE 'CREATE TABLE '||t_rec.table_name||
                        ' AS SELECT * FROM '||t_rec.table_name||'@sales';
   END LOOP;
END;

This time, I did run it, and it does work.

My homework, write 50 times:

I will not post without testing.

John