CODEWITHSUNDEEP
oracle-databaseoracle12cdblink
Gaurav Mittal
Gaurav Mittal

Reputation: 21

How to identify if the DBlink created in Oracle12c is shared or not

I have created couple of DB links. Some are shared and some are not.

I created shared DB Link:

CREATE SHARED DATABASE LINK dblink_name CONNECT TO username IDENTIFIED BY password AUTHENTICATED BY schema_name IDENTIFIED BY password USING 'service_name';

I created Normal DBlink as:

create database link dblink_name connect to username identified by password using service_name;

Is there a way where i can identify if the dblink created is shared mode or not.

Upvotes: 2

Views: 2233

Answers (1)

Jon Heller
Jon Heller

Reputation: 36922

There's no simple way to check the data dictionary to determine if a link is shared or not. Below are two workarounds, using SYS.LINK$ and DBMS_METADATA, but they each have problems.

SYS.LINK$

The undocumented table SYS.LINK$ contains this information in a bitmap, as explained by Solomon Yakobson in this thread:

select name from sys.link$ where bitand(flag,1) = 1;

But that table contains password hashes and is only viewable by SYS. You could perhaps grant permission to that table, which could raise some security concerns. Or create a view on top of it and then grant that view, although you're typically not supposed to create objects in the SYS schema.

DBMS_METADATA

The package DBMS_METADATA recreates the link DDL and can be used to check for the "SHARED" keyword.

select owner, db_link
from dba_db_links
where lower(dbms_metadata.get_ddl('DB_LINK', db_link, owner)) like '%create shared%';

This solution can be slow, especially if the system has a large number of database links. And searching for text strings in source code may not be reliable. According to the syntax diagrams in the manual there are no other keywords that could come between CREATE and SHARED, but that may change or there may be undocumented features or behavior.

Upvotes: 1

Related Questions