oracle创建远程数据库连接dblink

创建dblink流程：

1)PLSQL Developer 登录sys用户，"连接为"选sysdba

select * from user_sys_privs t where t.privilege like upper('%link%');
-------------------------
查询结果：
1 SYS DROP PUBLIC DATABASE LINK NO
2 SYS CREATE DATABASE LINK         NO
3 SYS CREATE PUBLIC DATABASE LINK NO
-------------------------
然后，给scott附权限
grant CREATE PUBLIC DATABASE LINK to scott;
grant DROP PUBLIC DATABASE LINK to scott;

 

2）给scott解锁
在运行里面输入cmd在DOS模式下输入sqlplus，以system用户名登录，密码是刚装oracle时自己填写的密码orcl，登录进去以后。

SQL> conn sys/sys as sysdba;       （分号是必须的但是我是以system登录的所在这不应该写conn sys/sys as sysdba应该写conn system/orcl as sysdba;）
         Connected.
SQL> alter user scott account unlock;
         User altered.
SQL> commit;
         Commit complete.
SQL> conn scott/tiger//请输入新密码，并确认后OK
Password changed
Connected.

 

3）PLSQL Developer 登录scott用户，"连接为"选sysdba

CREATE PUBLIC DATABASE LINK db1
　　CONNECT TO app IDENTIFIED BY app
　　USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))'

测试:
select * from 表@db1;

 

此时在本机任何用户下均可查询db1