public or private links

pma111
pma111 used Ask the Experts™
on
1) is there any easy way to determine whether a database link is "public" or private?

2) where would you be able to tell, are there any clues in the dba_db_links table?

3) what exactly is meant by "private" link, and how can you tell which users have access to use this "private" link? I am assuming "public" essentially means anyone with an account in the local database can access the remote database.

4) also when a database link is active, how does a user query tables in that database once logged into their local database. do you have to add a database.table.colum type prefix to your sql?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
for 1) would you refer to the "owner" column if its  a named user its a "private" link?
Senior Database Administrator
Commented:
First, here's the doco on database links:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5005.htm#SQLRF01205

Note that there are three different types:  Public, Shared and Private.

I created three different types, test1 being private, test2 being public and test3 being shared.  Then I queried dba_db_links:

SQL> select * from dba_db_links;

OWNER      DB_LINK    USERNAME   HOST       CREATED
---------- ---------- ---------- ---------- ----------
SYSTEM     TEST1      DEMO       demo       02-MAR-14
PUBLIC     TEST2      DEMO       demo       02-MAR-14
SYSTEM     TEST3      DEMO       demo       02-MAR-14

Open in new window


That should cover point 1 and 2.

Point 3 is also covered in the doco, but a private db link can only be used by the user that created it.  Public can be used by anyone in the local database and for shared the user has to be valid on the remote system.

Finally for point 4, if the table in the remote system is called remote_table and the database link is called test1, the query looks like this:

select * from remote_table@test1 where col1 = value1;

Hope that helps.
David VanZandtOracle Database Administrator III
Commented:
There another facet at the remote database, whether or not the schema_owner.table_name has SELECT privileges granted to PUBLIC, or is restricted to named users.  A link without access will, of course, fail.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial