# Basic way to Create and Drop DB LINK
CREATE PUBLIC DATABASE LINK SCOTTETMFDBLINK.WORLD CONNECT TO ETMFMT IDENTIFIED BY ETM0107FMT USING 'ETMFMIG.WORLD';
CREATE PUBLIC DATABASE LINK SCOTTETMFDBLINK.WORLD CONNECT TO ETMFMT IDENTIFIED BY ETM0107FMT USING '(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP.world)(PROTOCOL=TCP)(Host=usabhbmsvhz303.net.bms.com)(Port=1521)))(CONNECT_DATA=(SID=ETMFMIG)))';
DROP PUBLIC DATABASE LINK SCOTTETMFDBLINK.WORLD;
DROP DATABASE LINK EDM_REPORTING_DB_LINK.WORLD;
# Granting Privileges
GRANT CREATE DATABASE LINK TO SCOTT;
GRANT DROP PUBLIC DATABASE LINK TO SCOTT;
GRANT DROP DATABASE LINK TO SCOTT;
REVOKE CREATE PUBLIC DATABASE LINK FROM SCOTT;
# Create DB LINK for another user via Oracle Procedure.
SQL> CREATE or replace PROCEDURE scott.create_db_link AS
BEGIN
EXECUTE IMMEDIATE 'create database link LINK1 connect to scott identified by tiger using ''testdb''';
END create_db_link;
2 3 4 5
6 /
Procedure created.
SQL> show user
USER is "SYS"
SQL> exec scott.create_db_link
PL/SQL procedure successfully completed.
SQL> select * from dba_db_links where OWNER='SCOTT';
OWNER DB_LINK USERNAME HOST CREATED
----- ------- -------- ---- -------
SCOTT LINK1 SCOTT testdb 04-NOV-11
SQL> drop database link scott.LINK1;
drop database link scott.LINK1
*
ERROR at line 1:
ORA-02024: database link not found
SQL> CREATE PROCEDURE scott.drop_db_link AS
BEGIN
EXECUTE IMMEDIATE 'drop database link LINK1';
END drop_db_link; 2 3 4
5 /
Procedure created.
SQL> exec scott.drop_db_link
PL/SQL procedure successfully completed.
SQL> select * from dba_db_links where OWNER='SCOTT';
no rows selected
-- drop procedure scott.crt_db_link;
-- drop procedure scott.drop_db_link
