Use database links
Once a database link has been created
you are now ready to select your data from the remote database referenced by the db link.
The syntax is :
select <column list> from <table>@<dblink name>;
For example:
SQL> select * from dept@remotedb;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
The database link is opened during the select (or other DML transaction) and remains open for the duration of the session.
After you close a session, the links that were active in the session are automatically closed.
Close a db link
To explicitly close the database link , use the command below:
SQL> alter session close database link remotedb; Session altered.
Using db links in other DML statements
You can use insert/update/delete statements just as easily with database links
SQL> select * from dept@remotedb;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept@remotedb (deptno,dname,loc)
2 values (50,'MARKETING','BOISE');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept@remotedb;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING BOISE
SQL> update dept@remotedb set loc = 'LONDON'
where deptno = 50;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from dept@remotedb;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING LONDON
SQL> delete from dept@remotedb
where dname = 'MARKETING';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from dept@remotedb;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Note that DDL operations are not allowed through a database link:
SQL> alter table dept@remotedb
add column (manager varchar2(30));
alter table dept@remotedb add column
(manager varchar2(30))
*
ERROR at line 1:
ORA-02021: DDL operations are not allowed
on a remote database
Recommended reading:
Posted: October 23rd, 2009 under DB Links.
Tags: db link usage syntax