RDBMS
SQLPLUS等工具
利用本地的工具,訪問遠程的數據庫
定義數據庫鏈路
CREATE DATABASE LINK [db_link_name] CONNECT TO
[user_name] IDENTIFIED BY [password]USING '[tns_name]';
?[DB_LINK_NAME]:是所要連接的數據庫的服務名,也就是該數據庫的真實名稱(通常就是SID)。
?[USRE_NAME]:是所要連接的用戶名稱。
?[PASSWORD]:是所要連接的用戶的密碼。
?[TNS_NAME]:是所要連接的數據庫的服務命名。
例如:
?
select ename,dname
from dept, emp@L2
where emp.deptno=dept.deptno;
l L2即為前面所創建的數據庫鏈路。
lcreate synonym emp1 for emp@L2 ;
selectdname,ename,sal
fromemp1, dept
whereemp1.deptno=dept.deptno;
createview emp
as
select * from emp1@L1
union
select * from emp2@L2 ;
Select* from emp;
插入操作:
?insert into emp select * from emp@L2;
數據復制:
?create table emp as select * from emp@L2;
定義快照維護關系表的異步副本
?指在主表修改後的指定時間內刷新副本,用於主表修改少,但頻繁查詢的表。
create snapshot emp(
refresh start with sysdate
next next_day(sysdate,’Monday’)
as select * from emp@L1 ;
select* from emp; (本地數據庫中查詢快照的數據)
createor replace trigger update_emp
afterupdate on emp
foreach row
begin
update emp@L2 set emp.sal=:new.sal
where emp.empno=:new.empno;
end;
SQL> --創建數據庫鏈路
SQL> conn / as sysdba
已連接。
SQL> grant create database link to scott;
授權成功。
SQL> conn scott/tiger
已連接。
SQL> create database link l2 connect to scott identified by tiger using 'remoteorcl';
數據庫鏈接已創建。
SQL> select ename,dname 2 from dept, emp@L2 3 where emp.deptno=dept.deptno; 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> create synonym remoteemp for emp@l2;
同義詞已創建。
SQL> select ename,dname 2 from dept, remoteemp 3 where remoteemp.deptno=dept.deptno; 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> select ename,dname 2 from dept, remoteemp 3 where remoteemp.deptno=dept.deptno; 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> create or replace trigger sycnempsal
2 after update
3 on emp
4 for each row
5 begin
6
7 update remoteemp set sal=:new.sal where empno=:new.empno;
8
9 end;
10 /
觸發器已創建
SQL> select sal from emp where empno=7839;
SAL
----------
8186
SQL> update emp set sal=sal+1 where empno=7839;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select sal from emp where empno=7839;
SAL
----------
8187
create or replace trigger sycnempsal after update on emp for each row begin update remoteemp set sal=:new.sal where empno=:new.empno; end; /