Home > Database, Oracle > Database Links

Database Links

  • 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 PRIVILEGEIn 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:
    1. Add TNS to your instance and check weather you are able to tnsping remote database or not.
    2. Use the following command to create a simple DB-Link

SQL> CREATE DATABASE LINK
CONNECT TO
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@;

Advertisements
Categories: Database, Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: