Oracle:关于分布式数据库

简介: 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>

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

目录
相关文章
|
7天前
|
存储 Oracle 关系型数据库
数据库数据恢复—ORACLE常见故障的数据恢复方案
Oracle数据库常见故障表现: 1、ORACLE数据库无法启动或无法正常工作。 2、ORACLE ASM存储破坏。 3、ORACLE数据文件丢失。 4、ORACLE数据文件部分损坏。 5、ORACLE DUMP文件损坏。
40 11
|
7天前
|
Cloud Native 关系型数据库 分布式数据库
PolarDB 分布式版 V2.0,安全可靠的集中分布式一体化数据库管理软件
阿里云PolarDB数据库管理软件(分布式版)V2.0 ,安全可靠的集中分布式一体化数据库管理软件。
|
20天前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
一台Oracle数据库打开报错,报错信息: “system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。管理员联系我们数据恢复中心寻求帮助,并提供了Oracle_Home目录的所有文件。用户方要求恢复zxfg用户下的数据。 由于数据库没有备份,无法通过备份去恢复数据库。
|
1月前
|
关系型数据库 分布式数据库 数据库
PostgreSQL+Citus分布式数据库
PostgreSQL+Citus分布式数据库
61 15
|
26天前
|
存储 Oracle 关系型数据库
oracle数据恢复—Oracle数据库文件大小变为0kb的数据恢复案例
存储掉盘超过上限,lun无法识别。管理员重组存储的位图信息并导出lun,发现linux操作系统上部署的oracle数据库中有上百个数据文件的大小变为0kb。数据库的大小缩水了80%以上。 取出&并分析oracle数据库的控制文件。重组存储位图信息,重新导出控制文件中记录的数据文件,发现这些文件的大小依然为0kb。
|
12天前
|
存储 Oracle 关系型数据库
服务器数据恢复—华为S5300存储Oracle数据库恢复案例
服务器存储数据恢复环境: 华为S5300存储中有12块FC硬盘,其中11块硬盘作为数据盘组建了一组RAID5阵列,剩下的1块硬盘作为热备盘使用。基于RAID的LUN分配给linux操作系统使用,存放的数据主要是Oracle数据库。 服务器存储故障: RAID5阵列中1块硬盘出现故障离线,热备盘自动激活开始同步数据,在同步数据的过程中又一块硬盘离线,RAID5阵列瘫痪,上层LUN无法使用。
|
1月前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
46 7
|
1月前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
29 5
|
4月前
|
监控 Oracle 关系型数据库
"深度剖析:Oracle SGA大小调整策略——从组件解析到动态优化,打造高效数据库性能"
【8月更文挑战第9天】在Oracle数据库性能优化中,系统全局区(SGA)的大小调整至关重要。SGA作为一组共享内存区域,直接影响数据库处理能力和响应速度。本文通过问答形式介绍SGA调整策略:包括SGA的组成(如数据缓冲区、共享池等),如何根据负载与物理内存确定初始大小,手动调整SGA的方法(如使用`ALTER SYSTEM`命令),以及利用自动内存管理(AMM)特性实现智能调整。调整过程中需注意监控与测试,确保稳定性和性能。
359 2
|
5月前
|
存储 缓存 Oracle
Oracle数据库可扩展性和性能
【7月更文挑战第6天】
90 7

推荐镜像

更多