· What is Database link?
A database link is a schema object in one
database that enables you to access objects on another database. The other
database needs to be an Oracle Database system. However, to access non-Oracle
systems you must have to use Oracle Heterogeneous Services
Once you have created a database link, you can use it to refer to tabled and
views on the other database. In SQL statements, you can refer to a table or
view on the other database by appending @dblink to the table or view or view
name. You can query a table or view on the other database with the SELECT
statement. You can also access remote tables and views using any INSERT,
UPDATE, DELETE, or LOCK TABLES statement.
· Privileges
o
- To create a private database link, you must have the CREATE DATABASE LINK system privilege.
- To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege.
o And also you must have the CREATE SESSION system privilege on
the remote Oracle database.
GRANT CREATE DATABASE LINK TO ;
GRANT CREATE PUBLIC DATABASE LINK TO ;
GRANT DROP PUBLIC DATABASE LINK TO ;
·
Syntax
o
Private database link syntax
CREATE DATABASE LINK CONNECT TO IDENTIFIED BY USING ;
o Public database link syntax
CREATE PUBLIC DATABASE LINK CONNECT TO IDENTIFIED
BY USING ;
· Public Database and Private Database link
A public database link is available to all
users. If you omit this clause, the database link is private and available only
to you.
· Examples
o
Create private db link
CREATE DATABASE LINK CONNECT_PROD CONNECT TO
VINOD IDENTIFIED BY ORACLE USING ‘PROD’;
Note: connect_prod
is the database link name; also this is a private link so only the user who
created the link can use it.
o Create public db link
CREATE PUBLIC DATABASE LINK CONNECT_PROD
CONNECT TO VINOD IDENTIFIED BY ORACLE USING ‘PROD’;
Note: any
user can use the link.
o Drop a database link
DROP PUBLIC DATABASE LINK ;
DROP DATABASE LINK ;
o Close a database link
ALTER SESSION CLOSE DATABASE LINK ;
o Views:
USER_DB_LINKS
§ ALL_DB_LINKS
§ DBA_DB_LINKS
------------------------
No comments:
Post a Comment