Difference between revisions of "Oracle Administration Manage Database Links"

From Robs_Wiki
Jump to: navigation, search
(View current database links)
(Change a database link password)
 
Line 43: Line 43:
 
</pre>
 
</pre>
  
 +
or you could write a little script for it:
 +
<pre>
 +
spool /home/oracle/dbadir/rob/drop_db_link.log
 +
set echo on
 +
prompt
 +
prompt *** dropping database link pedro.DEV01.MYCOMP.ORG ***
 +
prompt
 +
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
 +
prompt *** dropping database links ibis ***
 +
prompt
 +
-- 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
 +
prompt *** dropping database link pietpuk.DEV01.MYCOMP.ORG ***
 +
prompt
 +
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
 +
</pre>
 
== Change a database link password ==
 
== Change a database link password ==
 
<pre>
 
<pre>

Latest revision as of 14:57, 11 February 2020

View current database links

dev_show_db_links.sql

Check the status of the private and public database links

admin_check_dblinks.sql

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

select
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
begin
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
prompt *** dropping database link pedro.DEV01.MYCOMP.ORG ***
prompt
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
prompt *** dropping database links ibis ***
prompt
-- 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
prompt *** dropping database link pietpuk.DEV01.MYCOMP.ORG ***
prompt
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

Query;

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
/

Results:

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

  87728505          2 18-DEC-2019 16:17:35       ROBLA                dbserver1              DBLINK_INFO: (SOURCE_GLOBAL_NAME=TSTDB.example.com, DBLINK_NAME=ROB_LINK.example.com, SOURCE_AUDIT_SESSIONID=4294967295)

Audit database link operations

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