Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Question Unanswered: pl/sql script tp move data from one oracle db to another

    I am new to Oracle. I have to write one SQL script that moves data from one Oracle db to another oracle db on different servers. If anybody has done similar work.....pls reply to me at divaker@indiatimes.com.

    Thanks in advance,
    Amit

  2. #2
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127

    Lightbulb

    Solution to this is

    1. Use export and import utility to transfer the data.

    or else

    2. Create a database link between the databases.
    Then write a pl/sql block to insert the data into the target database.

    The select statement will be like

    SELECT * FROM TABLE@DBLINK WHERE......

    INSERT INTO TABLE......

    (PL/SQL should be run from the target database)

    Hope this will help

    Regards
    Shelva

  3. #3
    Join Date
    Nov 2003
    Posts
    7
    Thanx Dear Shelva,
    i was trying the DBLINK option.

    following is the sql stmt that i wrote to create DBLINK:
    CREATE DATABASE LINK
    div CONNECT TO user IDENTIFIED BY password USING 'external';

    I am able to create DBLINk but when i execute an SQL statement e.g.

    sql>select * from table1@div;

    it doesn't return the rows from table. it gives me following error:

    select * from table1@div
    *
    ERROR at line 1:
    ORA-02085: database link DIV.US.ORACLE.COM connects to EXTERNAL.US.ORACLE.COM

    and the statement breaks....could you pls suggest something...what is going wrong?

    Thanx again,
    Amit


    Originally posted by shelva
    Solution to this is

    1. Use export and import utility to transfer the data.

    or else

    2. Create a database link between the databases.
    Then write a pl/sql block to insert the data into the target database.

    The select statement will be like

    SELECT * FROM TABLE@DBLINK WHERE......

    INSERT INTO TABLE......

    (PL/SQL should be run from the target database)

    Hope this will help

    Regards
    Shelva

  4. #4
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Hi,
    Make your name of the link and the database same
    Do not use external option

    CREATE DATABASE LINK
    div CONNECT TO user IDENTIFIED BY password

    it should work

  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    When the source database initialization parameter GLOBAL_NAMES is set to true, the database link name must match the target database global name as it exists in the GLOBAL_NAME view in the data dictionary.

    The GLOBAL_NAME can be determined by logging in to the database with system privileges and issuing the following command:

    SQL>Select * from global_name;

    Additionally, if you do not specify the domain portion of the dblink name in the create statement, Oracle automatically qualifies the link name with the domain of the SOURCE database global name view.

    Check the contents of ALL_DB_LINKS for the fully qualified link name.
    SATHISH .

  6. #6
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    Create public database link EXTERNAL.US.ORACLE.COM connect to userid identified by password using ?test?;

    select * from tablename@TEST.WORLD.COM;

    Will give you the desired Result.
    SATHISH .

  7. #7
    Join Date
    Nov 2003
    Posts
    7
    Dear Satish,
    Thanks a million.....its working now. the only problem was that i was not using the fully qualified link name.

    Regards,
    Amit

Posting Permissions

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