Oracle DB LINKS Metadata

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;

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 pagesize 300 

col DB_UNIQUE_NAME for a20

col OWNER for a20

col USERNAME for a20

col HOST for a30

col TESTSQL for a15

col DB_LINK for a20 

col TESTSQL for a100

select db.DB_UNIQUE_NAME, li.owner, li.db_link, li.username, li.host

    ,'prompt Link='||li.db_link||';' ||chr(10)

    || case when owner = 'PUBLIC' then -- Public link -> do simple select

         ' select ''ok'' from dual@'||li.db_link||';'

       else -- non public link -> must be run as the link owner

  'alter session set current_schema = '||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;'||chr(10)

       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;

/