Thursday, September 13, 2012

Create Database Link

This Topics talks About how to create Database link , what i need

Solution

1-If you are creating public dblink, you will need a user with the following permissions:

create public database link 
drop public database link 

 If you are creating dblink, you will need a user with the following permission: 

create database link

2-check Global_names parameter in database , if you create public database link its should be TRUE, otherwise FALSE 

How To check it :

-Show Parameter global 
OR
-SELECT name, valueFROM gv$parameter WHERE name ='global_names';
To Change its Value :

Alter system set = Scope=Spfile/Memory 
3) Create the database link: 

SQL> CREATE [SHARED] [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER USING '';

 Please note that: 
* The single quotes around the service name are mandatory 
* The service name used above must be in the TNSNAMES.ORA file on the server. If it does not existyou can create one like below: 

connection_link = 
(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = n host name>)
(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = )))

4) Make sure that the database link is created:

SQL> SELECT * FROM user_db_links;

Also you Can Check Database Domain :

SQL> SELECT name, valueFROM gv$parameter WHERE name IN ('db_name', 'db_domain');

sometimes you need to set database domain , refer to  Note 578668.1

Enjoy 

Osama Mustafa  

2 comments:

  1. Hi Osama ;

    SQL>show parameter global_name;

    NAME TYPE VALUE
    global_names boolean FALSE

    SQL>create public database link mydba connect to scott identified by tiger using 'ORCL';

    Database link created.

    SQL> select * from emp@mydba

    select count(*) from emp@mydba;

    COUNT(*)
    14

    Even i set global_name='FALSE' i can create public database link. then
    why should i set global_name=*'TRUE'

    Thanks in advance ..

    ReplyDelete
  2. to use database.domain
    Check
    http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams088.htm

    ReplyDelete