Oracle DB LINKS Metadata Info ....
set linesize 300
SET LONG 9000
SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM dba_db_links a;
==================================================================
set lines 180 pages 999
col owner for a15
col DB_LINK for a15
col USERNAME for a15
col HOST for a20
col CREATION_DATE for a20
col OWNER_LABEL for a20
SELECT owner,
CASE
WHEN owner = 'PUBLIC' THEN '<<<<PUBLIC'
ELSE owner
END AS owner_label,
db_link,
username,
host,
TO_CHAR(created, 'DD-MM-YYYY HH24:MI:SS') AS creation_date
FROM dba_db_links
WHERE 1=1
-- AND username = 'XXXX'
;
===============================================
IDENTIFIED BY VALUES is not possible in dblink from Version 11.2.0.4 onward
CREATE DATABASE LINK ANUJ1 CONNECT TO ANUJ123 IDENTIFIED BY VALUES '07B98AF7604AFAF492AC70E41300D9D003064EDDE1C5E96075479AB5A8036AF0A2AA16183623A084811F6F18EB2245A43DE9DCE1E4B736924F7434DC98A84699DE9B6787A0EE65BDCC8EEA47755648C2ADF55478060A2F4A1B33A716A97747900DABACFAC1674D73CBD1A2D9D2EED0679ABEC40D0B589AE572BAB73D6611559D' using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.000.00.00)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = orcl )))'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string
CREATE DATABASE LINK ANUJ2 CONNECT TO ANUJ123 IDENTIFIED BY vihaan123 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.000.00.00)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = orcl )))'
Database link created.
CREATE DATABASE LINK "CDB02" CONNECT TO C##PDBCLONEDBA IDENTIFIED BY "XXXXXXX"
USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.0)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = CDBAUX02)))';
Session altered.
SQL> select * from dual@CDBAUX02;
D
--
x
set long 1000000 linesize 1000 pagesize 0 feedback off trimspool on verify off serveroutput on
select dbms_metadata.get_ddl('DB_LINK','ANUJ2') from dual;
CREATE DATABASE LINK "ANUJ2"
CONNECT TO "ANUJ123" IDENTIFIED BY VALUES ':1'
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.00
0.00.00)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = orcl )))'
Now you will get ':1' not password value
=============
set pagesize 300
col tname for a30
select * from tab@dblink ;
CREATE DATABASE LINK dblink CONNECT TO anuj IDENTIFIED BY vihaan123 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=irac-scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=irac)))';
insert into sys.link$ (OWNER#, NAME, CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX)
select OWNER#, 'DBLINK1', CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX
from sys.link$ where name=upper('dblink');
insert new row !!!
insert into sys.link$ (OWNER#, NAME, CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX)
select OWNER#, 'DBLINK1', CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX
from sys.link$ where name=upper('dblink');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
set pagesize 300
col tname for a30
select * from tab@dblink1 ;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ANUJ1 TABLE
ANUJ_BIG_TABLE TABLE
BIN$54ZgrWd1M/zgUyL1akAE8A==$0 TABLE
BIN$66UJVfKoHNjgUyL1akBIsA==$0 TABLE
BIN$ZaQvn3FuYKPgUyL1akA0wg==$0 TABLE
BIN$ZjFB7+oJfnbgUyL1akDZsg==$0 TABLE
BV_DEMO TABLE
DEPT TABLE
EMP TABLE
SPM_TEST_TAB TABLE
T TABLE
TEST TABLE
TEST9 TABLE
TEST_LOB TABLE
TT TABLE
T_INTERIM TABLE
16 rows selected.
========
How to Check if a Database Link is Private / Public or Shared / not Shared (Doc ID 237477.1)
If the value of the OWNER column of the view DBA_DB_LINKS is PUBLIC, it is a public database link.
All other values indicate it is a private database link.
SQL> select OWNER from dba_db_links where DB_LINK = 'MYLINK';
To check for shared database links, query sys.link$
SQL> select NAME from sys.link$ where AUTHUSR is not null;
from web --
SET LINESIZE 400
SET PAGESIZE 300
SET FEEDBACK ON
SET TRIMSPOOL ON
COLUMN DB_UNIQUE_NAME FORMAT A20
COLUMN OWNER FORMAT A20
COLUMN USERNAME FORMAT A20
COLUMN HOST FORMAT A30
COLUMN DB_LINK FORMAT A20
COLUMN TESTSQL FORMAT A100
-- Query
SELECT db.db_unique_name,
li.owner,
li.db_link,
li.username,
li.host,
'prompt Link=' || li.db_link || ';' || CHR(10) ||
CASE
WHEN li.owner = 'PUBLIC' THEN
'select ''ok'' from dual@' || li.db_link || ';'
ELSE
'alter session set current_schema = ' || li.owner || ';' || CHR(10) ||
'create or replace function dblinktester return varchar2 is ' || CHR(10) ||
' v_result varchar2(5);' || CHR(10) ||
'begin' || CHR(10) ||
' select ''ok'' into v_result from dual@' || li.db_link || ';' || CHR(10) ||
' return v_result;' || CHR(10) ||
'end;' || CHR(10) ||
'/' || CHR(10) ||
'select dblinktester as result from dual;' || CHR(10) ||
'drop function dblinktester;'
END AS testsql
FROM v$database db
CROSS JOIN dba_db_links li;
======
select
db_link,
in_transaction
from
v$dblink
set serveroutput on
begin
for open_db_link in (
select
db_link,
in_transaction
from v$dblink
) loop
if open_db_link.in_transaction <> 'NO' then
dbms_output.put_line('Cannot close db link ' || open_db_link.db_link || ' as it is in a transaction.');
else
-- execute immediate 'alter session close database link ' || open_db_link.db_link;
dbms_output.put_line('db link ' || open_db_link.db_link || ' was closed.');
end if;
end loop;
end;
/
