create /drop a database link

How to drop a database link

Dropping a database link from your Oracle database is as easy as doing the following:
drop database link remotedb; 
or
drop public database link remotedb;

You will need no other system privilege other than the 'create database link' privilege granted directly
to your username or granted via a role to drop your own database link.

It is not possible to drop a database link belonging to another user.

If you try to specify another schema's database link by qualifying it with a name, 
Oracle will just look for a private database link in your schema with a name 
which includes the other schema's name and will not find it.

To drop a public database link, you will need the system privilege 'drop public database link'.
For example: 

SQL> connect / as sysdba
Connected.

SQL> drop database link scott.remotedb;
drop database link scott.remotedb
*
ERROR at line 1:
ORA-02024: database link not found


SQL> connect scott/tiger
Connected.
SQL> drop database link scott.remotedb;
drop database link scott.remotedb
*
ERROR at line 1:
ORA-02024: database link not found


SQL> drop database link remotedb;

Database link dropped.

 how to create a db link 

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 from @; 

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

*********************************END*********************************************