Moving data between different servers in oracle

Question

I'm new to Oracle, and I am working on moving specific data from a DB on one server to the DB on another server.

The two DBs have the same schema, but I want to pull specific columns referenced by their keys and move the data into other server. I'm trying to figure out what the best plan of attack on this would be.

A method that allows a command line just so I can type in the key of the data I want moved is preferred. Is it perhaps possible to accomplish with a PLSQL script?

Thanks.

Answer 1

Assuming that you can create network connections between the two databases, the simplest option would be to create a database link between them, i.e.

CREATE DATABASE LINK to_b
  CONNECT TO username_on_b
  IDENTIFIED BY password
  USING 'tns_alias_for_b'

You could then use that database link to query data from database B, i.e.

INSERT INTO table_name( list_of_columns )
  SELECT list_of_columns
    FROM table_name@to_b
   WHERE primary_key_value = <<some value>>;

That can be either a straight SQL statement, part of a PL/SQL procedure, or part of a SQL*Plus script. A PL/SQL procedure

CREATE OR REPLACE PROCEDURE move_row_from_b( 
  p_key_value IN table_name.primary_key%type 
)
AS
BEGIN
  INSERT INTO table_name( list_of_columns )
    SELECT list_of_columns
      FROM table_name@to_b
     WHERE primary_key_value = p_key_value;
END move_row_from_b;

which can be invoked either via EXEC from SQL*Plus or via an anonymous PL/SQL block

SQL> exec move_row_from_b( 23 );

BEGIN
  move_row_from_b( 23 );
END;

Or you could write a SQL*Plus script

variable key_value number;
accept key_value prompt 'Enter key: '
INSERT INTO table_name( list_of_columns )
  SELECT list_of_columns
    FROM table_name@to_b
   WHERE primary_key_value = :key_value;