• 1. Oracle 培训技术服务中心 2008年12月
  • 2. 目 录第一章  ORACLE结构 第二章  SQL语句 第三章 索引和约束 第四章 视图和快照 第五章  备份恢复
  • 3. 第一章 ORACLE结构
  • 4. 第一章 ORACLE结构1.1 ORACLE例程 1.2 ORACLE数据库
  • 5. Oracle例程(instance):    Oracle 例程是后台进程和内存结构的组合。只有启动例程后,才能访问数据库中的数据。每次启动例程时,会分配系统全局区(SGA) 并启动Oracle 后台进程。 SGA 也称作共享全局区,用于存储数据库进程共享的数据库信息。它包含有关Oracle 服务器的数据和控制信息,在Oracle 服务器所在计算机的虚拟内存中分配。 后台进程代表调用进程执行各种功能。它们把为每个用户运行的多个Oracle 程序所处理的功能统一起来。后台进程执行输入/输出(I/O),并监视其它Oracle 进程来提高并行性,从而使性能和可靠性更加优越。1.1 ORACLE例程
  • 6. 系统全局区(System Global Aera,简称SGA):     1.1 ORACLE例程
  • 7. 系统全局区(System Global Aera,简称SGA)组成: Shared pool(共享池)含library cache(库高速缓存:存最近的PLSql和Sql)以及data dictionary cache(数据字典高速缓存:存数据定义、表、索引、列等对象) database buffer cache(数据库缓冲区高速缓存):存储数据块(block) redo log buffer:记录block的全部更改   large pool:分担shared pool部分工作以及用于RMAN和并行消息缓冲等 java pool:存储java命令 1.1 ORACLE例程
  • 8. Shared pool(共享池) : 是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域。Shared Pool的大小由参数SHARED_POOL_SIZE决定。在32位系统中,这个参数的默认值是8M,而64位系统中的默认值位64M。最大为4G。 库高速缓存(Library Cache):Library Cache中包括共享SQL区(Shared SQL Areas)、PL/SQL存储过程、包和控制结构(如锁、库缓存句柄),用于sql语句以及PL/SQL程序单元(存储过程、函数、包、匿名PL/SQL块和触发器)的解析、内存分配 数据字典高速缓存(Dictionary Cache):数据字典是关于数据库的参考信息、数据库的结构信息和数据库中的用户信息的一组表和视图的集合,如我们常用到的V$视图、DBA_视图都属于数据字典。在SQL语句解析的过程中,Oracle可以非常迅速的访问(如果需要的话)这些数据字典,在SQL Trace中,这种对数据字典的访问就被统计为回调(recursive calls)。1.1 ORACLE例程
  • 9. 数据库缓冲区高速缓存(Data buffer cache ) : Buffer Cache是SGA区中专门用于存放从数据文件中读取的的数据块拷贝的区域。Oracle进程如果发现需要访问的数据块已经在buffer cache中,就直接读写内存中的相应区域,而无需读取数据文件,从而大大提高性能(要知道,内存的读取效率是磁盘读取效率的14000倍)。Buffer cache对于所有oracle进程都是共享的,即能被所有oracle进程访问。 保持缓冲池(Keep Buffer Pool)用于缓存那些永久驻入内存的数据块。它的大小由参数DB_KEEP_CACHE_SZIE控制; 回收缓冲池(Recycle Buffer Pool)会立即清除那些不再使用的数据缓存块。它的大小由参数DB_RECYCLE_CACHE_SIZE指定; 默认的标准缓存池,也就是上面所说缓存,它的大小由参数的DB_CACHE_SIZE指定。1.1 ORACLE例程
  • 10. 重做日志缓冲池( Redo log buffer) : Redo Log Buffer是SGA中一段保存数据库修改信息的缓存。这些信息被存储在重做条目(Redo Entry)中.重做条目中包含了由于INSERT、UPDATE、DELETE、CREATE、ALTER或DROP所做的修改操作而需要对数据库重新组织或重做的必须信息。在必要时,重做条目还可以用于数据库恢复。重做条目是Oracle数据库进程从用户内存中拷贝到Redo Log Buffer中去的。重做条目在内存中是连续相连的。后台进程LGWR负责将Redo Log Buffer中的信息写入到磁盘上活动的重做日志文件(Redo Log File)或文件组中去的。 参数LOG_BUFFER决定了Redo Log Buffer的大小。它的默认值是512K(一般这个大小都是足够的),最大可以到4G。当系统中存在很多的大事务或者事务数量非常多时,可能会导致日志文件IO增加,降低性能。这时就可以考虑增加LOG_BUFFER。1.1 ORACLE例程
  • 11. 大池(large pool): 大池是SGA中的一块可选内存池,用于共享服务(Shared Server MTS方式中)的会话内存和Oracle分布式事务处理,并行查询,IO服务进程以及Oracle备份和恢复操作(启用了RMAN时),根据需要时配置。参数LARGE_POOL_SIZE设置大池的大小。大池是属于SGA的可变区(Variable Area)的,它不属于共享池。 Java池(Java Pool): Java池也是SGA中的一块可选内存区,它也属于SGA中的可变区。Java池的内存是用于存储所有会话中特定Java代码和JVM中数据。Java池的使用方式依赖于Oracle服务的运行模式。Java池的大小由参数JAVA_POOL_SIZE设置。Java Pool最大可到1G。1.1 ORACLE例程
  • 12. 系统全局区(System Global Aera,简称SGA):     1.1 ORACLE例程
  • 13. SGA总结: Shared pool:在防伪税控生产数据库中shared pool一般设为1G,部分业务量较大地区设为2G database buffer cache:在防伪税控生产数据库中database buffer cache一般设为4G或8G,部分业务量较大地区设为16-20G redo log buffer:在防伪税控生产数据库中redo log buffer一般设为4M   large pool:在防伪税控生产数据库中large pool一般不设,部分业务量较大地区设置为512M或1G java pool:在防伪税控生产数据库中java pool一般设为100-200M1.1 ORACLE例程
  • 14. 后台进程组成: DBWR :负责把dirty buffer数据写入数据文件 LGWR :负责把redo log buffer内数据写入redo log file SMON :负责例程恢复(前滚,回退未提交事务处理,开库)、合并空闲空间,回收临时段   PMON :负责回退事务处理,释放锁,释放资源、重启调度程序 CKPT :每隔三秒,CKPT 进程就会向控制文件存储数据,以标识重做日志文件中恢复操作的起始位置,该操作称作检查点。1.1 ORACLE例程
  • 15. DBWR: 该进程负责将缓冲区脏数据写入数据文件,是缓冲存储区管理的一个ORACLE后台进程。当缓冲区中的一个空闲缓冲区被修改,它被标志为“弄脏”,DBWR的主要任务是将“弄脏”的缓冲区写入磁盘,使缓冲区保持“干净”。由于缓冲存储区的缓冲区填入数据库或被用户进程弄脏,未用的缓冲区的数目减少。当未用的缓冲区下降到很少,以致用户进程要从磁盘读入块到内存存储区时无法找到未用的缓冲区时,DBWR将管理缓冲存储区,使用户进程总可得到未用的缓冲区。 ORACLE采用LRU(LEAST RECENTLY USED)算法(最近最少使用算法)来释放近期未访问缓冲区,使I/O最小。在下列情况预示DBWR 要将弄脏的缓冲区写入磁盘: 1、正常检查点 2、dirty buffer达到阀值,该临界长度为参数DB-BLOCK-WRITE-BATCH的值的一半 3、出现超时(每次3秒) 4、删除或截断表 1.1 ORACLE例程
  • 16. LGWR: 该进程将日志缓冲区写入磁盘上的一个日志文件,它是负责管理日志缓冲区的一个ORACLE后台进程。LGWR进程将自上次写入磁盘以来的全部日志项输出,LGWR进程同步地写入到活动的镜象在线日志文件组。如果组中一个文件被删除或不可用,LGWR 可继续地写入该组的其它文件。在以下几种情况下LGWR输出日志缓冲区: 1、每三秒将日志缓冲区输出 2、当用户发出COMMIT语句时将日志缓冲区输出 3、当日志缓冲区的1/3已满时将日志缓冲区输出 4、当日志缓冲区的变化超过1MB时将日志缓冲区输出 CKPT: 该进程每隔3秒,对全部数据文件的标题进行修改,指示该检查点。负责发信号给DBWR,更新datafile文件头,更新控制文件 1.1 ORACLE例程
  • 17. SMON: 该进程实例启动时执行实例恢复,SMON对有故障的实例进行实例恢复:前滚以恢复尚未记入数据文件但已经记入联机重做日志中的数据,打开数据库以便用户可以登录,最后回滚尚未提交的事务。此外,SMON也执行诸如合并空闲空间,收回不再用的临时段空间等空间维护功能。 PMON: 该进程在用户进程出现故障时执行进程恢复,负责清理内存和释放该进程所使用的资源。例如:它要重置活动事务表的状态,回退用户的当前事务处理,释放锁,将该故障的进程的ID从活动进程表中移去。PMON还周期地检查调度进程(DISPATCHER)和服务器进程的状态,如果已死,则重新启动(不包括有意删除的进程)。1.1 ORACLE例程
  • 18. 后台进程总结: DBWR :负责把dirty buffer数据写入数据文件 LGWR :负责把redo log buffer内数据写入redo log file SMON :负责例程恢复(前滚,回退未提交事务处理,开库)、合并空闲空间,回收临时段   PMON :负责回退事务处理,释放锁,释放资源、重启调度程序 CKPT :每隔三秒,CKPT 进程就会向控制文件存储数据,以标识重做日志文件中恢复操作的起始位置,该操作称作检查点。1.1 ORACLE例程
  • 19. 第一章 ORACLE结构1.1 ORACLE例程 1.2 ORACLE数据库
  • 20. 1.2 ORACLE数据库
  • 21. Oracle数据库组成: 数据文件( Data files )物理存储ORACLE数据库数据的文件,作为操作系统文件而存在。数据以ORACLE二进制方式存储在数据文件中。 控制文件(Control Files):一个较小的二进制文件,不可读或手工编辑,用于描述数据库结构。它是数据库的心脏。 重做日志文件( Redo log files ) :用于记录数据库的所有修改信息,在数据库恢复时被ORACLE使用。 其他关键文件结构: 参数文件 :用来定义Oracle 例程的特性 口令文件 :用来验证哪些用户有权启动和关闭Oracle 例程 归档日志文件:是重做日志文件的脱机副本,当必须从介质失败中进行恢复时可能会需要这些副本。1.2 ORACLE数据库
  • 22. 1.2 ORACLE数据库
  • 23. 重做日志文件结构 数据库管理员可设置Oracle 数据库以维护联机重做日志文件副本,来避免由于单点故障丢失数据库信息。 联机重做日志文件组: 一组相同的联机重做日志文件副本称作联机重做日志组。 LGWR 后台进程向组内所有联机重做日志文件并发写入相同信息。 为保证数据库的正常操作,Oracle 服务器最少需要两个联机重做日志文件组。 联机重做日志文件成员: 组内的每个联机重做日志文件称为成员。 组内的每个成员都有相同的日志序列号和同样的大小。Oracle 服务器每次写入日志组时,都分配一个日志序列号以唯一地识别每个重做日志文件。当前日志序列号存储在控制文件和所有数据文件的标头内。1.2 ORACLE数据库
  • 24. 重做日志文件的使用 Oracle 服务器将对数据库所做的所有更改按顺序记录到重做日志缓冲区中。LGWR 进程把重做条目从重做日志缓冲区写入联机重做日志组的其中一个组,这个组叫做当前联机重做日志组。重做日志文件是以循环方式使用的。一旦某个重做日志文件被写满,LGWR 就会移动到下一个日志组。这称为日志切换,同时还将执行检查点操作,将信息写入控制文件。 如果数据库配置为在NOARCHIVELOG 模式下,每次联机重做日志文件已满并发生日志切换时,都要覆盖联机重做日志文件。如果数据库配置为在ARCHIVELOG 模式运行下,那么必须将已满的联机重做日志文件的不活动组归档。 对重作日志文件的操作分为以下几种: 添加联机重作日志组 删除联机重作日志组 添加联机重作日志成员 删除联机重作日志成员 强制执行日志切换1.2 ORACLE数据库
  • 25. Oracle数据库总结: 数据文件( Data files )物理存储ORACLE数据库数据的文件,作为操作系统文件而存在。数据以ORACLE二进制方式存储在数据文件中。 控制文件(Control Files):一个较小的二进制文件,不可读或手工编辑,用于描述数据库结构。它是数据库的心脏。 重做日志文件( Redo log files ) :用于记录数据库的所有修改信息,在数据库恢复时被ORACLE使用。重作日志的主要操作有添加、删除联机重作日志组或成员以及强制日志切换 参数文件 :用来定义Oracle 例程的特性 口令文件 :用来验证哪些用户有权启动和关闭Oracle 例程 归档日志文件:是重做日志文件的脱机副本,当必须从介质失败中进行恢复时可能会需要这些副本。1.2 ORACLE数据库
  • 26. 1.2 ORACLE数据库
  • 27. 目 录第一章  ORACLE结构 第二章  SQL语句 第三章 索引和约束 第四章 视图和快照 第五章  备份恢复
  • 28. 第二章 SQL语句2.1 DML语句 2.2 DDL语句
  • 29. DML语句在下列情况下发生: 往一个表中增加新行 更改一个表中现有的行 从一个表中删除掉现有的行 一个事务由一组构成一个逻辑操作的DML语句组成。2.1 DML语句
  • 30. 向一个表中增加新行DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON新行 50 DEVELOPMENT DETROITDEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON“…向 DEPT 表中 增加新行…” 50 DEVELOPMENT DETROIT2.1 DML语句
  • 31. INSERT语句使用 INSERT 语句向表中增加新行。 使用这种方法只能一次插入一行数据。INSERT INTO table [(column [, column...])] VALUES (value [, value...]);2.1 DML语句
  • 32. 插入新行插入包含每一个列值的新行。 按缺省顺序列出表中所有的列值。 列出 INSERT 子句中所有的列。 日期值和字符值要用单引号括起来。SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'DEVELOPMENT', 'DETROIT'); 1 row created.2.1 DML语句
  • 33. 插入带有空值的行省略的方法: 从列的链表忽略有空值的列。SQL> INSERT INTO dept (deptno, dname ) 2 VALUES (60, 'MIS'); 1 row created.明确的方法: 指定 NULL 关键字。SQL> INSERT INTO dept 2 VALUES (70, 'FINANCE', NULL); 1 row created.2.1 DML语句
  • 34. 插入特殊的值SYSDATE函数取出当前的日期的时间。SQL> INSERT INTO emp (empno, ename, job, 2 mgr, hiredate, sal, comm, 3 deptno) 4 VALUES (7196, 'GREEN', 'SALESMAN', 5 7782, SYSDATE, 2000, NULL, 6 10); 1 row created.2.1 DML语句
  • 35. 插入特殊的日期值增加一个新的员工。SQL> INSERT INTO emp 2 VALUES (2296,'AROMANO','SALESMAN',7782, 3 TO_DATE('FEB 3, 1997', 'MON DD, YYYY'), 4 1300, NULL, 10); 1 row created.检验结果。EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- -------- ---- --------- ---- ---- ------ 2296 AROMANO SALESMAN 7782 03-FEB-97 1300 102.1 DML语句
  • 36. 从另一个表中拷贝行创建带有子查询的 INSERT语句。 不要使用 VALUES 子句。 子查询中的列要与INSERT子句中的列相匹配。SQL> INSERT INTO managers(id, name, salary, hiredate) 2 SELECT empno, ename, sal, hiredate 3 FROM emp 4 WHERE job = 'MANAGER'; 3 rows created.2.1 DML语句
  • 37. 改变一个表中的数据EMP“…更改EMP 表中的一行数据…”EMP EMPNO ENAME JOB ... DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...20 EMPNO ENAME JOB ... DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...2.1 DML语句
  • 38. UPDATE 语句使用 UPDATE 语句更改现有的行。 如果需要,可以一次更改多行。UPDATE table SET column = value [, column = value, ...] [WHERE condition];2.1 DML语句
  • 39. 更改表中的行使用 WHERE 子句来指定要修改的行。SQL> UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782; 1 row updated.SQL> UPDATE employee 2 SET deptno = 20; 14 rows updated.如果忽略WHERE子句,那么句子中所有的值都将被更改。2.1 DML语句
  • 40. 用多列子查询进行修改SQL> UPDATE emp 2 SET (job, deptno) = 3 (SELECT job, deptno 4 FROM emp 5 WHERE empno = 7499) 6 WHERE empno = 7698; 1 row updated.更改第 7698号雇员的工作和部门,以与第 7499号雇员进行匹配。2.1 DML语句
  • 41. 对基于另一个表的行进行更改在 UPDATE语句中,使用子查询来更进行更改,更改的数据与其它表中的数据有关。SQL> UPDATE employee 2 SET deptno = (SELECT deptno 3 FROM emp 4 WHERE empno = 7788) 5 WHERE job = (SELECT job 6 FROM emp 7 WHERE empno = 7788); 2 rows updated.2.1 DML语句
  • 42. “…从一个表中删去一行…”从一个表中移去一行 DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DEVELOPMENT DETROIT 60 MIS ...DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 60 MIS ...2.1 DML语句
  • 43. DELETE语句可以使用 DELETE 语句从表中删去现存的行。DELETE [FROM] table [WHERE condition];2.1 DML语句
  • 44. 使用 WHERE 子句以指定哪些行应当被删去。从一个表中删去行SQL> DELETE FROM department 2 WHERE dname = 'DEVELOPMENT'; 1 row deleted.SQL> DELETE FROM department; 4 rows deleted.如果忽略WHERE 子句,那么表中所有的数据。2.1 DML语句
  • 45. 参照另一个表来删除行使用子查询,使得 DELETE 语句能从另一个表中删除某些行。SQL> DELETE FROM employee 2 WHERE deptno = 3 (SELECT deptno 4 FROM dept 5 WHERE dname ='SALES'); 6 rows deleted.2.1 DML语句
  • 46. 数据库事务要么全部完成,要么全部废弃的操作集合。 一个事务可以包含下列语句: 对数据做出一致性修改的DML语句 一个 DDL 语句 一个 DCL语句2.1 DML语句
  • 47. 数据库事务以第一个可执行的 SQL 语句开始。 以下列情况结束: 执行COMMIT 或者 ROLLBACK 语句 执行DDL或者 DCL语句 用户退出 系统崩溃2.1 DML语句
  • 48. COMMIT和ROLLBACK语句的优点保证数据的一致性 在数据永久改变之前,检查数据的改变 对逻辑相关的操作进行分组2.1 DML语句
  • 49. DELETE控制事务事务保存点A回滚到保存点 BDELETE保存点B提交INSERTUPDATE回滚到保存点 AINSERTUPDATEINSERT回滚INSERT2.1 DML语句
  • 50. 在下列环境下,一个自动提交发生: 处理DDL语句 处理DCL 语句 从 SQL*Plus中退出, 而没有明确指定COMMIT或者 ROLLBACK 当 SQL*Plus中断或者系统失败时自动进行回滚。隐式事务处理2.1 DML语句
  • 51. 在COMMIT 或ROLLBACK之前的数据状态之前的状态可以被恢复。 当前的用户可以用SELECT语句来查看DML操作后的结果。 其它用户看不到当前用户使用 DML语句进行数据操纵的结果。 产生改变的数据被加锁,其它用户不能改变这些行。2.1 DML语句
  • 52. 在 COMMIT之后的数据状态数据的改变将被永久的反应到数据库中去。 事务前面的数据状态将被永久地丢弃。 所有的用户可以查看结果。 加在产生改变的行上的数据被解锁; 这些行对于其他用户是可用的。 所有的保存点被释放。2.1 DML语句
  • 53. 提交数据SQL> UPDATE emp 2 SET deptno = 10 3 WHERE empno = 7782; 1 row updated.产生改变。Commit the changes。SQL> COMMIT; Commit complete.2.1 DML语句
  • 54. 回滚后的数据状态使用 ROLLBACK语句丢弃所有的数据改变。 数据的改变失效 事务之前的数据状态改变 在改变行的上的锁被释放SQL> DELETE FROM employee; 14 rows deleted. SQL> ROLLBACK; Rollback complete.2.1 DML语句
  • 55. 回滚到某个标记使用 SAVEPOINT语句在当前事务中产生一个标记。 使用ROLLBACK TO SAVEPOINT语句回滚到那个标记。SQL> UPDATE... SQL> SAVEPOINT update_done; Savepoint created. SQL> INSERT... SQL> ROLLBACK TO update_done; Rollback complete.2.1 DML语句
  • 56. 锁定在并发事务之间,阻止可能产生的破坏性相互影响。 不需要用户进行干预,自动使用。 在事务期间使用,事务结束时释放。 有两种基本的模式: 排它和共享。2.1 DML语句
  • 57. 总结描述 向表中增加一个新的行 更改表中现存的行 从表中删除现存的行 提交一个事务 允许回滚到保存点标记 废弃所有未提交的改变语句 INSERT UPDATE DELETE COMMIT SAVEPOINT ROLLBACK2.1 DML语句
  • 58. 第二章 SQL语句2.1 DML语句 2.2 DDL语句
  • 59. 数据库对象对象 描述 表 存储的基本单元,由行和列组成 视图 一个逻辑的数据集,数据来自一个或者多个表 序列 产生主键值 索引 目的提高查询的性能 同义词 给出对象的别名2.2 DDL语句
  • 60. 命名习惯必须以一个字母开始 可以是1-10个字符的长度 仅能包括字符: A–Z, a–z, 0–9, _, $, 和# 不能与本用户拥有的其他对象重名 不能是一个 Oracle服务器的保留字2.2 DDL语句
  • 61. CREATE TABLE语句必须有 : CREATE TABLE 的权限 拥有存储空间 可以指定: 表名 列名、列类型、列长度CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);2.2 DDL语句
  • 62. 缺省选项为某个列指定一个缺省值。… hiredate DATE DEFAULT SYSDATE, … 合法的值可以是一个直接的量、表达式、或者一个SQL函数。 缺省值的数据类型必须与列的数据类型匹配。2.2 DDL语句
  • 63. 创建表SQL> CREATE TABLE dept 2 (deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13)); Table created.创建表。确认创建的结果。SQL> DESCRIBE dept Name Null? Type --------------------------- -------- --------- DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)2.2 DDL语句
  • 64. Oracle数据库中的表用户表 由用户创建和维护的一系列表的集合 包含用户的信息 数据字典 由Oracle 服务器创建和维护的一系列表的集合 包含数据库的信息2.2 DDL语句
  • 65. 查询数据字典查看用户所拥有的数据类型。查看用户拥有的表、视图、同义词、序列。SQL> SELECT * 2 FROM user_tables;SQL> SELECT DISTINCT object_type 2 FROM user_objects;SQL> SELECT * 2 FROM user_catalog;描述用户拥有的表。2.2 DDL语句
  • 66. 数据类型数据类型 描述 VARCHAR2(size) 变长字符数据,size为最大长度 CHAR(size) 定长字符数据, size为长度 NUMBER(p,s) 数字类型,p为 精度,s为刻度 DATE 日期和时间值 LONG 可以保存最大长度为 2GB的字符数据 CLOB 可以保存最大长度为 4GB的单字节字符 数据 RAW and LONG RAW 存储二进制数据 BLOB 最大为 4GB的二进制数据类型 BFILE 在外部文件中存储的二进制数据;最大为 4GB2.2 DDL语句
  • 67. 使用子查询来创建一个表使用 CREATE TABLE语句和AS子查询选项来创建一个表并插入数据。 创建的表的列要和子查询的列相匹配。CREATE TABLE table [(column, column...)] AS subquery;2.2 DDL语句
  • 68. SQL> CREATE TABLE dept30 2 AS 3 SELECT empno, ename, sal*12 ANNSAL, hiredate 4 FROM emp 5 WHERE deptno = 30; Table created.使用子查询来创建一个表 Name Null? Type ---------------------------- -------- ----- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) ANNSAL NUMBER HIREDATE DATESQL> DESCRIBE dept302.2 DDL语句
  • 69. ALTER TABLE命令增加一个新列 更改一个现存的列 为新列定义缺省的值ALTER TABLE table ADD (column datatype [DEFAULT expr] [, column datatype]...);ALTER TABLE table MODIFY (column datatype [DEFAULT expr] [, column datatype]...);2.2 DDL语句
  • 70. 增加一列DEPT30 EMPNO ENAME ANNSAL HIREDATE ------ ---------- -------- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ...“…为表DEPT30 增加一个新列…”DEPT30 EMPNO ENAME ANNSAL HIREDATE ------ ---------- -------- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ...JOBJOB新列2.2 DDL语句
  • 71. 增加一列使用ADD子句来增加一个列。 EMPNO ENAME ANNSAL HIREDATE JOB --------- ---------- --------- --------- ---- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ... 6 rows selected.SQL> ALTER TABLE dept30 2 ADD (job VARCHAR2(9)); Table altered.新增加的列为成最后一个列。2.2 DDL语句
  • 72. 更改一个现存的列可以更改一个现存列的数据类型、长度、以及缺省值。 对于缺省值的修改只影响到以后会修改的列。SQL> ALTER TABLE dept30 2 MODIFY (ename VARCHAR2(15)); Table altered.2.2 DDL语句
  • 73. 丢弃一个列使用 DROP COLUMN来丢弃一个表中将来不使用的列。SQL> ALTER TABLE dept30 2 DROP COLUMN job ; Table altered.2.2 DDL语句
  • 74. 丢弃一个表表中所有的数据和结构都被删除了。 所有未决的事务都被提交。 此表上所有的索引全部被删除。 这个操作是不能回滚的。SQL> DROP TABLE dept30; Table dropped.2.2 DDL语句
  • 75. 改变一个对象的名称更改表、视图、同义词、序列等对象的名称,执行RENAME 命令就可以了。 操作用户必须是对象的拥有者。SQL> RENAME dept TO department; Table renamed.2.2 DDL语句
  • 76. 截断一个表TRUNCATE TABLE 语句功能: 移除表中所有的数据行 释放表中的存储空间 在执行 TRUNCATE语句后,不能对该操作回滚。 可以用DELETE语句来做相同的操作,不过DELETE并不立即释放空间,因而可以回滚。SQL> TRUNCATE TABLE department; Table truncated.2.2 DDL语句
  • 77. 为一个表增加注释可以使用 COMMENT语句为一个表,或者表中的某一列增加注释。 注释可以通过数据字典视图进行查询。 ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTSSQL> COMMENT ON TABLE emp 2 IS 'Employee Information'; Comment created.2.2 DDL语句
  • 78. 总结语句 描述 CREATE TABLE 创建一个表 ALTER TABLE 更改表结构 DROP TABLE 废弃一个表,包括它的结构和数据行 RENAME 更改对象的名称 TRUNCATE 从表中移除所有的行,并将这些空间释放掉 COMMENT 为一个表或者视图增加注释2.2 DDL语句
  • 79. 目 录第一章  ORACLE结构 第二章  SQL语句 第三章 索引和约束 第四章 视图和快照 第五章  备份恢复
  • 80. 第三章 索引和约束3.1 索引 3.2 约束
  • 81. 索引是什么?一个数据库模式对象 Oracle利用索引来加快对数据行的访问 依靠索引来快速定位数据,从而减少了磁盘I/O的次数 与使用它的表是相互独立的数据库对象 Oracle 服务器自动对索引进行维护和使用3.1 索引
  • 82. 索引是如何创建的?自动创建:当在创建表时,如果指定了 PRIMARY KEY或者 UNIQUE约束,那么将自动创建索引。 手动创建:用户可以在某个列上建立非唯一的索引,以加快基于该行的查询。3.1 索引
  • 83. 创建一个索引如:创建索引EMP_ENAME_IDX,以提高对表EMP的ENAME列的访问速度。SQL> CREATE INDEX emp_ename_idx 2 ON emp(ename); Index created.CREATE INDEX index ON table (column[, column]...);在一个列上或者多个列上创建索引。3.1 索引
  • 84. 什么时候创建索引欲创建索引的列在 WHERE子句或者连接条件中频繁使用。 该列所包含的不同值很多。 该列包含大量的空值。 表中的数据行数非常大,而且只有 2–4% 数据行被查询出来。3.1 索引
  • 85. 什么时候不必创建索引 表是空的。 列在查询条件中不经常使用。 大多数基于该表的查询,所查询出的数据量远多于2–4% 行。 表被频繁修。3.1 索引
  • 86. 确认索引创建结果USER_INDEXES 数据字典视图包含用户创建的索引的名字和它唯一性。 USER_IND_COLUMNS 视图包含索引的名字、表名、列名。SQL> SELECT ic.index_name, ic.column_name, 2 ic.column_position col_pos,ix.uniqueness 3 FROM user_indexes ix, user_ind_columns ic 4 WHERE ic.index_name = ix.index_name 5 AND ic.table_name = 'EMP';3.1 索引
  • 87. 基于函数的索引基于函数的索引也就是基于表达式的索引。 索引表达式由表的列、常量、 SQL函数或者用户自定义函数组成。 SQL> CREATE TABLE test (col1 NUMBER); SQL> CREATE INDEX test_index on test(col1,col1+10); SQL> SELECT col1+10 FROM test; 3.1 索引
  • 88. 删除索引从数据字典中删除一个索引。 从数据字典中删除 EMP_ENAME_IDX 索引。 要删除一个索引,必须是索引的拥有者,或者具有 DROP ANY INDEX的权限。SQL> DROP INDEX emp_ename_idx; Index dropped.SQL> DROP INDEX index;3.1 索引
  • 89. 第三章 索引和约束3.1 索引 3.2 约束
  • 90. 什么是约束?约束定义了表级的强制规则。 约束定义了数据的完整性。 在Oracle中,有下列的强制类型: NOT NULL 非空约束 UNIQUE 唯一约束 PRIMARY KEY 主键约束 FOREIGN KEY 外键约束 CHECK 定义一般性约束3.2 约束
  • 91. 可以为一个约束命名,Oracle服务器也可以自动为约束命名。 约束的创建: 当创建表时创建约束 在表创建后仍然可以创建约束 定义一个列级或者表级的约束。 查看数据字典中的约束。3.2 约束
  • 92. 定义一个约束CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint][,...]);CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10), ... deptno NUMBER(2) NOT NULL, CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO)); 3.2 约束
  • 93. 定义一个约束列级约束 表级约束column [CONSTRAINT constraint_name] constraint_type,column,... [CONSTRAINT constraint_name] constraint_type (column, ...),3.2 约束
  • 94. NOT NULL约束保证表中的某些列不出现空值EMP EMPNO ENAME JOB ... COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...NOT NULL 约束 (该列不能出现一个 空值)没有NOT NULL约束 (该列中任何一行都可以出现空行)NOT NULL 约束3.2 约束
  • 95. NOT NULL 约束定义列级约束SQL> CREATE TABLE emp( 2 empno NUMBER(4), 3 ename VARCHAR2(10) NOT NULL, 4 job VARCHAR2(9), 5 mgr NUMBER(4), 6 hiredate DATE, 7 sal NUMBER(7,2), 8 comm NUMBER(7,2), 9 deptno NUMBER(7,2) NOT NULL);3.2 约束
  • 96. 唯一键值约束DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON唯一键值约束 50 SALES DETROIT 60 BOSTON插入不允许 (DNAME-SALES 已经存在)允许3.2 约束
  • 97. 唯一键值约束在表级或者列级进行定义 SQL> CREATE TABLE dept( 2 deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13), 5 CONSTRAINT dept_dname_uk UNIQUE(dname));3.2 约束
  • 98. 主键约束DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON主键插入 20 MARKETING DALLAS FINANCE NEW YORK不允许 (DEPTNO 20 已经存在)不允许 (DEPTNO 是空的)3.2 约束
  • 99. 主键约束在表级或者列级进行定义SQL> CREATE TABLE dept( 2 deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13), 5 CONSTRAINT dept_dname_uk UNIQUE (dname), 6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));3.2 约束
  • 100. 外键约束DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS ...主键EMP EMPNO ENAME JOB ... COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 ...外键 7571 FORD MANAGER ... 200 9 7571 FORD MANAGER ... 200 20插入不允许 (DEPTNO 9 在 DEPT 表中不存在)Allowed 3.2 约束
  • 101. 外键约束要么在表级要么在列级做出定义SQL> CREATE TABLE emp( 2 empno NUMBER(4), 3 ename VARCHAR2(10) NOT NULL, 4 job VARCHAR2(9), 5 mgr NUMBER(4), 6 hiredate DATE, 7 sal NUMBER(7,2), 8 comm NUMBER(7,2), 9 deptno NUMBER(7,2) NOT NULL, 10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) 11 REFERENCES dept (deptno));3.2 约束
  • 102. 外键约束关键字FOREIGN KEY: 定义子表中的列为外键。 REFERENCES: 标记外键所要参考的父表和列 ON DELETE CASCADE: 允许在删除父表的列的同时,删除子表的列。3.2 约束
  • 103. CHECK 约束定义每一列所要满足的条件 在CHECK约束中不允许的表达式: 参考 CURRVAL, NEXTVAL, LEVEL, 和 ROWNUM这样的伪列 调用 SYSDATE, UID, USER,和 USERENV函数 参考其它列值的查询..., deptno NUMBER(2), CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99),...3.2 约束
  • 104. 增加一个约束可以增加、删除一个约束。但不能更改一个约束。 指定一个约束是有效还是无效。 可以用MODIFY 子句为一个表的列增加NOT NULL约束。 ALTER TABLE table ADD [CONSTRAINT constraint] type (column);3.2 约束
  • 105. 增加一个约束为 EMP表增加一个外键约束 指定一个管理员必须已经作为一个职员,存在于 EMP 表中SQL> ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr) REFERENCES emp(empno); Table altered.3.2 约束
  • 106. 丢弃一个约束从 EMP 表中移除约束。SQL> ALTER TABLE emp 2 DROP CONSTRAINT emp_mgr_fk; Table altered.移除 DEPT表上的主键约束 ,因为有CASCADE选项,所以同时删除了在EMP.DEPTNO 列上的外键约束。SQL> ALTER TABLE dept 2 DROP PRIMARY KEY CASCADE; Table altered.3.2 约束
  • 107. 使约束失效执行 ALTER TABLE 语句中的DISABLEz子句可以使约束失效。 使用 CASCADE 选项可以同时使有依赖关系选项失效。SQL> ALTER TABLE emp 2 DISABLE CONSTRAINT emp_empno_pk CASCADE; Table altered.3.2 约束
  • 108. 使约束生效可以使用 ENABLE 子句来激活一个失效的约束。 一个唯一索引,或者主键索引在激活唯一键或主键时自动产生。SQL> ALTER TABLE emp 2 ENABLE CONSTRAINT emp_empno_pk; Table altered.3.2 约束
  • 109. 级联约束CASCADE CONSTRAINTS 子句是随同DROP COLUMN子句一起使用的。 CASCADE CONSTRAINTS 将丢弃在删除列上的唯一键或主键约束。3.2 约束
  • 110. 总结在Oracle中,可以创建下列类型的约束: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK3.2 约束
  • 111. 目 录第一章  ORACLE结构 第二章  SQL语句 第三章 索引和约束 第四章 视图和快照 第五章  备份恢复
  • 112. 第四章 快照和视图4.1 视图 4.2 快照
  • 113. 数据库对象描述 数据存储的基本单元;由各个数据行和列组成 代表从一个或者多个表中产生的一个逻辑数据集 用途为产生主键的值 提高一些查询的性能 一个对象的别名对象 Table View Sequence Index Synonym4.1 视图
  • 114. 什么是视图?EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- --------- ----- --------- ----- ----- ------- 7839 KING PRESIDENT 17-NOV-81 5000 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10EMP Table EMPNO ENAME JOB ------ -------- ----------- 7839 KING PRESIDENT 7782 CLARK MANAGER 7934 MILLER CLERK EMPVU10 View4.1 视图
  • 115. 为什么使用视图?限制数据的存取 使得复杂的查询变得容易 使得数据相对比较独立 代表对同一数据的不同视角4.1 视图
  • 116. 简单视图和复杂视图特点 简单视图 复杂视图 表的数量 一个 一个或者更多 约束函数 没有 有 是否包含分组 不 是 可以对视图进行更新 是 不一定4.1 视图
  • 117. 创建一个视图在 CREATE VIEW 语句中包含一个子查询。CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY];4.1 视图子查询中可以包含复杂的 SELECT 语法。
  • 118. 创建视图创建一个视图,EMPVU10,包含部门号为10的雇员信息。使用 SQL*Plus的 DESCRIBE命令来描述视图的结构。SQL> DESCRIBE empvu10SQL> CREATE VIEW empvu10 2 AS SELECT empno, ename, job 3 FROM emp 4 WHERE deptno = 10; View created.4.1 视图
  • 119. 创建视图创建视图时,可以在列的子查询中使用别名。 可以根据给定的列别名,从视图中获取列的信息。SQL> CREATE VIEW salvu30 2 AS SELECT empno EMPLOYEE_NUMBER, ename NAME, 3 sal SALARY 4 FROM emp 5 WHERE deptno = 30; View created.4.1 视图
  • 120. 从视图中获取列信息EMPLOYEE_NUMBER NAME SALARY --------------- ---------- --------- 7698 BLAKE 2850 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950 7521 WARD 1250 6 rows selected.SQL> SELECT * 2 FROM salvu30;4.1 视图
  • 121. 查询一个视图 USER_VIEWS EMPVU10 SELECT empno, ename, job FROM emp WHERE deptno = 10; SQL*Plus SELECT * FROM empvu10;EMP 7839 KING PRESIDENT 7782 CLARK MANAGER 7934 MILLER CLERK4.1 视图
  • 122. 视图的数据更新使用 CREATE 或者 REPLACE VIEW 子句来更新EMPVU10视图,为每一个列名增加一个别名。 在 CREATE VIEW子句中的列别名应当与子查询中的列别名相同。SQL> CREATE OR REPLACE VIEW empvu10 2 (employee_number, employee_name, job_title) 3 AS SELECT empno, ename, job 4 FROM emp 5 WHERE deptno = 10; View created.4.1 视图
  • 123. 创建复杂的视图创建一个复杂的视图,视图的数据来自于两个表,而且视图定义中含有几个函数:SQL> CREATE VIEW dept_sum_vu 2 (name, minsal, maxsal, avgsal) 3 AS SELECT d.dname, MIN(e.sal), MAX(e.sal), 4 AVG(e.sal) 5 FROM emp e, dept d 6 WHERE e.deptno = d.deptno 7 GROUP BY d.dname; View created.4.1 视图
  • 124. 对视图进行DML操作的规则可以对简单视图进行DML操作。 如果含有如下情况,则不能删除视图中的数据: 含有Group函数 含有GROUP BY 子句 含有 DISTINCT 关键字 含有ROWNUM 这个伪列4.1 视图
  • 125. 在一个视图上执行DML 操作的规则如果一个视图包含下列情况,则不能修改该视图中的数据: 上一个幻灯片提到的任何一种情况。 列是由表达式来进行定义的。 含有ROWNUM 伪列。 如果有下列情况,则不能增加数据: 视图中包含上述和上一个幻灯片的任何一种情况。 在基表中包含有 NOT NULL约束的列,然而该列并没有在视图中出现。4.1 视图
  • 126. 禁止进行DML操作在视图定义中,可以使用WITH READ ONLY选项来保证该视图上不能进行DML操作。SQL> CREATE OR REPLACE VIEW empvu10 2 (employee_number, employee_name, job_title) 3 AS SELECT empno, ename, job 4 FROM emp 5 WHERE deptno = 10 6 WITH READ ONLY; View created.该视图任何数据行上的DML语句操作都将被Oracle服务器禁止。4.1 视图
  • 127. 删除视图删除一个视图不会丢失数据,因为视图的数据本来就是来自于数据库中的表。SQL> DROP VIEW empvu10; View dropped.DROP VIEW view; 4.1 视图
  • 128. 内联(inline)视图内联视图是在 嵌在某个SQL 语句中,具有别名的一个子查询。 一个内联视图类似于在FROM子句中使用的一个具有名字的子查询。 一个内联视图不是数据库中的一个模式对象。4.1 视图
  • 129. 内联视图举例SELECT a.ename, a.sal, a.deptno, b.maxsal FROM emp a, (SELECT deptno, max(sal) maxsal FROM emp GROUP BY deptno) b WHERE a.deptno = b.deptno AND a.sal < b.maxsal; 4.1 视图
  • 130. 总结一个视图是从数据库表中或者其他视图中获取的数据的集合。 视图提供了下列的优点: 限制数据库的存取 简化查询 提供数据独立性 产生同一数据上的多个视角 可以在不删除数据的情况下删除视图4.1 视图
  • 131. 第四章 快照和视图4.1 视图 4.2 快照
  • 132. 什么是快照?4.2 快照 快照的内容是一个或者多个表或者视图的查询结果。通常,被查询的表和视图位于远程数据库中,称为快照的主表,主表所在的数据库称为主数据库。快照主要用在分布式数据库中,是主表数据在本地数据库的只读拷贝。
  • 133. 为什么使用快照?数据的复制 提高数据的查询速度 使得复杂的查询变得简单 4.2 快照
  • 134. 快照刷新分类完全刷新 EXECUTE DBMS_SNAPSHOT.REFRESH('HT_YBNSR_DAB','C'); 快速刷新 EXECUTE DBMS_SNAPSHOT.REFRESH('HT_YBNSR_DAB','F');4.2 快照
  • 135. 实际应用 在省集中后,部分地市依旧有对部分数据进行实时更新的需求,譬如某些省份的网上认证开户系统依旧使用各地数据库,这就需要将ht_ybnsr_dab表中的数据与省局数据库中相应表保持一致。我们就可以运用刚刚所学的快照、物化视图技术实现这一功能,现以江苏省南京市为例说明物化视图建立、使用流程:(因为目前防伪税控省集中数据库都是rac集群配置,但大容量表的刷新物化视图操作会比较消耗资源,故而建议将数据链路建立在实例2上,也可根据实际情况自行调整。)4.2 快照
  • 136. 第一步 建立数据链路: 先在南京市数据库服务器上建立本地服务名,具体方法是在tnsnames.ora中添加如下内容: FWSK_MAST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 77.88.16.3)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = fwsk2) ) ) 以htjs登陆南京市数据库,执行如下语句,其中password处要填入江苏省数据库htjs的密码: CREATE DATABASE LINK "FWSK_TEST" CONNECT TO "HTJS" IDENTIFIED BY “password" USING 'FWSK_MAST'; 4.2 快照
  • 137. 第二步 建立快照: 以htjs登陆江苏省数据库,执行如下语句,在省局数据库上建立快照日志: create snapshot log on HTJS.HT_YBNSR_DAB tablespace HTJS with rowid; 以htjs登陆南京市数据库,执行如下语句,建立快照,因为目前很多地市数据库依然为oracle8故而此处采用快照语句,若数据库版本为9i可以按之前课程所述语法建立物化视图,红字部分标明了快照(物化视图)包含数据的范围,别的地市需按实际情况进行修改: create snapshot HTJS.HT_YBNSR_DAB tablespace HTJS using index tablespace INDX REFRESH FORCE WITH ROWID AS select * from HTJS.HT_YBNSR_DAB@FWSK_LNK where substr(swjg_dm,1,5)='13201'; 建立快照完成后,可执行如下语句进行初始完全刷新,若一切正常,在执行select * from htjs.ht_ybnsr_dab后会看到与省局数据库中相同的数据: EXECUTE DBMS_SNAPSHOT.REFRESH('HT_YBNSR_DAB','C');4.2 快照
  • 138. 第三步 定期刷新快照: 快照建立后还要保证数据的实时更新,以htjs登陆南京市数据库,执行如下语句,即可快速刷新省局数据库中的数据(前提必须是已经做过完全刷新): EXECUTE DBMS_SNAPSHOT.REFRESH('HT_YBNSR_DAB','F'); 以oracle用户登陆南京市数据库服务器,建立snap_refresh.sh脚本,并通过在crontab中添加定时任务的方式定时执行刷新操作,至此,即可实现省局市局间数据的同步功能: ORACLE_SID=fwsk export ORACLE_SID sqlplus /nolog <
  • 139. 目 录第一章  ORACLE结构 第二章  SQL语句 第三章 索引和约束 第四章 快照和视图 第五章  备份恢复
  • 140. 第五章 备份恢复5.1 备份恢复介绍 5.2 exp&imp 5.3 RMAN
  • 141.   数据库管理员(DBA) 的主要职责之一就是确保数据库可用。DBA 可以采取预防措施来预防,但期望永远不出现故障只能是一种幼稚的想法。数据库出现故障后,DBA 必须尽快使之恢复运行,尽量减少数据损失。为了保护数据免受各种可能发生的故障的影响,DBA 必须定期备份数据库。 如果没有最新的备份,一旦发生文件损失,DBA 就不可能在不损失数据的情况下使数据库恢复运行。备份对从各种类型的故障中进行恢复至关重要。无论如何强调验证备份都不为过。以为备份存在而没有实际检查它是否存在,则一旦备份无效,代价可能会极其惨重。5.1 备份恢复介绍
  • 142. 备份分类 按照备份方式分类,ORACLE数据库的备份分为物理备份和逻辑备份两种: 物理备份是将实际组成数据库的操作系统文件从一处拷贝到另一处的备份过程,通常是从磁盘到磁带。可以使用 Oracle 的恢复管理器(Recovery Manager,RMAN)或操作系统命令进行数据库的物理备份。 逻辑备份是利用SQL语言从数据库中抽取数据并存于二进制文件的过程。Oracle提供的逻辑备份工具是 EXP。数据库逻辑备份是物理备份的补充。5.1 备份恢复介绍
  • 143. 备份分类 物理备份可分为一致性备份和不一致性备份两种: 一致性备份:一致性备份是当数据库的所有可读写的数据库文件和控制文件具有相同的系统改变号(SCN),并且数据文件不包含当前 SCN 之外的任何改变。因此,只有在以下条件下的备份是一致性备份:数据库正常关闭(用shutdown normal 或 shutdown immediate 命令关闭)。 不一致性备份:不一致备份是当数据库的可读写的数据库文件和控制文件的系统改变号(SCN)在不一致条件下的备份。在 SCN 号不一致的条件下,数据库必须通过应用重做日志使 SCN 一致的情况下才能启动。因此,如果进行不一致备份,数据库必须设为归档状态才有意义。在以下两个条件下的备份是不一致性备份:数据库处于打开状态。 数据库处于关闭状态,但是用非正常手段关闭的。例如,数据库是通过 shutdown abort 或机器掉电等等方法关闭的。5.1 备份恢复介绍
  • 144. 恢复分类 恢复就是把数据库由存在故障的状态转变为无故障状态的过程。根据出现故障的原因,恢复分为两种类型: 实例恢复。这种恢复是Oracle实例出现失败后,Oracle自动进行的恢复。 介质恢复。这种恢复是当存放数据库的介质出现故障时所做的恢复。5.1 备份恢复介绍
  • 145. 恢复分类 根据数据库的恢复程度,将恢复方法分为两种类型: 完全恢复:将数据库恢复到数据库失败时数据库的状态。这种恢复是通过装载数据库备份和并应用全部的重做日志做到的。 不完全恢复:将数据库恢复到数据库失败前的某一时刻数据库的状态。这种恢复是通过装载数据库备份和并应用部分的重做日志做到的。进行不完全恢复后必须在启动数据库时用 resetlogs 选项重设联机重做日志。5.1 备份恢复介绍
  • 146. 第五章 备份恢复5.1 备份恢复介绍 5.2 exp&imp 5.3 RMAN
  • 147. exp介绍 普通导出: EXP FWSKBAK/BAK FILE=/FWSK/FWSK_BAK.DMP INCTYPE=COMPLETE LOG=/FWSK/FWSK_BAK.LOG COMPRESS=N DIRECT=Y 按用户导出: EXP  SONIC/SONIC     BUFFER=64000  FILE=C:\SONIC.DMP  OWNER=SONIC这样用户SONIC的所有对象被输出到文件中。 按表导出: EXP  SONIC/SONIC     BUFFER=64000  FILE=C:\SONIC.DMP  OWNER=SONIC TABLES=(SONIC)这样用户SONIC的表SONIC就被导出 5.2 EXP&IMP
  • 148. 管道导出: 因为32位操作系统文件有2G大小限制,故需拆分备份文件,管道导 出脚本如下: su – oracle ORACLE_SID=fwsk mknod /fwsk/bak/exp_pipe p #建立备份导出管道 split –b2000m < /fwsk/bak/exp_pipe & #以2G为单位分割文件,并定向到管道,以后台运行 exp htjs/htxx file=/fwsk/bak/exp_pipe #将备份文件输出到管道 备份成功后将以默认x开头aa、ab、ac……序列生成文件名,即xaa、xbb文件都是2G的数据备份文件5.2 EXP&IMP
  • 149. imp介绍 普通导入: IMP HTJS/HTXX FILE='/DB/ORACLE/0901FWSK.DMP' LOG='/FWSK/FWSK0902.LOG' FULL=Y FEEDBACK=100000 COMMIT=Y IGNORE=Y; 按用户导入: IMP  SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP  FROMUSER=SONIC TOUSER=SONIC,用户SONIC的所有对象被导入到文件中。必须指定FROMUSER、TOUSER参数,才能导入数据。 按表导入: IMP  SONIC/SONIC BUFFER=64000  FILE=C:\SONIC.DMP  OWNER=SONIC TABLES=(SONIC),用户SONIC的表SONIC被导入。 5.2 EXP&IMP
  • 150. 管道导入: 因为32位操作系统文件有2G大小限制,故需将之前拆分的备份文件 先组合再导入,管道导入脚本如下: su – oracle ORACLE_SID=fwsk mknod /fwsk/bak/imp_pipe p #建立恢复导入管道 cat /fwsk/bak/xaa /fwsk/bak/xab /fwsk/bak/xac > /fwsk/bak/imp_pipe & #以后台运行,将3个分割备份文件(xaa、xab、xac)合并后重定向到管道 imp htjs/htxx file=/fwsk/bak/imp_pipe full=y #通过管道将文件导入到数据库5.2 EXP&IMP
  • 151. 第五章 备份恢复5.1 备份恢复介绍 5.2 exp&imp 5.3 RMAN
  • 152. 恢复管理器(Recovery Manager, RMAN) 是一个Oracle 实用程序,用于管理Oracle 数据库上的备份、还原和恢复操作。RMAN 具有功能强大且独立于操作系统的命令语言。RMAN 提供了一种灵活的方式来执行下列操作: 备份数据库、表空间、数据文件、控制文件和归档日志 存储频繁执行的备份和恢复操作 执行增量块级别备份 跳过未使用的块 指定备份限制5.3 RMAN
  • 153.   RMAN的优点 RMAN提供的几种功能是在使用操作系统命令执行用户管理的备份时所不具备的。 您可以将频繁执行的操作作为脚本存储在数据库中。 使用增量块级别备份功能,您可只备份自上次备份后发生更改的块。这还可以减少在ARCHIVELOG 模式下执行恢复操作所需的时间。 您可以使用RMAN 来管理备份片的大小,并通过并行化备份操作来节省时间。 RMAN 操作可以和操作系统的日程安排集成在一起,以实现自动备份操作。 RMAN会自动跟踪新的数据文件和表空间,这样就不再需要在脚本中添加新的表空间和数据文件 RMAN只备份使用过的数据块]),这样RMAN备份映像通常小于联机备份脚本的备份映像 5.3 RMAN
  • 154. RMAN组件 5.3 RMAN
  • 155. RMAN组件 服务器会话:由RMAN 调用的服务器进程(UNIX) 或线程(Windows NT) 与目标数据库连接,通过PL/SQL 接口执行备份、还原和恢复功能。 目标数据库:正在使用RMAN 对其进行备份和恢复操作的数据库称作目标数据库。目标数据库的控制文件包含其物理结构的有关信息,例如,数据文件的大小和位置、联机和归档重做日志文件以及控制文件。在备份和恢复操作过程中,由RMAN 调用的服务器会话将使用这些信息。 RMAN 资料档案库:RMAN 在执行备份、还原和恢复操作时使用的数据称为RMAN 元数据。这些元数据存储在目标数据库的控制文件和可选的恢复目录数据库中。尽管不是必须创建恢复目录才可以使用RMAN,但使用恢复目录却是有益的。恢复目录应放在目标数据库之外的另一数据库中。 通道:要执行并记录备份和恢复操作,RMAN 需要链接至目标数据库。该链接称为通道。您可手动分配通道,也可以使用自动通道分配功能预先配置通道。 介质管理库:介质管理库(MML) 由RMAN 在写入磁带或从磁带读取时使用。使用磁带介质所需的附加介质管理软件由介质和存储系统供应商提供。5.3 RMAN
  • 156. 1、目录数据库的创建和授权 目录数据库是用来保存RMAN备份恢复信息的数据库,其DB_NAME可以为任何有效数据库名,为了表述方便,本文后续内容均以CATALOG作为目录数据库的DB_NAME,目录数据库应与防伪税控产品数据库(目标数据库)物理分离,防伪税控数据库可与其它数据库共用同一个目录数据库。 SQL>CREATE TABLESPACE CATTBS DATAFILE ‘C:\ORADATA/CATALOG/CATTBS.DBF’ SIZE 200M SQL>alter user rman account unlock ; SQL>GRANT CONNECT,RESOURCE, RECOVERY_CATALOG_OWNER TO RMAN ; 5.3 RMAN
  • 157. 2、创建恢复目录,注册fwsk 在目标数据库(防伪税控税务端系统)创建目录数据库的网络服务名。 打开RMAN 连接数据库 RMAN>CONNECT CATALOG rman/rman@ht_rcat; 创建恢复目录 RMAN>CREATE CATALOG TABLESPACE CATTBS; 在恢复目录中注册防伪税控数据库 RMAN>connect target /; RMAN>register database; 5.3 RMAN
  • 158. 3、备份保存策略设置 RMAN>connect catalog RMAN/RMAN@ht_rcat; RMAN>connect target /; RMAN>CONFIGURE DEVICE TYPE DISK PARALLELISM 2; RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;(是指在恢复目录中最近3天的数据备份是有效的,3天后过期 ) RMAN>CONFIGURE RETENTION POLICY TO REDUNDANCY 2;(是指在恢复目录中最近2个冗余备份有效,2个冗余之前的备份过期 ) 5.3 RMAN
  • 159. 4、备份脚本 全库备份 rman log='/oracle/admin/fwsk/scripts/fwskbak.log' append <
  • 160. 4、备份脚本 1级增量备份 rman log='/oracle/admin/fwsk/scripts/fwskbak.log' append <
  • 161. 5、备份管理 显示RMAN的默认设置及备份保存策略 $rman RMAN>connect catalog RMAN/RMAN@ht_rcat; RMAN>connect target /; RMAN>show all; 显示过期备份集 RMAN>report obsolete;5.3 RMAN
  • 162. 5、备份管理 显示最近两天未备份数据文件 RMAN>report need backup days=2; 检测备份正确性 RMAN>crosscheck backup; 检测备份日志正确性 RMAN>crosscheck archivelog all; 5.3 RMAN
  • 163. 5、备份管理 RMAN备份集可恢复性检测 RMAN>run { restore database validate check logical ; } 说明:本操作会比较消耗资源和时间。 5.3 RMAN
  • 164. 6、RMAN恢复 全库恢复 rman log='/oracle/admin/fwsk/scripts/fwskrecv.log' <
  • 165. 6、RMAN恢复 表空间恢复 rman log='/oracle/admin/fwsk/scripts/fwskrecv.log' <
  • 166. 6、RMAN恢复 数据文件恢复 rman log='/oracle/admin/fwsk/scripts/fwskrecv.log' <
  • 167. 6、RMAN恢复 控制文件恢复 rman log='/oracle/admin/fwsk/scripts/fwskrecv.log' <
  • 168. 谢 谢 !