SQL*Plus COPY Command between two database

SQL*Plus COPY Command

Dear all,

 After long time I am writing this docs.

I'm doing the update command between two database using copy command which is available in sql* plus

and without creating database link , but using the tns names from connecting and updating .

Rule :

 update the (bangalore db table ) table using Chennai (database table) table. with below criteria

UPDATE DO_CR_STR (bangalore table) SET SC_CR_STR='REDEEM' WHERE ID_CR_STR IN(SELECT STR_NO FROM LS_STR_CRDT_RDM_STG (chennai table) );


Source database - Chennai ( tnsname - chennai )

Destination database - Bangalore ( tnsname - bangalore )

UPDATE DO_CR_STR SET SC_CR_STR='REDEEM' WHERE ID_CR_STR IN(SELECT STR_NO FROM LS_STR_CRDT_RDM_STG_temp );

Step 1 :

Connect the sqlplus by using

sqlplus /nolog

Step 2 :

Create the table in bangalore database from chennai database using following command,

sql> COPY FROM username/passwd@chennai -
>TO username/passwd@bangalore -
>create LS_STR_CRDT_RDM_STG_temp -
>USING SELECT * from LS_STR_CRDT_RDM_STG;

disc

Step 3 :

Connect the bangalore database from your server using tns names and update the table by using created table from chennai server.

sql> conn username/passwd@bangalore;



sql> UPDATE DO_CR_STR SET SC_CR_STR='REDEEM' WHERE ID_CR_STR IN(SELECT STR_NO FROM LS_STR_CRDT_RDM_STG_temp );

commit;

Step 4 :

once the update process completed do the drop command .

sql> drop table storebo.LS_STR_CRDT_RDM_STG_temp;

disc.

This above scripts only applicable for , if your updating many database from one database as on daily basis.

Otherwise go for DB link creation .

Best of regards,

Suresh R