- A database link is a schema object in one database that enables you to access objects on another database.
- The user must have CREATE DATABASE LINK PRIVILEGE to create a DB link. To create a public database link the user must have CREATE PUBLIC DATABASE LINK PRIVILEGE. In DB link only the owner of the db link can access objects in remote Database,where as in public db link any user of the database can access objects on remote database by specifying the DB-Link name.
- While creating DB link we can connect to existing schema like GL or PA or,create a user in remote database give him privileges to access selected objects in database through views and synonyms.To create a simple DB_Link:
- Add TNS to your instance and check weather you are able to tnsping remote database or not.
- Use the following command to create a simple DB-Link
SQL> CREATE DATABASE LINK
IDENTIFIED BY USING ;
- The name of the DB link should be same as database name if the parameter GLOBAL_NAME=’TRUE’.If u modify this parameter we need to bounce the instance.
- We cannot alter database link,we have to drop existing one and create again and we can’t drop database link’s in another user schema.
- To drop a database link,we can use the following syntax;
SQL>DROP DATABASE LINK ;
Query the table DBA_DB_LINKS or USER_DB_LINKS for created database link information.
- To check weather you are succeeded with db link creation, try this;
SQL> SELECT count(*) FROM EMP@;
- You can create synonym for an object on remote DB so that the user didn’t understands that the object is in remote database.
SQL> CREATE SYNONYM FOR scott.emp@;