Copying all tables in a local database to a remote database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Copying all tables in a local database to a remote database

  1. #1
    Join Date
    Jun 2005
    Posts
    2

    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;
    /

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    And what is the error you got?

    Tamil

  3. #3
    Join Date
    May 2005
    Posts
    31

    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;
    Last edited by Bonker; 06-16-2005 at 10:15 AM.
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  4. #4
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    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

  5. #5
    Join Date
    Jun 2005
    Posts
    2

    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

  6. #6
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    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:

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width