Creating Database link in database

[Lachlann]

I am using oracle 9i enterprise edition and it would a new practice for me.I, actually wanted to know the basic concepts of database link and what are the privileges should I have to create this link.What is database link? What is the difference between a public and a private database link?

[Zecho]

Database Link:

Database link is a pointer that is used to access the data from remote database while you are working on local database.You have some proper privileges to create this link.

To know the current privilege regarding database link,you have to query on the ROLE_SYS_PRIVS view :

SELECT DISTINCT PRIVILEGE AS "Database Link Privileges"
FROM ROLE_SYS_PRIVS
WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK',
'CREATE PUBLIC DATABASE LINK');

Or you can execute the privs.sql script to know about link privileges type :

SQL> @privs

[Reegan]

When you create a database link, you have to decide which user will permission to access it.

I am going to create a private database link which will point to remote database. I am a normal user and have such privilege to create the private database link.

The syntax would be as follows :

CREATE DATABASE LINK <Link name>
CONNECT TO <current user> USING <Remote database>;

[opaper]

Creating public database link:

The creation of public database link,you have "CREATE PUBLIC DATABASE LINK" privilege and use the PUBLIIC keyword.

The syntax to create the public database link:

CREATE PUBLIC DATABASE LINK <Public_Link_name>
CONNECT TO <Current user> USING <'Name of remote database'>;