Create DBLink SOP

Create DBLink SOP

Scenario:

User want to create DBLink between 10.138.4. and 10.134.92.

Table name : MKPF

Solution:

1.      telnet 10.134.92. and create user hghlink:

   Login with oracle user :Su – ora

Start up sqlplus : sqlpus ‘/as sysdba’

Create user sidlink:

create user hghlink identified by oracle;(#username是10.138.4.上的oracle 用戶﹐可以由對方提供用戶名和密碼)

Note: oracle is the password of lhlink

 

2.      Remote login to 10.138.4. & create public database link:

Login with oracle user :Su – ora

Start up sqlplus : sqlpus ‘/as sysdba’

   Create public database link:

create public database link SID connect to hghlink identified by 'oracle' using 'SID';

 

Note:

“hgh” is the name of DBlink

“hghlink” is the name of user

'oracle' is the password of hghlink

'SID’ is the oracle SID of 10.134.92

 

3.       (cd  /opt/oracle/ora92/network/admin/tnsnames.ora 注意﹕修改此文件前最好先備份一個)

Appand the following:

 

SID =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.134.92)(PORT = 1527))

    )

    (CONNECT_DATA =

      (SID = hgh)

      (SERVER = DEDICATED)

    )

  )

 

4.      test the connection: tnsping SID(顯示OK的話則沒問題)

 

5.      grant privilege to user:

grant connect to hghlink;

commit;

grant select on sapr3.mkpf to hghlink; 

commit;

 

6.      sqlplus caisit/caisitdb2005@sid

能連上則OK,可以用select count(*) from sapr3.mkpf 語句測試下能否訪問

若要直接訪問表mkpf則需要建一個同義詞﹕create synonym mkpf for sapr3.mkpf則OK.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21495077/viewspace-618236/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21495077/viewspace-618236/