Solved

public or private links

Posted on 2014-03-02
3
316 Views
Last Modified: 2014-03-03
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?
0
Comment
Question by:pma111
3 Comments
 
LVL 3

Author Comment

by:pma111
ID: 39899044
for 1) would you refer to the "owner" column if its  a named user its a "private" link?
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
ID: 39899068
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.
0
 
LVL 23

Assisted Solution

by:David
David earned 250 total points
ID: 39899326
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now