SQL> host cls
SQL> show user
USER 为 "SCOTT"
SQL> conn / as sysdba
已连接。
SQL> grant create database link to scott;
授权成功。
SQL> conn scott/tiger
已连接。
SQL> --创建数据库链路
SQL> create database link mylink connect to scott identified by tiger using 'remoteorcl';
数据库链接已创建。
SQL> --查询远端的数据
SQL> --查询员工的信息,要求显示员工号,姓名和部门号
SQL> --假设本地只有部门表,员工表在远端
SQL> select e.empno,e.ename,d.dname
2 from dept d, emp@mylink e
3 where d.dno=e.dno;
where d.dno=e.dno
*
第 3 行出现错误:
ORA-00904: "E"."DNO": 标识符无效
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,d.dname
2 from dept d, emp@mylink e
3* where d.dno=e.dno
SQL> desc dept;
名称 是否为空? 类型
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,d.dname
2 from dept d, emp@mylink e
3* where d.deptno=e.deptno
SQL> /
EMPNO ENAME DNAME
---------- ---------------------------------------- --------------
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
EMPNO ENAME DNAME
---------- ---------------------------------------- --------------
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
已选择14行。
SQL> create synonym remoteemp for emp@mylink;
create synonym remoteemp for emp@mylink
*
第 1 行出现错误:
ORA-01031: 权限不足
SQL> create synonym remoteemp for emp@mylink;
同义词已创建。
SQL> ed
已写入 file afiedt.buf
1* create synonym remoteemp for emp@mylink
SQL> select e.empno,e.ename,d.dname
2 from dept d, remoteemp e
3 where d.deptno=e.deptno;
EMPNO ENAME DNAME
---------- ---------------------------------------- --------------
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
EMPNO ENAME DNAME
---------- ---------------------------------------- --------------
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
已选择14行。
SQL> create or replace trigger syncSalary
2 after update on emp
3 for each row
4 begin
5 update remoteemp set sal = :new.sal where empno=:new.empno;
6 commit;
7 end;
8 /
触发器已创建
SQL> host cls
SQL> select empno,ename,sal from emp where empno=7839;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
SQL> update emp set sal=sal+100 where empno=7839;
update emp set sal=sal+100 where empno=7839
*
第 1 行出现错误:
ORA-02055: 分布式更新操作失效; 要求回退
ORA-04092: COMMIT 不能在触发器中
ORA-06512: 在 "SCOTT.SYNCSALARY", line 3
ORA-04088: 触发器 'SCOTT.SYNCSALARY' 执行过程中出错
SQL> create or replace trigger syncSalary
2 after update on emp
3 for each row
4 begin
5 update remoteemp set sal = :new.sal where empno=:new.empno;
6 end;
7 /
create or replace trigger syncSalary
*
第 1 行出现错误:
ORA-02067: 要求事务处理或保存点回退
SQL> rollback;
回退已完成。
SQL> create or replace trigger syncSalary
2 after update on emp
3 for each row
4 begin
5 update remoteemp set sal = :new.sal where empno=:new.empno;
6 end;
7 /create or replace trigger syncSalary
8 after update on emp
9 for each row
10 begin
11 update remoteemp set sal = :new.sal where empno=:new.empno;
12 end;
13 /
警告: 创建的触发器带有编译错误。
SQL> create or replace trigger syncSalary
2 after update on emp
3 for each row
4 begin
5 update remoteemp set sal = :new.sal where empno=:new.empno;
6 end;
7 /
触发器已创建
SQL> select empno,ename,sal from emp where empno=7839;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
SQL> update emp set sal=sal+100 where empno=7839;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select empno,ename,sal from emp where empno=7839;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5100
SQL> exit