Use Oracle Database Link
Always test these 'use oracle database link' commands on a test server
before implementing them on a production server.
before implementing them on a production server.
Once a database link has been created ( how to create a db link ),
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 BOSTONThe 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 linksSQL> 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
Next: How to 'drop' a database link.
Click here to read about troubleshooting database link errors.
