Oracle Database Link

Oracle Database Links are of two types, Private or Public. Private is the default one. let’s start with connecting with database, in this Example, My two Database are on same Machine. For creating Database link, you need to have TNS entry for remote database.


C:\Documents and Settings\A Rahim Khan>set oracle_sid = catdb;

C:\Documents and Settings\A Rahim Khan>sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 2 10:52:28 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
catdb

SQL> create user hr_link identified by hr;

User created.

SQL> grant connect,resource,CREATE DATABASE LINK hr_link;

Grant succeeded.



Here, my remote database is ORCL1, I am creating a Private Database Link for HR Schema.


SQL> conn hr_link/hr
Connected.

SQL> create database link orcl1_priv_link connect to hr identified by hr using 'orcl1';

Database link created.

SQL> set lines 1200;
SQL> set pages 1200;
SQL> select * from departments@orcl1_priv_link;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400
           70 Public Relations                      204        2700
           80 Sales                                 145        2500
           90 Executive                             100        1700
          100 Finance                               108        1700
          110 Accounting                            205        1700
          120 Treasury                                         1700
          130 Corporate Tax                                    1700
          140 Control And Credit                               1700
          150 Shareholder Services                             1700
          160 Benefits                                         1700
          170 Manufacturing                                    1700
          180 Construction                                     1700
          190 Contracting                                      1700
          200 Operations                                       1700
          210 IT Support                                       1700
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700
          250 Retail Sales                                     1700
          270 Payroll                                          1700

25 rows selected.

Thanks
A Rahim Khan

Advertisements
  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: