Oracle Administration Manage Database Links

From Robs_Wiki
Jump to: navigation, search

View current database links


Check the status of the private and public database links


Create a database link

create database link

Drop a database link

Note that a private database link can only dropped by the user itself, not even by sys:

SQL> @show_db_links

OWNER                DB_LINK                        USERNAME             HOST
-------------------- ------------------------------ -------------------- ------------------------------
SCOTT                PROD.EBS.UNIVERSE              TIGER                MARS

SQL> drop database link PROD.EBS.UNIVERSE;
drop database link PROD.EBS.UNIVERSE
ERROR at line 1:
ORA-02024: database link not found

So a way around this is to become the user via a proxy:

SQL> alter user scott grant connect through robla;
SQL> conn robla[scott]
SQL> drop database link PROD.EBS.UNIVERSE;

Another method would be to create a procedure to do the job:

SQL> create procedure scott.weg_ermee as
execute immediate 'drop database link PROD.EBS.UNIVERSE';
end weg_ermee;

SQL> exec scott.weg_ermee;
PL/SQL procedure succesfully completed

or you could write a little script for it:

spool /home/oracle/dbadir/rob/drop_db_link.log
set echo on
prompt *** dropping database link pedro.DEV01.MYCOMP.ORG ***
alter user pedro grant connect through robla;
grant create database link to pedro;
connect robla[pedro]/&&password
drop database link DEV01.MYCOMP.ORG;
connect robla/&&password
revoke create database link from predro;
-- leaving the proxy access, always handy to have ...

prompt *** dropping database links ibis ***
-- grant drop database link to fred;
-- this user has this privilege permantly
connect robla[fred]/&&password
drop database link DEV03.MYCOMP.ORG;
drop database link DEV04.MYCOMP.ORG
connect robla/&&password

prompt *** dropping database link pietpuk.DEV01.MYCOMP.ORG ***
alter user pietpuk grant connect through robla;
grant create database link to pietpuk;
connect robla[pietpuk]/&&password
drop database link DEV01.MYCOMP.ORG ;
connect robla/&&password
revoke create database link from pietpuk;

spool off

Change a database link password

SQL> grant alter database link to scott;

SQL> connect robla[scott]

SQL> alter database link SCOTT.TIGER.ORG connect to scotty identified by manager;

Identify database link connections


col userid format a20
--col ntimstamp# format a20
col userhost format a32
col comment$text format a60
set linesize 200
select * from 
(select sessionid, statement, to_char(ntimestamp#,'DD-MON-YYYY HH24:MI:SS') ntimestamp#, userid, userhost, comment$text
from sys.aud$ where comment$text like '%DBLINK%' order by ntimestamp# desc)
where rownum < 11


 SESSIONID  STATEMENT NTIMESTAMP#                USERID               USERHOST                         COMMENT$TEXT
---------- ---------- -------------------------- -------------------- -------------------------------- ------------------------------------------------------------
  87747563          2 19-DEC-2019 07:49:33       ROBLA                dbserver1              DBLINK_INFO: (,, SOURCE_AUDIT_SESSIONID=4294967295)

  87728505          2 18-DEC-2019 16:17:35       ROBLA                dbserver1              DBLINK_INFO: (,, SOURCE_AUDIT_SESSIONID=4294967295)

Audit database link operations

Oracle 12c provides a DBLINK_INFO column in SYS.UNIFIED_AUDIT_TRAIL.