数据库连接 DB_LIKE
访问远程数据库资源的一种方法
确保网络畅通
create public database link ts connect to scott identified by seker using 'orcl'
create public 公共的访问模式
database link ts 连接名称
connect to scott 连接用户名
identified by seker 用户密码
SQL> conn / as sysdba
Connected.
SQL> create public database link ts connect to scott identified by seker using 'ORCL';
Database link created.
SQL> conn scott/seker
Connected.
SQL> select ename,sal from emp where ename='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
SQL> select ename,sal from emp@ts where ename='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
SQL> update emp@ts set sal=sal+1 where ename='SCOTT';
1 row updated.
SQL> select ename,sal from emp where ename='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
SQL> select ename,sal from emp@ts where ename='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3001
SQL>
查看数据库连接
col OWNER for a10
col USERNAME for a10
col host for a10
col created for a10
SQL> select owner,username,host,created from dba_db_links;
OWNER USERNAME HOST CREATED
---------- ---------- ---------- ----------
PUBLIC SCOTT ORCL 02-APR-11
SQL>
删除
SQL> drop public database link ts;
Database link dropped.
访问远程数据库资源的一种方法
确保网络畅通
create public database link ts connect to scott identified by seker using 'orcl'
create public 公共的访问模式
database link ts 连接名称
connect to scott 连接用户名
identified by seker 用户密码
using 'orcl' orcl是tnsname.ora中的定义
dblink怎么弄?经理给我了一个ip地址,一个用户名,一个秘密,让我建一个dblink在他的数据库中创建两个表
create public database link db119_bak
connect to user identified by password
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =ip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = serviceName)
)
)';
SQL> conn / as sysdba
Connected.
SQL> create public database link ts connect to scott identified by seker using 'ORCL';
Database link created.
SQL> conn scott/seker
Connected.
SQL> select ename,sal from emp where ename='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
SQL> select ename,sal from emp@ts where ename='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
SQL> update emp@ts set sal=sal+1 where ename='SCOTT';
1 row updated.
SQL> select ename,sal from emp where ename='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
SQL> select ename,sal from emp@ts where ename='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3001
SQL>
查看数据库连接
col OWNER for a10
col USERNAME for a10
col host for a10
col created for a10
SQL> select owner,username,host,created from dba_db_links;
OWNER USERNAME HOST CREATED
---------- ---------- ---------- ----------
PUBLIC SCOTT ORCL 02-APR-11
SQL>
删除
SQL> drop public database link ts;
Database link dropped.