UPDATE TABLE USING DBLINK FROM REMOTE DATABASE
To update a table remotely from a destination database using source table, dblink can be used
Here i create a dblink called dbl to pipe the connection from destination to source.
SQL> create database link dbl connect to test identified by "password" using 'db emt';
Database link created.
SQL> select * from dual@dbl
2 ;
D
-
X
SQL> create table t1 as select * from TEST.BIG_TABLE@dbl;
Table created.
Data can be remotely updated using UAS(update as select) like this
SQL> update t1 tab1
2 set (OWNER,OBJECT_NAME) = (select bt.owner,bt.object_name
3 from TEST.BIG_TABLE@dbl bt
4 where tab1.id = bt.id);
10000 rows updated.
SQL> commit;
Commit complete.
SQL> update t1 tab1
2 set (OWNER,OBJECT_NAME) = (select bt.owner,bt.object_name
3 from TEST.BIG_TABLE@dbl bt
4 where tab1.id = bt.id)
5 where id < 100;
99 rows updated.
SQL> commit;
Commit complete.