本文展示了如何在Oracle数据库中创建数据库链接，并通过示例演示了如何使用tnsnames.ora配置文件及easyconnect方式进行跨数据库查询。具体包括授予创建数据库链接权限、创建数据库链接以及从链接的数据库中查询表信息。
使用tnsnames.ora
SQL> grant create database link to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> create database link lk connect to hr identified by hr using 'enmo1';
Database link created.
SQL> select * from tab@lk;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
EMP_SAL_CHANGE TABLE
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
SP_LOGERR TABLE
10 rows selected.
SQL> conn hr/hr
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
EMP_SAL_CHANGE TABLE
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
SP_LOGERR TABLE
10 rows selected.
使用easy connect
SQL> create database link d_1 connect to scott identified by tiger using'192.0.2.5:1521/enmo1';
Database link created.
SQL> select * from tab@d_1;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
扫一扫
4758
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
