Geek Answers Handbook

Moving data between different servers in oracle

I am new to Oracle and I am working on moving certain data from a database on one server to a database on another server.

Two databases have the same schema, but I want to pull out the specific columns referenced by their keys and move the data to another server. I'm trying to figure out what is the best attack plan for this.

Preferred is a method that allows you to use the command line only so that I can enter the data key that I need. Is it possible to accomplish using a PLSQL script?

Thank.

+5
source share
1 answer

, , , ..

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

B, ..

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

SQL, PL/SQL-, SQL * Plus script. PL/SQL

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;

EXEC SQL * Plus, PL/SQL

SQL> exec move_row_from_b( 23 );

BEGIN
  move_row_from_b( 23 );
END;

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;
+8

More articles:


All Articles