分布式数据库

SQL> –创建数据库链路
SQL> create database link l2 connect to scott identified by tiger using ‘remoteorcl’;
create database link l2 connect to scott identified by tiger using ‘remoteorcl’
*
第 1 行出现错误:
ORA-01031: 权限不足

SQL> /

数据库链接已创建。

SQL> –在分布式数据库中执行查询
SQL> select ename,dname
2 from dept, emp@L2
3 where emp.deptno=dept.deptno;
from dept, emp@L2
*
第 2 行出现错误:
ORA-00942: table or view does not exist
ORA-02063: 紧接着 line (起自 L2)

SQL> ed
已写入 file afiedt.buf

1 select ename,dname
2 from dept, emp@L2
3* where emp.deptno=dept.deptno
SQL> /
from dept, emp@L2
*
第 2 行出现错误:
ORA-00942: table or view does not exist
ORA-02063: 紧接着 line (起自 L2)

SQL> /

ENAME DNAME

---

SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH

ENAME DNAME

---

JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

已选择14行。

SQL> /

ENAME DNAME

---

SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH

ENAME DNAME

---

JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

已选择14行。

SQL> /

ENAME DNAME

---

SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING123 ACCOUNTING
TURNER SALES
ADAMS RESEARCH

ENAME DNAME

---

JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

已选择14行。

SQL> host cls

SQL> –为emp@L2创建同义词
SQL> create sysnoym remoteemp for emp@L2;
create sysnoym remoteemp for emp@L2
*
第 1 行出现错误:
ORA-00901: 无效 CREATE 命令

SQL> create SYNONYM remoteemp for emp@L2;

同义词已创建。

SQL> ed
已写入 file afiedt.buf

1* create SYNONYM remoteemp for emp@L2
SQL> select ename,dname
2 from dept, remotemp
3 where remotemp.deptno=dept.deptno;
from dept, remotemp
*
第 2 行出现错误:
ORA-00942: 表或视图不存在

SQL> ed
已写入 file afiedt.buf

1 select ename,dname
2 from dept, remotemp
3* where remotemp.deptno=dept.deptno
SQL> /
from dept, remotemp
*
第 2 行出现错误:
ORA-00942: 表或视图不存在

SQL> ed
已写入 file afiedt.buf

1 select ename,dname
2 from dept, remoteemp
3* where remoteemp.deptno=dept.deptno
SQL> /

ENAME DNAME

---

SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING123 ACCOUNTING
TURNER SALES
ADAMS RESEARCH

ENAME DNAME

---

JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

已选择14行。

SQL> host cls

SQL> –利用触发器实现数据的同步更新
SQL> create or replace trigger syncsal
2 after update
3 on emp
4 for each row
5 begin
6 update remoteemp set sal=:new.sal where empno=:new.empno;
7
8 end;
9 /

触发器已创建

SQL> select sal from emp where empno=7839;

SAL

  8086                                                                      

SQL> update emp set sal=sal+1 where empno=7839;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select sal from emp where empno=7839;

SAL

  8087                                                                      

SQL> spool off