Copying all tables in a local database to a remote database
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;
/
Copying all tables in a local database to remote database
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.
Quote:
Originally Posted by John Spencer
On the local database as the user whose tables you want to copy do:
Code:
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