Oracle Database 10g:DBA 中文手册I


Oracle Database 10g: DBA 中文手册 I (第200-300页) 海量oracle资料下载 http://www.database8.com 违反约束条件 违反约束条件 的示例包括: • 插入重复的主键值 • 删除引用完整性约束 条件中的子行的父行 • 更新列值后超出了检查约束条件的范围 ID AGE … 22 101 … X -30 102 … … 49 101 … 16 103 … … 5 版权所有 © 2006,Oracle。保留所有权利。 违反约束条件 提交不符合约束条件的 DML 时就会出现违反约束条件的情况。违反约束条件的形式有多 种,其中包括: • 唯一性:尝试在采用了唯一约束条件的列中使用重复值,例如,在某一列是主键, 或者该列已建立唯一索引的情况下。 • 引用完整性:违反了具有父行的每个子行的规则。 • 检查:尝试在不遵循列定义规则的列中存储一个值。例如,AGE 列可能具有强制将 其设置为正数的检查约束条件。 约束条件状态 DISABLE NOVALIDATE DISABLE VALIDATE ENABLE NOVALIDATE ENABLE VALIDATE 无 DML 新数据 现有数据 版权所有 © 2006,Oracle。保留所有权利。 约束条件状态 为了更好地处理数据可能暂时违反约束条件的情况,可将约束条件指定为不同的状态。可 以启用 (ENABLE) 或禁用 (DISABLE) 完整性约束条件。如果启用约束条件,在数据库中 输入或更新数据时就会检查数据。此时,禁止输入不符合约束条件规则的数据。如果禁用 约束条件,则可以在数据库中输入不符合规则的数据。完整性约束条件可处于下列其中一 种状态: • DISABLE NOVALIDATE • DISABLE VALIDATE • ENABLE NOVALIDATE • ENABLE VALIDATE DISABLE NOVALIDATE:不检查新数据和现有数据,因此这些数据可能不符合约束条件。 当数据来自验证过的源,而且表是只读表时,通常会使用此状态。因此,不会将新数据输 入表中。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-17 约束条件状态(续) DISABLE VALIDATE:如果约束条件处于此状态,则不允许对有约束条件的列进行任何 修改。因为如果在验证现有数据后,又允许将未经检查的数据输入表中,就会出现不一致 的情况。当必须验证现有数据,但不需要修改数据,而且不需要另外为性能而建立索引时, 通常会使用此状态。 ENABLE NOVALIDATE:新数据符合约束条件,但现有数据处于未知状态。当可以更正 现有的约束条件违规情况,同时又不允许将新的违规数据输入系统时,常常会使用此状态。 ENABLE VALIDATE:新数据与现有数据均符合约束条件。这是约束条件的典型状态和默 认状态。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-18 约束条件检查 检查约束条件 的时机: • 若是非延迟约束条件 ,在执行语句时 • 若是延迟约束条件, 在发出 COMMIT 时 案例:DML 语句后接 COMMIT 1 2 非延迟约束条件 已检查 COMMIT 已发出 3 延迟约束条件已检查 4 COMMIT 已完成 版权所有 © 2006,Oracle。保留所有权利。 约束条件检查 可以将约束条件的有效性检查延迟到事务处理结束时。 非延迟约束条件又称即时约束条件,是在每个 DML 语句结束时强制执行的。违反约束 条件会导致回退语句。如果约束条件导致诸如 delete cascade 等操作,则认为该操 作是导致执行此操作的语句的一部分。定义为“非延迟”的约束条件不能更改为延迟约 束条件。 延迟约束条件是提交事务处理时才检查的约束条件。如果在提交时检测到任何违反约束 条件的情况,则会回退整个事务处理。当同时输入外键关系中的父行和子行(如订单录 入系统中,这种情况下需要同时录入订单及订单项)时,这些约束条件非常有用。 按下列一种方式可指定将约束条件定义为“延迟”: • “立即开始”,指定在默认情况下,约束条件必须用作即时约束条件,除非另外显 式进行了设置。 • “延迟开始”,指定在默认情况下,只在事务处理结束时强制使用约束条件。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-19 使用 SQL 创建约束条件:示例 a ALTER TABLE countries ADD (UNIQUE(country_name) ENABLE NOVALIDATE); b ALTER TABLE employees ADD CONSTRAINT pk PRIMARY KEY (employee_id) c CREATE TABLE t1 (pk NUMBER PRIMARY KEY, fk NUMBER, c1 NUMBER, c2 NUMBER, CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1, CONSTRAINT ck1 CHECK (pk > 0 and c1 > 0)); 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL 创建约束条件:示例 幻灯片中显示创建约束条件的三个示例: a. 执行此语句后,需要对 COUNTRIES COUNTRY_NAME 表执行任何插入或更新操作,以生成唯一的 b. c. 值。但是,有可能在发出此语句后,表中已存在非唯一的 COUNTRY_NAME 值。NOVALIDATE 关键字指示应忽略这些值。仅新行受到约束条件 的约束。 使用此语句可向雇员表添加主键。约束条件名称为 PK,主键为 EMPLOYEE_ID 列。 此语句用于在创建表时定义约束条件,而不是以后使用 ALTER TABLE 语句进行定 义。RI 约束条件强制使 FK 列中的值必须存在于 T1 表的主键列中。CK1 约束条件 强制使 PK 和 C1 列大于零。 注:每个约束条件都有一个名称。如果 DDL 语句中没有提供名称,则分配系统提供的名 称,该名称以 SYS_ 开头。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-20 查看表中的列 版权所有 © 2006,Oracle。保留所有权利。 查看表中的列 要使用 Enterprise Manager 查看表的属性,请执行以下步骤: 1. 在“Database Ad ministration(数据库管理)”页的“Schema(方案)”区域中,单 击“Tables(表)”链接。 2. 从“Results(结果)”列表中选择表,单击“View(查看)”按钮可查看表的属性。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-21 查看表的内容 版权所有 © 2006,Oracle。保留所有权利。 查看表的内容 要使用 Enterprise Manager 查看表中的行,请执行以下步骤: 1. 在“Tables(表)”页上选择表。 2. 在“Actions(操作)”菜单中选择“View Data(查看数据)”,然后单击 “Go(开始)”。 此时会出现“View Data for Table(查看表数据)”页。“Result(结果)”区域中会显示 表的行数据。“Query(查询)”框会显示执行 SQL 查询后生成的结果。在这一页中,单 击任一列名,可按升序或降序对列中的数据进行排序。如果要更改查询,则可单击 “Refine Query(优化查询)”按钮。在“Refine Qu ery for Table(优化表查询)”页上, 可选择要显示的列,然后可通过为 SQL 语句指定 WHERE 子句来限制查询结果。 有关 SQL 语句中 WHERE 子句的详细信息,请参阅《Oracle 数据库 SQL 参考》。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-22 表操作 版权所有 © 2006,Oracle。保留所有权利。 表操作 可以选择一个表,然后对该表执行操作。下面是其中一些操作: • Create Like(类似创建):使用此操作可创建与选定表具有相同结构的表。必须更 改约束条件名称。可以添加或删除列,还可在创建表之前对表结构进行其它更改。 • Create Index(创建索引):使用此选项可创建表的索引。 • Generate DDL(生成 DDL):生成表示已存在的表的 DDL。随后,此 DDL 可复制 到文本文件以用作脚本或进行归档。 • Grant Privileges(授予权限):默认情况下,创建表后,只有所有者才可对其执行 操作。其它用户若要对表执行 DML 甚至 DDL,所有者必须授予这些用户权限。 • Show Dependencies(显示相关性): 显示此表依赖的对象,或依赖于此表的对象。 • View Data(查看数据):以只读方式选择和显示表中的数据。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-23 删除表 删除一个表将会删除 : • 数据 • 表结 构 • 数据 库触发器 • 相应 的索引 • 关联 的对象权限 DROP TABLE DROP TABLE hr.employees PURGE; • • 语 句的可选子句: CAS CADE CONSTRAINTS:相关的引用完整性约束条件 PUR GE:无法闪回 版权所有 © 2006,Oracle。保留所有权利。 删除表 语法: DROP TABLE [schema.] table [CASCADE CONSTRAINTS] [PURGE] DROP TABLE 命令可删除数据、表结构和关联的对象权限。使用 DROP TABLE 的某些注 意事项如下: • 如果不使用 PURGE 子句,则表定义、关联索引和触发器会被放置在回收站中。表数 • • 据仍然存在,但不存在表定义的情况下无法访问。如果使用 Enterprise Manager 删除 了表,则不需要使用 PURGE 子句。 使用 FLASHBACK TABLE 命令可从回收站恢复方案对象。PURGE RECYCLEBIN 命令 可用于清空回收站。 需要使用 CASCADE CONSTRAINTS 选项才能删除所有相关的引用完整性约束条件。 PURGE 注:如果不使用 选项,那么表及表索引所占用的空间仍会计入用户在有关表空间 中的可用限额之内。也就是说,这些空间仍视为已占用。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-24 截断表 TRUNCATE TABLE hr.employees; • • 截断表会使表的行数据不可用,而且可选择释放占用空间。 会截断相应的索引。 版权所有 © 2006,Oracle。保留所有权利。 截断表 语法: TRUNCATE TABLE [schema.] table [{DROP | REUSE} STORAGE] 使用此命令会产生以下影响: • 通过将高水位标记 (HWM) 设置到表开头,将表标记为空,从而使表行不可用。 • 由于 TRUNCATE TABLE 是 DDL 命令,因此不会生成还原数据,而且会隐式提交 命令。 • 还会截断相应的索引。 • 不会截断外键引用的表。 • 使用此命令时不会触发删除触发器。 要删除所有表行,这种方式通常比发出 DELETE 语句的速度要快很多倍,原因如下: • Oracle 数据库重置表的 HWM,而不是象 DELETE 一样处理每行。 • 不生成还原数据。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-25 … WHERE key = 22 索引 > 方案 约束条件 索引 视图 序列 临时表 数据字典 键 行 指针 22 索引 22 表 版权所有 © 2006,Oracle。保留所有权利。 索引 索引是与表关联的可选结构。通过创建索引可提高数据更新和检索的性能。Oracle 索引提 供到数据行的直接访问路径。 可以对表的一个或多个列创建索引。创建索引后,Oracle 服务器会自动维护和使用索引。 表数据的更新(如添加新行、更新行或删除行)会自动传播到所有相关的索引,这些对用 户来说是完全透明的。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-26 索引类型 根据需要,为 您提供若干类型的索引 结构: • B 树索引采用二进制树 的形式,它是默认的 索引类型。 • 在位图索引中,每个 建立索引的独特值都 有一个位图, 每一位的位置表示一 行,其中可能包含( 也可能不包含) 索引值。对于低基数 列而言,这是最佳结 构。 版权所有 © 2006,Oracle。保留所有权利。 索引类型 下面是最常见的索引形式: • B 树 • 位图 B 树索引的键值存储在平衡树(B 树)中,这样可以进行快速的二进制搜索。 在位图索引中,每个要建立索引的独特键值都有一个位图。在每一位图中,为要建立索引 的表中的每一行保留了一位。这样,独特值很少时可快速进行查找;也就是说,索引列的 基数较低。性别指示器就是这种索引的一个示例。这种指示器只包含值“M”和“F”, 因此,只需要搜索两个位图。再举例说,如果位图索引用于 phone_number 列,则需要 管理和搜索太多的位图,这种情况下效率非常低。所以,请对低基数列使用位图索引。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-27 B 树索引 根 索引条目 分支 叶 索引条目头 键列长度 键列值 ROWID 版权所有 © 2006,Oracle。保留所有权利。 B 树索引 B 树索引的结构 索引的顶层为根,它包含指向索引中下一层次的条目。下一层次为分支块,它又指向位于 索引中下一层次的块。位于最低层次的是叶节点,它包含指向表行的索引条目。叶块双向 相互关联,这便于按键值升序或降序扫描索引。 索引叶条目的格式 索引条目包括下列组成部分: • 条目头,用于存储列数和锁定信息 • 键列长度-值对,用于定义键中列大小,后面跟着列值(这种对的数目就是索引中的 最大列数) • 包含键值的行的 ROWID Oracle Database 10g:数据库管理 - 课堂练习 I 7-28 B 树索引(续) 索引叶条目的特性 在非分区表的 B 树索引中: • 当多个行具有相同的键值时,如果不压缩索引,键值会出现重复 • 当某行包含的所有键列为 NULL 时,该行没有对应的索引条目。因此,当 句指定了 NULL 时,将始终导致全表扫描。 • 因为所有行属于同一个段,所以要使用受限的 ROWID 指向表行 对索引执行 DML 操作的效果 WHERE 子 对表执行 DML 操作时,Oracle 服务器会维护所有索引。下面说明对索引执行 DML 命令 产生的效果: • 执行插入操作导致在相应块中插入索引条目。 • 删除一行只导致对索引条目进行逻辑删除。在删除块中的所有条目之前,已删除行 占用的空间不可用于新条目。 • 更新键列导致对索引进行逻辑删除和插入。PCTFREE 设置对索引没有影响,但创建 时除外。即使索引块的空间少于 PCTFREE 指定的空间,也可以向索引块添加新条目。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-29 位图索引 表 文件 3 块 10 块 11 索引 块 12 键 开始 ROWID 结束 ROWID 位图 版权所有 © 2006,Oracle。保留所有权利。 位图索引 在以下特定情况下,位图索引比 B 树索引更有优势: • 表具有数百万行且键列的基数较低时,也就是列的独特值极少时。例如,对于护照 记录表中的性别和婚姻状况列,位图索引可能比 B 树索引更可取。 • 经常使用与 OR 运算符有关的多个 WHERE 条件的组合进行查询时。 • 键列上存在只读活动或较少更新活动时。 位图索引的结构 位图索引也可以按 B 树形式进行组织,但是,叶节点会存储每个键值的位图,而不是 ROWID 列表。位图中每一位与一个可能的 ROWID 对应,如果已 设置该位,则表示有对应 ROWID 的行包含键值。 如图所示,位图索引的叶节点包含: • 条目头,其中包含列数和锁定信息 Oracle Database 10g:数据库管理 - 课堂练习 I 7-30 位图索引(续) 位图索引的结构(续) • 由每个键列的长度-值对组成的键值。在本示例中,键只由一列组成,第一个条目的 键值为 Blue。 • 开始 ROWID,在本示例中它指定块号 10、行号 0 和文件号 3 • 结束 ROWID,在本示例中它指定块号 12、行号 8 和文件号 3 • 由位字符串组成的位图段。(如果对应行包含键值,则会设置位;如果对应行不包 含键值,则不会设置位。Oracle 服务器使用已获专利的压缩技术存储位图段。) 开始 ROWID 是位图中的位图段指向的第一行的 ROWID,就是说,位图的第一个位对应于 该 ROWID,位图的第二个位对应于块中的下一行。结束 ROWID 是一个指针,它是指向由 位图段覆盖的表中的最后一行。位图索引使用受限的 ROWID。 使用位图索引 B 树用于定位叶节点,这些节点包含指定键值的位图段。开始 ROWID 和位图段用于定位 包含键值的行。 更改表中的键列后,位图也一定经过修改。这会导致锁定相关的位图段。由于锁是在整个 位图段上获得的,因此,在第一个事务处理结束之前,位图覆盖的行不能通过其它事务处 理来更新。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-31 索引选项 • • • • • • 唯一索引可确保每个索引值是唯一的。 索引可按升序或降序 存储其键值。 反向关键字索引以反 向顺序存储其键值字 节。 组合索引是基于多列的索引。 基于函数的索引是以 函数返回值为基础的 索引。 压缩索引会删除重复 的关键字值。 版权所有 © 2006,Oracle。保留所有权利。 索引选项 为了提高检索效率,在索引中按降序存储关键字可能会更有益。这是根据最常用的数据访 问方式得出的结论。 反向关键字索引会按反向顺序存储索引值的字节。这可以减少索引中特定热点的活动量。 如果多个用户正按同一顺序处理数据,那么在任何给定时刻,关键字值的前缀部分(当前 正在处理的)是非常接近的值。因此,在索引结构的该区域中会发生大量的活动。为反向 字节样式的关键字值建立索引后,反向关键字索引可在索引结构中分散这些活动。 按多列结合创建的索引称之为组合索引。例如,根据一个人的姓与名可创建一个索引: CREATE INDEX name_ix ON employees (last_name, first_name); Oracle Database 10g:数据库管理 - 课堂练习 I 7-32 索引选项(续) 使用基于函数的索引可建立函数返回值的索引。这个函数可以是一个内置 SQL 函数、外 部提供的 PL/SQL 函数、或用户编写函数。这样一来,服务器根据索引表达式执行搜索时, 不必为每个关键字值调用函数。以下示例为返回的树木材积建立索引,树木材积由函数根 据每一种树的树种、高度和树围(这些是 TREES 表中的列)进行计算: CREATE INDEX tree_vol_ix ON TREES(volume(species,height,circumference)); 然后,WHERE 字句中包含表达式 volume(species,height,circumference) 的任 何查询都能够利用此索引,执行速度更快,因为每一种树的材积计算已经完成。可以向维 护普通索引一样自动维护一般基于函数的索引。 使用压缩索引可减少执行过程中占用的磁盘空间。由于删除了重复的关键字值,指定的磁 盘空间量可以容纳更多的索引条目,因此,相同的时间段内可以从磁盘读取更多的条目。 必须分别在写入索引和读取索引时执行压缩和解压缩。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-33 创建索引 CREATE INDEX my_index ON employees(last_name, first_name); 版权所有 © 2006,Oracle。保留所有权利。 创建索引 在“Administration(管理)”页的“Schema(方案)”标题下单击“Indexes(索引)” 链接,可查看“Indexes(索引)”页。可查看索引属性,或使用“Actions(操作)”菜 单查看索引的相关性。 根据对表设置的约束条件,可显式或隐式建立索引。定义主关键字就是隐式建立索引的一 个示例,这种情况中为了强制设置列的唯一性,会自动建立唯一索引。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-34 LOCATION 表 什么是视图 > 方案 约束条件 索引 视图 … COUNTRY 表 视图 CREATE VIEW v AS SELECT location_id, country_name FROM locations l, countries c WHERE l.country_id = c.country_id AND c.country_id in ('AU','BR'); 版权所有 © 2006,Oracle。保留所有权利。 什么是视图? 视图是一个或多个表或其它视图中数据的自定义表示。由于视图可隐藏极其复杂的条件、 联接,以及其它复杂表达式和 SQL 构造,所以可认为视图是存储的查询。实际上,视图 并不包含数据;而是从所基于的表中获取数据。这些表称为视图的基表。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-35 创建视图 版权所有 © 2006,Oracle。保留所有权利。 创建视图 和表一样,可以按照某些限制条件对视图执行查询、更新、插入和删除操作。对视图执行 的所有操作实际上都会影响到视图的基表。通过限制访问表中预定义的一组行与列,视图 还另外可以提供安全性。此外,视图不仅可隐藏数据复杂性,还可存储复杂查询。 要查看数据库中定义的视图,请单击“Administration(管理)”页上“Schema(方案)” 标题下的“Views(视图)”链接。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-36 序列 序列是按照某 种模式自动生成整数 的一种 机制。 • 序列具有名称,指 出了请求下一个值 时引用序列的方式 。 • 序列不与任何特定 的表或列相关联。 • 增长方式可为升序 ,也可为降序。 • 编号之间的间隔可 随意。 • 达到限额时,序列 可循环。 > 方案 约束条件 索引 视图 序列 临时表 数据字典 5 版权所有 © 2006,Oracle。保留所有权利。 序列 要检索序列中的下一个值,请根据其名称引用它;序列与表或与列之间没有关联。 如果没有将序列定义为循环序列,则指定编号出现后,不会再次出现该编号。有时,应用 程序请求一个值,但是该值从未结束在数据库中的使用或存储。这样可能会造成存储编号 的表中出现编号不连续。 通过缓存序列号可改善性能,因为为了加速访问,内存中预分配了一组编号。如果存在实 例失败的情况,就不会使用任何缓存的序列号,这会造成间隔。 注:如果应用程序要求不能存在间隔,那么应用程序应实施一个自定义编号生成器。但是, 此方法会导致性能下降。如果使用一个表存储一个值,而且每次请求时都递增该值、更新 该表,那么这个进程会成为系统范围内的一个瓶颈。这是因为每个会话都必须等待该机制, 而为了确保不出现重复或间隔,该机制每次只能处理一个请求。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-37 1 2 3 4 创建序列 版权所有 © 2006,Oracle。保留所有权利。 创建序列 通过在“Administration(管理)”页的“Schema(方案)”标题下单击“Sequences (序列)”链接,使用 Enterprise Manager 可查看并创建序列。序列创建选项汇总如下: • Name:(名称):序列的名称,表示序列的引用方式。 • Schema(方案):序列的所有者。 • Maximum Value(最大值):指定序列可以生成的最大值。这是一个整数值,不能 超过 28 位数。它必须大于“Minimum Value(最小值)”和“Initial(初始值)”。 使用“Unlimited(无限制)”指定升序序列的最大值为 10 ,降序序列的最大值为 -1。默认值为“Unlimited(无限 制)”。 • Minimum Value(最小值):指定序列的最小值。这是一个整数值,不能超过 28 位 数。它必须小于或等于“Initial(初始值)”,且小于“ Maximum Value(最大 值)”。使用“Unlimited(无限制)” 指定升序序列的最小值为 1,降序序列的最 小值为 -1026 。默认值为“Unlimited(无限制)”。 • Interval(间隔):指定序列号之间的间隔。这个整数值可以是任意正整数或负整数, 但不能为零。它不能超过 28 位数。默认值为 1。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-38 2 7 创建序列(续) • Initial(初始值):指定要生成的第一个序列号。使用此子句,以一个大于最小值的 值开始一个升序序列,或以一个小于最大值的值开始一个降序序列。 • Cycle Values(循环值):升序序列达到序列最大值后,它会生成序列最小值。降序 序列达到序列最小值后,它会生成序列最大值。如果没有选择此选项,当序列用尽 后尝试检索值时就会返回错误。 • Order Values(顺序值):此选项可确保按请求顺序生成序列号。如果使用序列号作 为时间戳,则可以使用此子句。对用于生成主关键字的序列而言,确保顺序通常不 重要。如果使用安装了 Real Application Clusters 的 Oracle 数据库,只在要确保按顺 序生成序列时才需要此选项。 • Cache Options(缓存选项):指定为了加速访问,Oracle 数据库在内存中预分配并 保留的序列值的数量。这是一个整数值,不能超过 28 位数。此参数的最小值为 2。 对循环序列而言,此值必须小于循环中的值的数量。因为缓存的值的数量不能多于 指定序列号循环中可以容纳的数量。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-39 使用序列 版权所有 © 2006,Oracle。保留所有权利。 使用序列 请引用使用下列假例的 SQL 语句中的序列值: • CURRVAL:返回序列的当前值 • NEXTVAL:递增序列,返回下一个值 必须根据序列名称确认 CURRVAL 和 NEXTVAL: sequence.CURRVAL sequence.NEXTVAL 首次引用 NEXTVAL 会返回序列的初始值。对 NEXTVAL 的后续引用会按照定义增量递增 序列值,并返回新值。对 CURRVAL 的任何引用永远返回序列的当前值,它是上次引用 NEXTVAL 所返回的值。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-40 临时表: 临时表 > 方案 约束条件 索引 视图 序列 临时表 数据字典 • • • 会话或事务处理结束 时会自动清除存储数据 为每个会话提供专用数据存储 可供所有会话使用, 而不影响各个会话的专 用数据 版权所有 © 2006,Oracle。保留所有权利。 临时表 如果需要为了执行某一任务而存储专用数据,而且要在执行该任务后结束事务处理或会话 时清除数据,就可以利用临时表。临时表提供了这种功能,这样您不必在其它会话中隐藏 数据,也不必在结束时删除所生成的数据。对于某一会话来说,唯一可见的临时表数据是 该会话已经插入的数据。 临时表可专用于事务处理,也可专用于会话。对于专用于事务处理的临时表而言,数据在 事务处理期间存在;对于专用于会话的临时表而言,数据在会话期间存在。在这两种情况 下,通过会话插入的数据专用于会话。每个会话仅可查看和修改它自己的数据。因此,从 不会在临时表的数据上获得 DML 锁。下列子句控制行的生存期: • ON COMMIT DELETE ROWS:指定插入行的生存期仅为事务处理的持续时间 • ON COMMIT PRESERVE ROWS:指定插入行的生存期为会话的持续时间 使用 CREATE GLOBAL TEMPORARY TABLE 语句可创建临时表。在临时表中可以创建索引、 视图及触发器,还可以使用“Export and Import(导出和导入)”或“Data Pump(数据 泵)”导出和导入临时表的定义。但是,即使使用了 ROWS 选项,也不会导出数据。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-41 临时表(续) 除了上述已经提到的会引起数据删除的事件,还可以使用 TRUNCATE TABLE 命令有效地 强制删除数据。使用这个命令可删除所有已插入的数据。使用这个命令比使用 DELETE 命令更有效。 在临时表中可以创建索引、视图及触发器。 通过单击“Create Table: Table Organization(创建表: 表组织)”页上的“Temporary (临时)”选项,可以使用 Enterprise Manager 创建临时表。单击“Continue(继续)”, 在下一页中可指定临时表是专用于会话还是专用于事务处理。“Tablespace(表空间)” 字段处于禁用状态,因为总是在用户的临时表空间中创建临时表;不可以指定其它表空间。 注:GLOBAL 关键字基于国际标准化组织 (ISO) 在 SQL 标准中指定的术语。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-42 临时表:注意事项 • 使用 GLOBAL TEM PORARY 子句创建临时 表: • • CREATE GLOBAL TEMPORARY TABLE employees_temp ON COMMIT PRESERVE ROWS AS SELECT * FROM employees; 使用 TRUNCATE TABLE 命令删除表内 容。 在临时表中可创建下 列项: – 索引 – 视图 – 触发器 版权所有 © 2006,Oracle。保留所有权利。 临时表:注意事项 使用 CREATE GLOBAL TEMPORARY TABLE 语句可创建临时表。在临时表中可以创建索引、 视图及触发器,还可以使用“Export and Import(导出和导入)”或“Data Pump(数据 泵)”导出和导入临时表的定义。但是,即使使用了 ROWS 选项,也不会导出数据。 除了上述已经提到的会引起数据删除的事件,还可以使用 TRUNCATE TABLE 命令有效地 强制删除数据。使用这个命令可删除所有已插入的数据。使用这个命令比使用 DELETE 命令更有效。 注:GLOBAL 关键字基于国际标准化组织 (ISO) 在 SQL 标准中指定的术语。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-43 数据字典:概览 表 索引 视图 用户 方案 过程 等等 > 方案 约束条件 索引 视图 序列 临时表 数据字典 SELECT * FROM dictionary; 版权所有 © 2006,Oracle。保留所有权利。 数据字典:概览 Oracle 的数据字典是对数据库的描述。其中包含了数据库中所有对象的名称和属性。创建 或修改任何对象都会引起更新数据字典,这样可反映那些更改。这些信息存储在由 Oracle 数据库维护的基表中,不过,通过使用预定义视图(而不是直接读取表)可访问这些表。 数据字典: • 由 Oracle 数据库服务器使用,可用于查找关于用户、对象、约束条件和存储的信息 • 对象结构或定义已修改时,由 Oracle 数据库维护 • 任意用户可用来查询关于数据库的信息 • 由 SYS 用户所有 • 不得使用 SQL 直接进行修改 注:DICTIONARY 数据字典视图或其 DICT 同义词包含了数据字典中一切对象的名称和 说明。使用 DICT_COLUMNS 视图可查看视图列及其定义。有关各种视图的完整定义,请 参阅“Oracle 数据库参考”文档。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-44 数据字典视图 DBA_ ALL_ USER_ 谁可以查询 DBA 每个用户 每个用户 内容 一切对象 用户有权查看 的一切对象 用户拥有的 一切对象 下列项的子集 无 DBA_ 视图 ALL_ 视图 注 可能有其它仅限 DBA 使用的列 包括用户拥有的对象 除了缺少 OWNER 列, 通常与 ALL_ 相同。某 些视图有象 PUBLIC 别 名一样的简称。 版权所有 © 2006,Oracle。保留所有权利。 数据字典视图 视图前缀指出了指定用户可查看的数据类型或数据量。使用 DBA_ 前缀时,只有拥有 ALL_ DBA 权限的用户才可访问全局视图的所有对象。下一个权限级别是 前缀级别,它 USER_A 表示查询用户有权查看的所有对象,无论用户是否拥有这些对象。例如,如果 被授权访问属于 USER_B 的表,那么 USER_A 会看到该表列在与表名称有关的任何 ALL_ 视图中。前缀 USER_ 代表最小的可见范围。只显示属于查询用户的对象;即,存在于用 户自己方案中的对象。 一般情况下,每个视图集都是拥有更高授权的视图集的子集,无论按行还是按列。指定视 图集中的所有视图并不都在其它视图集中有对应的视图。这取决于视图中信息的性质。例 如,有一个 DBA_LOCK 视图,但是并不存在 ALL_LOCK 视图。这是因为只有 DBA 才对 有关锁的数据感兴趣。因此,为了满足需要,应确保选择了适当的视图集。如果您有权访 问 DBA 视图,则还可能要仅查询 USER 版本的视图,因为您知道它属于您自己,而又不 希望在您的结果集中添加其它对象。 拥有 SYSDBA 或 SELECT ANY DICTIONARY 权限的用户可查询 DBA_ 视图。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-45 数据字典:用法示例 a SELECT table_name, tablespace_name FROM user_tables; b SELECT sequence_name, min_value, max_value, increment_by FROM all_sequences WHERE sequence_owner IN ('MDSYS','XDB'); c SELECT USERNAME, ACCOUNT_STATUS FROM dba_users WHERE ACCOUNT_STATUS = 'OPEN'; d DESCRIBE dba_indexes; 版权所有 © 2006,Oracle。保留所有权利。 静态数据字典:用法示例 幻灯片示例显示的查询回答了以下问题: a. 方案中已创建的表的名称(以及表所在表空间的名称)是什么? b. 关于(有权访问的)数据库中任意序列的重要信息是什么? c. 此数据库中当前可以登录的用户是哪些? d. DBA_INDEXES 视图的列是什么?显示可以查看数据库中所有索引的哪些信息。下 面列出了此命令的部分输出: SQL> DESCRIBE dba_indexes; Name Null? Type --------------- -------- ------------- OWNER NOT NULL VARCHAR2(30) INDEX_NAME NOT NULL VARCHAR2(30) INDEX_TYPE VARCHAR2(27) TABLE_OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) Oracle Database 10g:数据库管理 - 课堂练习 I 7-46 小结 在本课中 ,应该已经学会如何: • 定义方案对象和 数据类型 • 创建和修改表 • 定义约束条件 • 查看表列和表内 容 • 创建索引 • 创建视图 • 创建序列 • 说明临时表的用 法 • 使用数据字典 版权所有 © 2006,Oracle。保留所有权利。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-47 练习概览:管理方案对象 本练习包 含以下主题: • 创建包含列的表 • 创建约束条件: – 主键 – 外键 – 检查约束条件 • 创建索引 版权所有 © 2006,Oracle。保留所有权利。 Oracle Database 10g:数据库管理 - 课堂练习 I 7-48 管理数据和并发处理 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后, 应能完成以下工作: • 通过使用 SQL 来管理 数据 • 识别和管理 PL/SQL 对象 • 描述触发器和触发事 件 • 监视和解决锁定冲突 版权所有 © 2006,Oracle。保留所有权利。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-2 通过 SQL 处理数据 > SQL PL/SQL 锁定 SQL> INSERT INTO employees VALUES 2 (9999,'Bob','Builder','bob@abc.net',NULL,SYSDATE, 3 'IT_PROG',NULL,NULL,100,90); 1 row created. SQL> UPDATE employees SET SALARY=6000 2 WHERE EMPLOYEE_ID = 9999; 1 row updated. SQL> DELETE from employees 2 WHERE EMPLOYEE_ID = 9999; 1 row deleted. 版权所有 © 2006,Oracle。保留所有权利。 通过 SQL 处理数据 在数据库中使用基本数据操纵语言 (DML) 语句来处理数据。在“移动数据”一课中已简 要说明了这些语句,本课将详细进行讨论。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-3 INSERT 命令 • • 一次创建一行 通过另一个表插入多 行 版权所有 © 2006,Oracle。保留所有权利。 INSERT 命 令 INSERT INSERT 使用基本 语句一次可创建一行。如果使用所谓的子选择,使用 命令后 可将一个表中的若干行复制到另一个表中。这种方法又称为使用 幻灯片示例使用的是以下 INSERT 命令: INSERT SELECT 语句。 insert into dept_80 (select * from employees where department_id = 80}; 这种情况下,dept_80 表拥有与 employees 表完全相同的结构。如果不是这种情况, 则可命名每个表中的列。SELECT 语句中选择的各个值与要插入的表中的各个列相关联。 列值按 INSERT 和 SELECT 语句中指定的顺序进行匹配。只需要数据类型完全匹配。 例如: insert into just_names (first, last) (select first_name, last_name from employees); 其中 just_names 表中仅有的两列的数据类型与 employees 表中的 first_name 和 last_name 列的数据类型相同。 使用 INSERT SELECT 方法可将一个或多个表中的大量数据装入到另一个表中。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-4 UPDATE 命令 使用 UPDATE 命令可更改表的零 行或多行 版权所有 © 2006,Oracle。保留所有权利。 UPDATE 命 令 UPDATE 命令用于修改表中的现有行。UPDATE WHERE 命令所修改的行数取决于 WHERE WHERE 条件。 如果省略了 何行。 子句,则更改所有行。如果所有行都不满足 条件,则不修改任 Oracle Database 10g:数据库管理 - 课堂练习 I 8-5 DELETE 命令 使用 DELETE 命令可从表中删除零行或多行 版权所有 © 2006,Oracle。保留所有权利。 DELETE 命 令 DELETE 命令用于从表中删除现有行。DELETE WHERE 命令所修改的行数取决于 WHERE WHERE 条件。 如果省略了 子句,则删除所有行。如果所有行都不满足 条件,则不删除任 何行。注意,此示例中未删除任何行,这并不表示出现了错误;返回消息只是指出从表中 删除了零行。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-6 MERGE 命令 使用 MERGE 命令可通过单个命令同时执行 INSERT 和 UPDATE。 版权所有 © 2006,Oracle。保留所有权利。 MERGE 命令 使用 MERGE 命令可在同一命令中执行 UPDATE 和 INSERT。可将一个源中的数据合并到 另一个源,因而可选择插入新行和更新特定列(如果行已经存在)。 假设以下示例。JOBS 表中的某些数据如下所示: JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- -------------- ---------- ---------- AD_PRES President 20000 40000 FI_ACCOUNT Accountant 4200 9000 ST_CLERK Stock Clerk 2000 5000 IT_PROG Programmer 4000 10000 Oracle Database 10g:数据库管理 - 课堂练习 I 8-7 MERGE 命令(续) 以下是 JOBS_ACQUISITION 表的内容: JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- -------------- ---------- ---------- AD_PRES VP 20000 40000 DBA DB Admin 4200 9000 SA Sys Admin 2000 5000 使用 MERGE 命令将包含新 JOB_ID 的所有行插入到 JOBS 表中,如果 JOB_ID 已存在, 则使用 JOB_TITLE 更新现有 JOBS 行。结果“President”职位更改为“VP”,并且会添 加新职务“SA”和“DBA”。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-8 COMMIT 和 ROLLBACK 命令 下面的命令用 于结束事务处理: • COMMIT:使更改成为永 久性更改 • ROLLBACK:撤消更改 版权所有 © 2006,Oracle。保留所有权利。 COMMIT 和 ROLLBACK 命令 默认情况下,不会提交输入的每个 DML 命令。某些工具(包括 iSQL*Plus)有一些选项 可用来设置提交每个命令或一组命令。 在发出 COMMIT 或 ROLLBACK 之前,更改处于暂挂状态。只允许执行更改的用户查看更 改后的数据。其它用户可选择相同数据,但只能看到数据更改之前的内容。其它用户不能 对别的用户已更改的数据发出 DML。 默认情况下,当后一个用户尝试更改前一个用户正更改的行,后一个用户必须等待,直到 先执行更改的用户提交或回退更改为止。这由 Oracle 数据库的锁定机制自动控制。由于 锁定机制已经内置到行中,所以数据库绝不会用完锁。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-9 PL/SQL > SQL PL/SQL 锁定 Oracle 的 SQL 的过程 语言扩展 (PL/SQL) 是第四代编程 语言 (4GL)。它提供: • SQL 的过程扩展 • 平台和产品间的可移 植性 • 更高级的安全性和数 据完整性保护 • 支持面向对象的编程 版权所有 © 2006,Oracle。保留所有权利。 PL/SQL PL/SQL 是 Oracle 专有的第四代编程语言,它提供 SQL 的过程扩展。PL/SQL 为 Oracle 数据 库和应用程序提供了一种公共编程环境,不必考虑所用的操作系统或硬件平台。 借助 PL/SQL,您可以使用 SQL 语句处理数据,并且还可以使用过程结构(如 IF-THEN、 CASE 和 LOOP)控制编程流。另外,还可以声明常量和变量,定义过程和函数,使用集合 和对象类型,以及使用陷阱来捕获运行时错误。在 PL/SQL 程序中还可调用使用其它语言 (如 C、C++ 和 Java)编写的程序。 PL/SQL 还提供数据保护。调用方不必知道要读取或处理的数据结构就可进行调用。另外, 调用方不必具有访问这些对象的权限,只需要具有执行 PL/SQL 程序的权限就足够了。或者, 可选择使用另一种模式的权限来调用 PL/SQL,这种情况下,调用方必须有权执行调用程序 运行期间执行的每个语句。 因为 PL/SQL 代码在数据库内部运行,所以这种代码在执行数据量巨大的操作时非常有效, 并且可最大程度地降低应用程序的网络通信量。 有关 PL/SQL 的过程结构和用法的详细信息,请参阅《PL/SQL 用户指南与参考》中的文档。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-10 管理 PL/SQL 对象 数据库管理员 应可以: • 找出存在问题的 PL/SQL 对象 • 建议适当的 PL/SQL 用法 • 将 PL/SQL 对象 装入到数据库中 • 协助 PL/SQL 开 发人员诊断故障 版权所有 © 2006,Oracle。保留所有权利。 管理 PL/SQL 对象 通常作为 DBA 不负责将 PL/SQL 代码装入到数据库中,也不负责协助开发人员诊断故障。 另外,通常不要求 DBA 使用 PL/SQL 来编写应用程序,但作为 DBA 应对不同的 PL/SQL 对 象有足够的了解,才能为应用开发人员提出建议,也才能找出存在问题的对象。 在 Database Control 中,通过单 击“Schema(方案)”中的“Administration(管理)” 标签 可访问 PL/SQL 对象。单击对象类型时,可查看、修改和创建选定的 PL/SQL 对象的类型。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-11 PL/SQL 对象 PL/SQL 数据 库对象有许多类型: • 程序包 • 程序包体 • 类型主体 • 过程 • 函数 • 触发器 版权所有 © 2006,Oracle。保留所有权利。 PL/SQL 对 象 • 程序包:程序包是由逻辑上相关的一些过程和函数组成的集合。程序包的这一部分 又称为说明,用于描述应用程序的接口;还声明可供使用的类型、变量、常量、异 常错误、游标和子程序。 • 程序包体:程序包体完整地定义了游标和子程序,因此实施了说明。程序包体包含 实施明细和专用声明,这些内容不显示给调用方。 • 类型主体:类型主体是由与用户定义的数据类型相关联的一些方法(过程和函数) 组成的集合。有关用户定义的数据类型的详细信息,请参阅《Oracle 数据库应用程 序开发者指南:对象关系功能部件》。 • 过程:过程是用于执行特定操作的 PL/SQL 块。 • 函数:函数是通过使用 RETURN PL/SQL 命令返回单值的 PL/SQL 块。它是具有返回 值的过程。 • 触发器:触发器是在数据库中发生特定事件时执行的 PL/SQL 块。这些事件可以是 表事件,如在表中插入行时。也可以是数据库事件,如在用户登录数据库时。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-12 函数 版权所有 © 2006,Oracle。保留所有权利。 函数 SYSDATE SUM AVG TO_DATE PL/SQL 函数通常用于计算值。有许多内置函数,如 、 、 和 。 开发人员还可在编写应用程序时创建自己的函数。PL/SQL 函数的代码中必须 包含 RETURN 语句。如幻灯片所示,PL/SQL 函数是在输入名称、方案和源代码后创建的。 幻灯片中显示的 compute_tax 函数是使用以下 SQL 命令创建的: CREATE OR REPLACE FUNCTION compute_tax (salary NUMBER) RETURN NUMBER AS BEGIN IF salary<5000 THEN RETURN salary*.15; ELSE RETURN salary*.33; END IF; END; / Oracle Database 10g:数据库管理 - 课堂练习 I 8-13 过程 过程用于执行 特定操作。过程可: • 通过使用参数列表传 入和传出值 • 通过使用 CALL 命令来调用 版权所有 © 2006,Oracle。保留所有权利。 过程 PL/SQL 过程用于执行特定操作。与函数一样,过程可接受输入值,执行诸如 CASE 和 LOOP 等条件语句。 IF-THEN、 Oracle Database 10g:数据库管理 - 课堂练习 I 8-14 程序包 程序包是由函 数和过程组成的集合。 每个程序包应由两个 对象组成: • 程序包说明 • 程序包体 程序包说明 版权所有 © 2006,Oracle。保留所有权利。 程序包 程序包是函数与过程的组合。通过将一些函数和过程组成一个程序包,性能和可维护性会 有所提高。每个程序包应由两个独立编译的数据库对象组成: • 程序包说明:这个对象(有时称为程序包头)的对象类型为 PACKAGE,其中只包含 程序包中过程、函数和变量的定义。 • 程序包体:这个对象的对象类型为 PACKAGE BODY,包含程序包说明中定义的子程 序的实际代码。 使用点符号可调用程序包中的过程和函数: package_name.procedure or function name 在幻灯片显示的程序包中,可按如下方式调用子程序: SQL> SELECT money.compute_tax(salary) FROM hr.employees WHERE employee_id=107; SQL> EXECUTE money.give_raise_to_all; Oracle Database 10g:数据库管理 - 课堂练习 I 8-15 程序包说明和程序包体 版权所有 © 2006,Oracle。保留所有权利。 程序包体 程序包体: • 与程序包说明是分开的。因此,可以更改并重新编译程序包体代码,此时依赖于程 序包说明的其它对象不会标记为无效。 • 其中包含程序包说明中定义的子程序的代码。这是负责完成工作的部分。程序包说 明指示如何调用程序包中的子程序;程序包体是代码段。 • 只有在编译了程序包说明之后才能编译程序包体。可以在没有程序包体的情况下创 建程序包说明,但不能在没有程序包说明的情况下创建程序包体。 • 通过包装可隐藏代码明细。包装是一个可搞乱 PL/SQL 源代码的独立程序,所以可 在不暴露源代码的情况下传送 PL/SQL 应用程序。有关包装用法的详细信息,请参 阅《PL/SQL 用户指南与参考》。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-16 内置程序包 • • Oracle 数据库带有 350 多个内置 PL/SQL 程序包,可 用于: – 管理和维护实用程序 – 扩展功能 请使用 DESCRIBE 命令查看子程序。 版权所有 © 2006,Oracle。保留所有权利。 内置程序包 随 Oracle 数据库一起提供的内置 PL/SQL 程序包可用于访问扩展的数据库功能,例如高级 队列、加密和文件输入/输出 (I/O)。其中还包括了许多的管理和维护实用程序。 管理员可使用哪些程序包取决于数据库为之提供服务的应用程序的类型。以下是一些比较 常用的管理和维护程序包: • DBMS_STATS:用于收集、查看和修改优化程序统计信息 • DBMS_OUTPUT:通过 PL/SQL 生成输出 • DBMS_SESSION:通过 PL/SQL 访问 ALTER SESSION 和 SET ROLE 语句 • DBMS_RANDOM:生成随机数字 • DBMS_UTILITY:获取时间、CPU 时间和版本信息;计算散列值,以及执行许多其 它功能 • DBMS_SCHEDULER:调度可从 PL/SQL 调用的函数和过程 • DBMS_CRYPTO:对数据库数据进行加密和解密 • UTL_FILE:通过 PL/SQL 读写操作系统文件 注:有关这些内置程序包及其它内置程序包的详细信息,请参阅《PL/SQL 程序包与类型 参考》手册。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-17 触发器 版权所有 © 2006,Oracle。保留所有权利。 触发器 触发器是存储在数据库中的 PL/SQL 代码对象,它们会在某些事件发生时自动运行或 “触发”。在 Oracle 数据库中,允许许多的操作充当触发事件,包括插入到表中、用户 登录数据库以及尝试删除表或更改审计设置等等操作。 触发器可以调用其它过程或函数。触发器的代码最好简短一些,需要较长代码的内容尽量 放置到单独的程序包中。 DBA 使用触发器可支持基于值进行审计(在“实施 Oracle 数据库安全性”一课中会讨 论),可强制设置复杂约束条件,并且可自动处理很多任务。例如,幻灯片中显示的 SECURE_EMPLOYEES 触发器将所有 DML 语句记录到了保留表中。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-18 触发事件 事件类型 DML DDL 数据库 事件示例 INSERT、UPDATE、DELETE CREATE、DROP、ALTER、GRANT、 REVOKE、RENAME LOGON、LOGOFF、STARTUP、SHUTDOWN、 SERVERERROR 版权所有 © 2006,Oracle。保留所有权利。 触发事件 有许多事件可用来触发触发器,这些事件分为三类。 • DML 事件触发器在语句修改数据时触发。 • DDL 事件触发器在语句创建或以某种方式修改对象时触发。 • 数据库事件触发器在数据库中发生特定事件时触发。 大多数触发器可指定为在事件发生前或事件发生后触发。若是 DML 事件,触发器可设计 为因执行某一语句而触发一次,或者在修改每行时触发一次。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-19 锁定 > SQL PL/SQL 锁定 • • • 使用锁定可防止多个 会话同时更改同一数 据。 锁定是在指定语句的 最低可能级别自动获 取的。 锁定不会升级。 事务处理 1 事 务处理 2 SQL> UPDATE employees 2 SET salary=salary+100 3 WHERE employee_id=100; SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=100; 版权所有 © 2006,Oracle。保留所有权利。 锁定 会话必须先锁定要修改的数据,之后数据库才允许会话修改相应数据。锁定后,会话拥有 对数据的独占控制权,这样在释放锁定之前,其它任何事务处理都不能修改锁定的数据。 事务处理可以锁定个别数据行、多个数据行、甚至整个表。Oracle Database 10g 支持手动 锁定和自动锁定。自动获取的锁定总是选择尽可能低的锁定级别,以尽量减少与其它事务 处理的潜在冲突。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-20 锁定机制 • • • 高级数据并发处理: – 执行插入、更新和删除时使用行级锁定 – 查询不需要任何锁定 自动队列管理 在事务处理(使用 COMMIT 或 ROLL BACK 之前一直持有锁定 操作)结束 事务处理 1 事务处理 2 SQL> UPDATE employees 2 SET salary=salary+100 3 WHERE employee_id=100; SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=101; 版权所有 © 2006,Oracle。保留所有权利。 锁定机制 锁定机制用于在数据库中提供尽可能高的数据并发处理能力。事务处理修改数据时会获取 行级锁定,而不是块级或表级锁定。修改对象(如表移动)时会获取对象级锁定,而不是 整个数据库锁定或方案锁定。 数据查询不需要锁定,即使锁定了数据,查询也会成功进行(总是显示原始的、根据还原 信息重新构造的锁定之前的值)。 如果多个事务处理需要锁定同一资源,则第一个请求锁定的事务处理会获得锁定。其它事 务处理将等待,直到第一个事务处理完成为止。排队机制是自动进行的,不需要任何管理 员交互。 事务处理结束时会释放所有锁定。事务处理在发出 COMMIT 或 ROLLBACK 后会完成。如 果事务处理失败,同一后台进程会自动回退失败事务处理进行的所有更改,然后释放失败 事务处理持有的所有锁定。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-21 数据并发处理 时间: 09:00:00 事务处理 1 事务处理 2 事务处理 3 ... 事务 x UPDATE hr.employees SET salary=salary+100 WHERE employee_id=100; UPDATE hr.employees SET salary=salary+100 WHERE employee_id=101; UPDATE hr.employees SET salary=salary+100 WHERE employee_id=102; ... UPDATE hr.employees SET salary=salary+100 WHERE employee_id=xxx; 版权所有 © 2006,Oracle。保留所有权利。 数据并发处理 默认情况下,锁定机制采用细粒度行级锁定模式。不同的事务处理可在同一个表内更新不 同的行,彼此不相互干扰。 尽管默认模式是行级锁定,但 Oracle 数据库也根据需要支持在更高级别执行手动锁定。 SQL> LOCK TABLE employees IN EXCLUSIVE MODE; Table(s) Locked. 使用以上语句时,任何其它尝试更新锁定表中的行的事务处理都必须等待,直到发出锁定 请求的事务处理完成为止。EXCLUSIVE 是最严格的锁定模式。下面列出了其它一些锁定 模式: • ROW SHARE:允许并发访问锁定的表,但禁止在会话中锁定整个表进行独占访问。 • ROW EXCLUSIVE:与 ROW SHARE 相同,但是还禁止以 SHARE 模式锁定。更新、 插入或删除数据时会自动获取 ROW EXCLUSIVE 锁定。 • SHARE:允许并发查询,但禁止更新锁定的表。需要有(并且会自动请求)SHARE 锁定才能创建表的索引。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-22 数据并发处理(续) • SHARE ROW EXCLUSIVE:用于查询整个表,允许其他人查询表中的行,但禁止其 他人在 SHARE 模式下锁定表或更新行。 • EXCLUSIVE:允许查询锁定表,但禁止对锁定表执行任何其它活动。需要有 EXCLUSIVE 锁定才能删除表。 与任何关于锁定的请求一样,手动锁定语句会一直等待,直到已经有锁定(或先前请求了 锁定)的会话释放锁定为止。LOCK 命令会接受用于控制等待行为的特殊参数 NOWAIT。 如果指定表已经被另一会话锁定,NOWAIT 会立即将控制权还给您: SQL> LOCK TABLE hr.employees IN SHARE MODE NOWAIT; LOCK TABLE hr.employees IN SHARE MODE NOWAIT * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified 通常不必手动锁定对象。自动锁定机制提供大多数应用程序所需的数据并发处理能力。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-23 DML 锁定 事务处理 1 SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id= 107; 1 row updated. 事务处 理 2 SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id= 106; 1 row updated. 每个 DML 事务处理必须 获取两个锁定: • 针对正在更新的一行或多行的 EX CLUSIVE 行锁定 • 针对包含这些 行的表的 ROW EXC LUSIVE 表级锁定 版权所有 © 2006,Oracle。保留所有权利。 DML 锁定 每个 DML 事务处理会获取两个锁定: • 针对正在更新的一行或多行的 EXCLUSIVE 行锁定 • 针对要更新的表的 ROW EXCLUSIVE 表级锁定。这可避免在进行更改时由另一会话 锁定整个表(可能会删除或截断表)。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-24 入队机制 入队机制用于跟踪: • 等待锁定的会话 • 请求的锁定模式 • 请求锁定的会话的顺 序 版权所有 © 2006,Oracle。保留所有权利。 入队机制 锁定请求会自动排队。只要持有某个锁定的事务处理一完成,行中的下一个会话就接收该 锁定。 入队机制会跟踪请求锁定的顺序及请求的锁定模式。 已经持有锁定的会话可请求转换 锁定,而不必排到队列末尾。例如,假定某个会话对表 持有 SHARE 锁定。该会话可以请求将 SHARE 锁定转换为 EXCLUSIVE 锁定。只要没有其 它会话已经对表持有 EXCLUSIVE 或 SHARE 锁定,持有 SHARE 锁定的会话就会被授予 EXCLUSIVE 锁定,不必重新在队列中等待。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-25 事务处理 1 UPDATE employees SET 锁定冲突 时间 事务处 理 2 9:00:00 UPDATE employees SET salary=salary+100 WHERE salary=salary+100 WHERE employee_id=100; 1 row updated. UPDATE employees SET COMMISION_PCT=2 WHERE employee_id=101; 会话因锁定冲突而等待入队 会话仍在等待! 1 row updated. 会话继续进行 9:00:05 16:30:00 16:30:01 employee_id=101; 1 row updated. SELECT sum(salary) FROM employees; SUM(SALARY) ----------- 692634 在最后 7.5 个小时里进行了许多 选择、插入、更新和删除操作, 但未提交,也未回退! commit; 版权所有 © 2006,Oracle。保留所有权利。 锁定冲突 锁定冲突经常发生,但通常可随着时间流逝和通过入队机制而解决。极少数情况下,锁定 冲突可能需要管理员干预。如幻灯片所示,事务处理 2 在 9:00:00 获取了对某一行的锁定 且忘记了提交,从而留下了锁定。事务处理 1 在 9:00:05 尝试更新整个表,因此需要锁定 所有行。但事务处理 2 会阻塞事务处理 1,直到事务处理 2 在 16:30:01 提交为止。 这种情况下,用户要尝试执行事务处理 1,就一定要与管理员联系以求得帮助,DBA 必 须检测冲突并解决冲突。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-26 锁定冲突的可能原因 • • • 未提交更改 长时间运行事务处理 不必要的高锁定级别 版权所有 © 2006,Oracle。保留所有权利。 锁定冲突的可能原因 锁定冲突的最常见原因是未提交更改,但还有一些其它可能原因: • 长时间运行事务处理:许多应用程序使用批处理来执行批量更新。这些批作业通常 会安排在没有用户活动或者用户活动少时执行,但是,有些情况下,批作业在用户 活动少的期间内没有完成或要占用过长时间来运行。同时执行事务处理和批处理时 通常会发生锁定冲突。 • 不必要的高锁定级别:并不是所有数据库都支持行级锁定(Oracle 在 1988 年的发行 版 6 中添加了对行级锁定的支持)。某些数据库仍然在页级或表级上进行锁定。当 开发人员编写要在许多不同数据库上运行的应用程序时,他们会人为地使用高锁定 级别来编写应用程序,以使 Oracle 数据库与功能较少的数据库系统的操作方式相同。 如果开发人员不熟悉 Oracle,有时也会以高于 Oracle 数据库要求的锁定级别编写代 码,其实这是不必要的。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-27 检测锁定冲突 在性能页选择 “阻塞会话” 。 单击“Session ID(会话 ID)”链接 ,查看关于锁定会话的 信息,包括实际 SQL 语 句。 版权所有 © 2006,Oracle。保留所有权利。 检测锁定冲突 使用 Enterprise Manager 中的“阻塞会话”页可找出锁定冲突。有冲突的锁定请求以分层 布局的形式显示,其中持有锁定的会话位于顶部,下面是排队请求锁定的所有会话。 对于冲突中涉及的每个会话,会显示用户名、会话 ID 和会话已等待的秒数。选择会话 ID 可查看会话当前正在执行或请求的实际 SQL 语句。 自动数据库诊断监控程序 (ADDM) 还会自动检测锁定冲突,并且会就无效的锁定趋势提 出建议。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-28 解决锁定冲突 为了解决锁定冲突,您应该: • 提交或回退持有锁定 的会话 • 终止持有锁定的会话 (作为最后的手段) 版权所有 © 2006,Oracle。保留所有权利。 解决锁定冲突 要解决锁定冲突,持有锁定的会话必须释放锁定。让会话释放锁定的最好方式是与用户联 系,要求用户完成事务处理。 紧急情况下,管理员可以通过单击“Kill Session(终止会话)”按钮来终止持有锁定的会 话。请记住,终止会话后,当前事务处理中的所有工作都会丢失(回退)。会话终止后用 户必须再次登录,然后重做终止会话后上次提交以来所做的所有工作。 如果用户的会话已终止,用户下次尝试发出 SQL 语句时会收到以下错误: ORA-03135: connection lost contact Oracle Database 10g:数据库管理 - 课堂练习 I 8-29 使用 SQL 解决锁定冲突 SQL 语句可用来确定阻塞会话并终止会话。 SQL> select sid, serial#, username 1 from v$session where sid in (select blocking_session from v$session) 结果: 2 SQL> alter system kill session '144,8982' immediate; 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL 解决锁定冲突 与在 Enterprise Manager 中执行的大多数其它任务一样,会话操作也可以通过发出 SQL 语 句来完成。v$session 表包含所有连接会话的明细。blocking_session 是阻塞会话 的会话 ID。所以,如果查询 SID 和 SERIAL#(其中 SID 与阻塞会话 ID 相匹配),则提 供执行 kill session 操作所需的信息。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-30 死锁 事务处理 1 事务处理 2 UPDATE employees SET salary = salary x 1.1 WHERE employee_id = 1000; UPDATE employees SET salary = salary x 1.1 WHERE employee_id = 2000; ORA-00060: Deadlock detected while waiting for resource 9:00 9:15 9:16 UPDATE employees SET manager = 1342 WHERE employee_id = 2000; UPDATE employees SET manager = 1342 WHERE employee_id = 1000; 版权所有 © 2006,Oracle。保留所有权利。 死锁 死锁是锁定冲突的特殊示例。两个或多个会话等待相互锁定的数据时,就会发生死锁。因 为每个会话都在等待另一个会话释放锁定,所以任何一个会话都不能完成事务处理,也就 不能解决冲突。 Oracle 数据库会自动检测死锁并终止发生错误的语句。更正这种错误的适当做法是执行提 交或回退,这样做后可在一个会话中释放所有其它会话的锁定,以便其它会话可继续完成 其事务处理。 在幻灯片示例中,事务处理 1 必须提交或回退,才能更正检测到的死锁错误。如果执行提 交,则需要重新提交第二次更新才能完成事务处理。如果执行回退,则必须同时重新提交 这两个语句才能完成事务处理。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-31 小结 在本课中,应 该已经学会如何: • 通过使用 SQL 管理数 据 • 识别和管理 PL/SQL 对象 • 描述触发器和触发事 件 • 监视和解决锁定冲突 版权所有 © 2006,Oracle。保留所有权利。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-32 练习概览:管理数据和并发处理 本练习包含以 下主题: • 找出锁定冲突 • 解决锁定冲突 版权所有 © 2006,Oracle。保留所有权利。 Oracle Database 10g:数据库管理 - 课堂练习 I 8-33 管理还原数据 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后, 应能完成以下工作: • 说明 DML 和还原数据生成 • 监视和管理还原数据 • 描述还原数据和重做 数据之间的不同 • 配置还原保留期 • 确保还原保留期 • 使用还原指导 版权所有 © 2006,Oracle。保留所有权利。 Oracle Database 10g:数据库管理 - 课堂练习 I 9- 2 数据操纵 • • 数据操纵语言 (DML) 由以下 SQL 语句组成: – INSERT – UPDATE – DELETE – MERGE DML 始 终作为事务处理的一部 分执行,它可以: – 回退,使用 ROLLBACK 命令 – 提交,使用 COMMIT 命令 版权所有 © 2006,Oracle。保留所有权利。 数据操纵 INSERT UPDATE DELETE MERGE 处理或修改数据时使用 DML 类的 SQL 语句: 、 、 和 。 这些语句作为事务处理的一部分而执行,事务处理以第一个成功的 DML 语句作为开始, 以 COMMIT 或 ROLLBACK 命令作为结束。事务处理不是完全提交,就是完全回退。 如果进程或系统失败,也可能发生回退。 注:使用 MERGE 命令可合并执行插入与更新,可将一个表中的数据合并到另一个表中。 在“管理数据和并发处理”一课中对此进行了论述。 Oracle Database 10g:数据库管理 - 课堂练习 I 9- 3 还原数据 还原数据是: • 原始的、修改之前的数据副本 • 为更改数据的每个事 务处理而捕获 • 至少保留到事务处理 结束 • 用于支持: – 回退操作 – 读取一致性和闪回查询 – 从失败事务处理中恢复 用户 版权所有 © 2006,Oracle。保留所有权利。 还原数据 当某个进程更改了数据库中的数据时,Oracle 数据库会保存旧值(还原数据)。按数据修 改前的原样存储数据。如果捕获了还原数据,则可以回退未提交的数据。还原数据还用于 支持读取一致性和闪回查询。 在读取一致性查询中,提供与查询开始时的数据一致的结果。如果要成功完成读取一致性 查询,原始信息必须仍作为还原信息存在。只要保留了还原信息,Oracle 数据库就能通过 重建数据来满足读取一致性查询的要求。 闪回查询是有目的地查询,可用来查找过去某个时间存在的某个版本的数据。只要过去那 个时间的还原信息仍存在,闪回查询就能成功完成。 还原数据也可用于从失败的事务处理中进行恢复。如果在用户决定提交或回退事务处理之 前用户会话异常结束(可能因为网络错误或客户机故障),就会导致事务处理失败。实例 崩溃也可能导致事务处理失败。 Oracle Database 10g:数据库管理 - 课堂练习 I 9- 4 还原数据(续) 如果事务处理失败,最安全的行为是选择让 Oracle 数据库撤销用户做出的所有更改,从 而恢复原始数据。 所有事务处理的还原信息都通过以下方法至少保留到事务处理结束: • 用户还原事务处理(回退) • 用户结束事务处理(提交) • 用户会话异常终止(回退) • 用户会话异常终止退出(提交) 保留的还原数据量及其保留时间取决于数据库活动量以及数据库配置。 Oracle Database 10g:数据库管理 - 课堂练习 I 9- 5 事务处理和还原数据 旧值 还原段 表 新值 • • 每个事务处理只分配 给一个还原段 一个还原段可以同时服务多个事务处理 更新事务处理 版权所有 © 2006,Oracle。保留所有权利。 事务处理和还原数据 事务处理开始时已分配到还原段。在事务处理的整个过程中,更改数据时,原始(更改之 前)值会被复制到还原段。通过选中 v$transaction 动态性能视图,可以查看哪些事 务处理被分配到哪些还原段。 还原段是为了支持事务处理,根据需要由实例自动创建的专用段。像所有段一样,还原段 由区组成,区又由数据块组成。还原段根据需要可自动增长和收缩,它充当一个用于所分 配的事务处理的循环存储缓冲区。 事务处理会填充其还原段中的区,直至完成了事务处理或占用了所有空间为止。如果填充 完区之后还需要更多的空间,事务处理则获取段中下一个区的空间。占用了所有区之后, 事务处理会自动转回到第一个区或请求还原段分配的新区。 注:并行 DML 操作实际上可导致事务处理使用多个还原段。要了解关于并行 DML 执行 的详细信息,请参阅《Oracle 数据库管理员指南 10g》。 Oracle Database 10g:数据库管理 - 课堂练习 I 9- 6 存储还原信息 还原信息存储在还原段中,还原段又存储在还原表空间中。 还原表空间: • 仅用于还原段 • 包含特殊的恢复注意 事项 • 只能与单个实例相关 联 • 要求在任意指定时间 ,一个给定的实例只 能有一个是 当前可写还原表空间 版权所有 © 2006,Oracle。保留所有权利。 存储还原信息 还原段只存在于特定形式的表空间中,这种形式的表空间称为还原表空间。尽管数据库可 能有许多还原表空间,但是一次只能有一个还原表空间被指定为可写入还原数据的当前还 原表空间。 还原段始终由 SYS 拥有。由于还原段充当循环缓冲区,因此每个段最少包含两个区。默 认的最大区数取决于数据库块大小,但是此值非常大(块大小为 8-KB 时区数为 32,765)。 还原表空间是永久的、本地管理的表空间(有自动区分配)。管理这些还原表空间像管理 其它执行异常恢复的表空间一样。由于需要有还原数据才能从失败的事务处理(如实例崩 溃时可能会导致事务处理失败)中进行恢复,所以还原表空间只在实例处于 MOUNT 状态 时可恢复。还原表空间的恢复注意事项在“执行数据库恢复”一课中会介绍。 Oracle Database 10g:数据库管理 - 课堂练习 I 9- 7 还原数据与重做数据 记录 用于 存储于 避免 还原 如何还原更改 回退、读取一致性 还原段 在多用户系统中读取不一致 重做 如何重新生成更改 向前滚动数据库更改 重做日志文件 数据丢失 版权所有 © 2006,Oracle。保留所有权利。 还原数据与重做数据 还原数据和重做数据初看很相似,但是二者的作用却截然不同。在需要还原更改的情况下 就需要用到还原数据,而且为了保持读取一致性和执行回退,也需要还原数据。在因某种 原因而丢失了更改数据时,如果需要再次执行更改,就需要用到重做数据。 提交过程需要验证在事务处理中所做的更改是否已写入重做日志文件。这个重做日志文件 会永久保存在磁盘上,而不是保存在内存中。此外,一般情况下会多路复用它。因此,磁 盘上有重做数据的多个副本。 即使更改尚未写入实际存储表块的数据文件,只要确保更改已写入重做日志文件就够了。 Oracle Database 10g:数据库管理 - 课堂练习 I 9- 8 监视还原 还原通常不需 要管理。要监视的范围 包括: • 还原表空间中的空闲 空间 • “快照太旧”错误 DBA 版权所有 © 2006,Oracle。保留所有权利。 监视还原 大多数时间,还原由实例自动管理,很少需要数据库管理员 (DBA) 干预。可能需要管理 员介入的一些情况包括: • 还原空间不足 • 用户会收到 ORA-01555 snapshot too old 错误消息 还原信息始终保留直至事务处理结束。这意味着如果删除或更新了大量的数据(插入操作 只占用极少的还原空间,因为插入数据的原始图像是空值)而没有提交,那么还原表空间 必须同样大才能包含原始数据。假定一种情况,其中使用以下命令删除了 50-GB 表的所 有行: SQL> DELETE FROM reallybigtable; 还原表空间需要留出 50 GB 原始信息的空间,以备发出此语句的用户改变主意而要回退 更改。当还原表空间用完了用于还原数据的空间时,用户会收到如下错误消息: ORA-01650: unable to extend rollback segment 通过预防性监视,可在问题影响用户之前检测到还原表空间中的空间问题。 Oracle Database 10g:数据库管理 - 课堂练习 I 9- 9 监视还原(续) 管理员在还原信息时还可能遇到另一个问题,也就是当查询需要访问还原信息时还原信息 已被覆盖的问题。这可能发生在长时间运行查询或闪回查询中。当查询需要过去一段时间 的数据的“快照”,而且重建快照需要的还原数据不再存在时,查询会返回以下错误: ORA-01555: snapshot too old 之所以会发生这种情况,是因为 Oracle 数据库向用户展示的数据视图与查询开始运行时 的数据视图一致。如果查询的表有未提交的更改,Oracle 数据库会通过读取还原数据来获 取数据的提交版本。这就是读取一致性。如果查询运行时间太长,其间的那些修改确已提 交,随后其还原数据已释放且被覆盖,那么查询运行很长时间后看见的数据视图不再与查 询刚开始运行时看见的数据视图一致。因此,还原保留期应配置为满足最长时间运行查询 的需要。 Oracle Database 10g:数据库管理 - 课堂练习 I 9-10 管理还原 管理还原应包 括防止: • 在还原表空间中出现 空间错误: – 正确调整还原表空间的大小。 – 确保定期提交大型事务处理。 • “快照太旧”错误: – 配置适当的还原保留期间隔。 – 正确调整还原表空间的大小。 – 考虑确保还原保留期。 使用自动还原管理: UNDO_MANAGEMENT=AUTO UNDO_TABLESPACE=UNDOTBS1 DBA 版权所有 © 2006,Oracle。保留所有权利。 管理还原 建议使用自动还原管理,通过将 UNDO_MANAGEMENT 初始化参数设置为 AUTO 可以进行 配置。手动还原管理支持向后兼容 Oracle8i 及较早版本,但是需要更多的 DBA 交互。 使用自动还原管理时,通过使用 UNDO_TABLESPACE 初始化参数控制实例使用哪个还原 表空间,DBA 可在表空间级管理还原。选择还原表空间之后,管理员只需要考虑提供足 够的空间并配置还原保留期间隔。 使用手动管理,DBA 还必须考虑: • 调整段大小,包括最大区和调整区大小 • 标识和清除块事务处理 • 创建足够的回退段以处理事务处理(手动模式下,还原段也称为回退段) • 选择表空间以包含回退段(还原表空间仅用于自动还原管理) Oracle Database 10g:数据库管理 - 课堂练习 I 9-11 配置还原保留期 UNDO_RETENTION 指定要保留的已提交 还原信息量(秒数) 。 必须设置这个 参数的最合适情况是: • 还原表空间启用了 AUTOEXTEND 选项 • 要设置 LOB 的还原保留 期 • 要确保保留期 DBA 版权所有 © 2006,Oracle。保留所有权利。 配置还原保留期 UNDO_RETENTION 指定还原保留期的低阈值(秒数)。若是 AUTOEXTEND 还原表空 间,系统至少按这个参数指定的时间保留还原数据,而且还为了满足查询的还原要求, 自动调整还原保留期。若是固定大小的还原表空间,系统根据还原表空间大小和使用历 史记录,自动调整可能存在的最大还原保留期;系统会忽略 UNDO_RETENTION 直至启 用保留期确保 (retention guarantee)。因此,进行自动还原管理时,列出的三种情况都使 用了 UNDO_RETENTION 设置。这三种以外的情况,则忽略此参数。 还原信息分为三类: • 未提交的还原信息:支持当前运行的事务处理,如果用户要回退或事务处理失败 时,需要用到这类信息。绝对不会覆盖未提交的还原信息。 • 提交的还原信息:不再需要用来支持运行的事务处理,但是为了符合还原保留期 间隔,仍然要用到这类信息。这也称为“未过期”还原信息。当不是因为缺少空 间而导致活动事务处理失败时,就会保留提交的还原信息。 • 过期的还原信息:不再需要用来支持运行的事务处理。活动事务处理需要空间时 会覆盖过期的还原信息。 Oracle Database 10g:数据库管理 - 课堂练习 I 9-12 确保还原保留期 确保:15 分钟 还原数据 始终满足 SELECT 语句运行 15 分钟 或更少时间。 生成的还原数据比 可用空间多时事务 处理会失败。 版权所有 © 2006,Oracle。保留所有权利。 确保还原保留期 默认还原行为是覆盖提交的、尚未过期的事务处理,而不是因为缺少还原空间而导致活动 事务处理失败。 此行为在确保了保留期后会有所改变。有了确保的保留期,即使会导致事务处理失败,仍 然会强制执行还原保留期设置。 RETENTION GUARANTEE 是表空间属性而不是初始化参数。此属性只可使用 SQL 命令 行语句来更改。通过更改还原表空间来确保保留期的语法是: SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; 要将有确保保留期的还原表空间返回到其常规设置,请使用以下命令: SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE; 保留期确保仅适用于还原表空间。尝试对非还原表空间设置保留期会产生以下错误: SQL> ALTER TABLESPACE example RETENTION GUARANTEE; ERROR at line 1: ORA-30044: 'Retention' can only specified for undo tablespace Oracle Database 10g:数据库管理 - 课堂练习 I 9-13 调整还原表空间的大小 当前表空间大小 还原占用率 版权所有 © 2006,Oracle。保留所有权利。 调整还原表空间的大小 必须调整还原表空间的大小,以便还原表空间包含所有事务处理的原始信息。单击 Enterprise Manager 的“Administration(管理)”页上的“Undo Management(还原管理)” 链接,显示系统还原概览,包括当前设置、每分钟还原占用率及指定时期内最长时间运行 查询的时间长度。 属于还原表空间的数据文件在用完空闲空间时可自动扩展。Oracle Corporation 建议与还原 表空间相关联的数据文件不应启用自动扩展,这与对其它表空间的建议不同。第一次确定 还原空间要求时,可能要启用数据文件的自动扩展,但在正确调整表空间的大小之后,必 须禁用自动扩展。通过禁用还原表空间数据文件的自动扩展,可避免单个用户因忽略提交 事务处理而无意占用大量的磁盘空间。 Oracle Database 10g:数据库管理 - 课堂练习 I 9-14 使用还原指导 版权所有 © 2006,Oracle。保留所有权利。 使用还原指导 通过“Undo Management(还原管理)”属性页可访问还原指导。通过它可估计满足指定 的还原保留期所需要的还原表空间大小。 输入期望的保留期,还原指导分析区域会显示支持保留期所需要的表空间大小。还可以通 过单击图上的一点来查看支持选定期间所需要的表空间大小。 选择了还原保留期之后,单击“OK(确定)”可实现新保留期。 Oracle Database 10g:数据库管理 - 课堂练习 I 9-15 小结 在本课中,应 该已经学会如何: • 说明 DML 和还原数据生成 • 监视和管理还原 • 描述还原数据和重做 数据之间的不同 • 配置还原保留期 • 确保还原保留期 • 使用还原指导 版权所有 © 2006,Oracle。保留所有权利。 Oracle Database 10g:数据库管理 - 课堂练习 I 9-16 练习概览:管理还原段 本练习包含以 下主题: • 计算用于支持 48 小时 保留期间隔的还原表空 间大小 • 修改用于支持 48 小时保留期间隔的还原表空间 版权所有 © 2006,Oracle。保留所有权利。 Oracle Database 10g:数据库管理 - 课堂练习 I 9-17 实施 Oracle 数据库安全性 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后, 应能完成以下工作: • 说明由 DBA 负责的安全工作 • 应用最少权限原则 • 启用标准数据库审计 • 指定审计选项 • 复查审计信息 • 维护审计线索 版权所有 © 2006,Oracle。保留所有权利。 课程目标 从本课开始,将学习关于 Oracle 安全性的知识。补充信息包括在以下文档中: • 《Oracle 数据库概念 10g 发行版 2 (10.2)》 • 《Oracle 数据库管理员指南 10g 发行版 2 (10.2)》 • 《Oracle 数据库安全指南 10g 发行版 2 (10.2)》 在下列课程中会提供其它的培训: • Oracle Database 10g:数据库管理 - 课堂练习Ⅱ(D17092GC30) • Oracle Database 10g:安全性 (D17499GC10) Oracle Database 10g:数据库管理 - 课堂练习 I 10-2 • 法律: 业界安全性要求 > 要求 最少权限 审计 基于值 FGA DBA 安全性更新 – – – – Sarbanes-Oxley 法案 (SOX) 安全信息流通和责任法案 (HIPAA) 加利福尼亚州违约法 英国数据保护法案 • 审计 版权所有 © 2006,Oracle。保留所有权利。 业界安全性要求 到目前为止,有关安全性的要求一直是业界关注的一个问题。过去,如果不涉及处理政府 或军事方面的数据,也就不涉及什么法律要求。但是,这种情况在很短的时间内发生了变 化。目前,为了强制保护数据的隐私性和准确性,已经通过了各种各样的法律。伴随这些 法律而来的,还要求审计相应的安全措施。 法律:本课中列出的每一条法律都有一些具体的要求。这里列出的只是全球已通过和要通 过的许多法律中的一些代表。勿庸置疑,各地的安全法会有所不同。 • Sarbanes-Oxley 法案 (SOX) 要求上市公司加强内部单据的控制能力,以避免由于个 人的欺骗性行为而泄漏公司的财务状况或影响公司财务报表的准确性。首席执行官 和首席财务官必须证实公司有足够的内部控制能力,还必须证实财务报表的准确性。 如果提供有欺骗性的报表,他们会被罚款甚至入狱。SOX 中对用来生成报表的信息 和用来确保财务资料完整性的内部控制能力等有详细的要求。 • 安全信息流通和责任法案 (HIPAA) 用于防止个人可识别安全信息被公之于众或被误 用。信息掌握者必须提供访问此数据的所有人的审计线索。 • 英国数据保护法案通过限制对个人可识别数据的访问保护个人隐私。这个法案包含 8 个要点,其中一个要点是要求保证数据的安全性。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-3 业界安全性要求(续) • 其它法律: - 家庭教育权利与隐私法案 (FERPA) 涉及由学校掌握的健康和个人信息。 - 加利福尼亚州违约法要求掌握信用卡、驾照、身份证号之类的大量个人身份信 息 (PII) 的公司必须保护这些信息。如果泄漏了信息,公司必须通知涉及的所有 个人。CA-SB-1386 和 CA-AB-1950 这两条法律适用于掌握 PII 的公司。 - 联邦信息安全管理法案 (FISMA) 是正在创建的安全指南和标准(通过美国国家 标准局 (NIST) 管理的联邦信息处理标准 (FIPS) 文档来创建)。这些标准适用于 处理美国政府信息的机构。 审计:许多法律中都包括要求定期审计安全计划(内部控制能力)的条款。SOX 的要求 不太明确,而且以公司负责人的解释为准。实施细节可能会有很大的出入,这取决于公司 负责人要求的细节级别。虽然 SOX 的要求不太明确,但是处罚很严厉,所以一定要保护 公司的信息安全性。实施安全措施的成本与这种风险之间一定要取得平衡。没有人能保证 100% 的安全性。最好的解决办案是在业界达成共识。如果您履行了议定的最低安全方案, 另外,业已尽职尽责,就不会受到最不利的法律处罚。可提供业界标准方案的一些可靠资 源包括:美国系统管理和网络安全审计协会 (SANS)、卡内基梅隆大学计算机系统防御系 的 CERT/CC 以及 ISO-17799 认证标准。请分别访问以下地址: http://www.sans.org/index.php http://www.cert.org/nav/index.html http://www.iso17799software.com/ ISO-17799 认证标准是关于安全方案的国际标准。其中包括最佳方案、证书和风险评估。 它涵盖了各种各样的问题并包括一些预先编写的策略。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-4 责任分离 • • • • • 必须信任具有 DBA 权限的用 户。请考虑以下两个因 素: – 滥用信任 – 审计线索保护受信任的职位 必须共同分担 DBA 责任 一定不能共享帐户 DBA 和系 统管理员必须由不同人 员担任 分离操作员与 DBA 的责任 版权所有 © 2006,Oracle。保留所有权利。 责任分离 以下是满足责任分离的主要要求。 必须信任 DBA:很难限制某个 DBA 去做什么。为了完成工作,DBA 需要很高的权限。 DBA 是受信任的职位,因此必须接受全面检查。即使是受信任的 DBA 也必须承担责任。 请考虑以下两个因素: • 滥用信任:DBA 可能会误用 DBA_USERS 视图中的加密密码。 • 审计线索保护受信任的职位:谨慎实施审计且遵守准则之后,审计线索会指出特定 人员没有违反规程,也没有破坏性行为。如果有恶意用户试图让人怀疑受信任的用 户,设计适当的审计线索会捕获该行为。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-5 数据库安全性 安全系统可确保所包含数据的机密性。安全性包括以下几个 方面: • 限制对数据和服务的 访问 • 验证用户 • 监视可疑活动 版权所有 © 2006,Oracle。保留所有权利。 数据库安全性 Oracle Database 10g 为业界提供了最佳的安全系统框架。但是,要让这个框架起作用,数 据库管理员必须遵循最佳方案并持续监视数据库活动。 限制对数据和服务的访问 并不是所有用户对所有数据都具有访问权。根据数据库上存储的内容,可按业务需要、客 户期望以及日益增加的法律限制条件来管理有限制的访问权限。必须保护信用卡信息、健 康保健数据、身份识别信息等,使之免受未授权访问。Oracle 数据库通过提供细粒度的授 权控制来限制数据库访问。限制访问必须包括应用最少权限原则。 验证用户 为了强制对敏感数据实施访问控制,系统必须首先知道是谁尝试访问数据。如果验证信息 泄漏,则会导致所有其它安全性预防措施变得无用。最基本的用户验证方式是要求用户提 供知道的验证信息,如口令。如果可以保证口令遵循简单规则,就可极大地增强系统的安 全性。比较严格的验证方法包括要求用户提供掌握的某些验证信息,如令牌或公共密钥基 础结构 (PKI) 证书。更严格的验证方法是,通过诸如指纹、虹膜、骨组织模式等唯一生物 学特征来识别用户。Oracle 数据库支持通过高级安全选项来使用高级验证技术,如基于令 牌、生物学和证书的身份识别技术。为了防止试图泄漏验证信息,必须锁定当前未使用的 用户帐户。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-6 数据库安全性(续) 监视可疑活动 即使经过授权和验证的用户有时也会泄漏系统信息。为了查到信息失窃的原因,第一步就 是要找出不寻常的数据库活动,如某个雇员突然开始查询大量的信用卡信息、研究结果或 其它敏感信息。为了跟踪用户活动和确定可疑活动的变化趋向,Oracle 数据库提供了很多 审计工具。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-7 • • 最少权限原则 只在计算机上安装所 需软件 只在计算机上激活所 需服务 > 要求 最少权限 审计 基于值 FGA DBA 安全性更新 • • • • 只允许需要访问的用 户访问操作系统和数 据库 限制对 root 或管理员帐户的访问 限制对 SYSDBA 和 SYSOPER 帐户的 访问 只允许用户访问完成 工作所需的数据库对象 版权所有 © 2006,Oracle。保留所有权利。 最少权限原则 首先在最低级别应用最少权限原则,继而在每一级别应用此原则。总是会出现一些新的、 不能预料的安全问题。通过应用这个原则,可降低发生问题的可能性,从而避免可能受到 的损害。 • 只在计算机上安装所需软件:通过减少安装的软件包数目,可减少升级维护工作, 降低出现安全性漏洞的可能性,减少软件冲突。 • 只在计算机上激活所需服务:服务越少意味着打开的端口越少,因而受到的攻击也 就越少。 • 只允许需要访问的用户访问操作系统 (OS) 和数据库:用户越少意味着口令和帐户越 少。这会降低公开帐户或帐户失效的可能性。帐户越少,管理员越容易保持帐户处 于当前状态。 • 限制对 root 或管理员帐户的访问:必须小心保护和审计管理员帐户,而且决不能共 享管理员帐户。 • 限制对 SYSDBA 和 SYSOPER 帐户的访问:需要访问这两个角色的用户,必须各自 有自己的帐户并且必须经过审计。 • 只允许用户访问完成工作所需的数据库对象:如果用户访问的对象和服务超过需要 访问的范围,他们就有机会进行不当行为。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-8 应用最少权限原则 • • • • • 保护数据字典: O7_DICTIONARY_ACCESSIBILITY=FALSE 从 PUBLIC 撤消不必要的 权限: REVOKE EXECUTE ON UTL_SMTP, UTL_TCP, UTL_HTTP, UTL_FILE FROM PUBLIC; 限制用户可访问的目 录 限制具有管理权限的 用户 限制远程数据库验证 : REMOTE_OS_AUTHENT=FALSE 版权所有 © 2006,Oracle。保留所有权利。 应用最少权限原则 应用最少权限原则意味着一定只能授予用户有效完成任务所需的权限。这会降低用户无意 或恶意修改或查看他们无权修改或查看数据的机率。 保护数据字典:默认情况下,O7_DICTIONARY_ACCESSIBILITY 参数设置为 FALSE。 如果没有很好的理由,一定不要更改这个设置,因为这会阻止具有 ANY TABLE 系统权限 的用户访问数据字典的基表。这个参数设置还确保了 SYS 用户只能以 SYSDBA 身份登录。 从 PUBLIC 撤销不必要的权限:下列程序包对于需要这些程序包的应用程序非常有用, 但需要正确的配置才能安全地使用这些程序包。请从 PUBLIC 中撤销 EXECUTE 权限,然 后在下列程序包需要时将 EXECUTE 权限授予角色:UTL_SMTP、UTL_TCP、UTL_HTTP 和 UTL_FILE。 有一些作用比较大的程序包可能被误用,这些程序包包括: • UTL_SMTP:允许将数据库用作简单邮件传输协议 (SMTP) 邮件服务器来发送任意电 子邮件消息。如果将这个程序包授予 PUBLIC,可能会出现未经授权就交换电子邮 件消息的情况。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-9 应用最少权限原则(续) • UTL_TCP:允许数据库服务器与任何正在接收或等待的网络服务建立外出网络连接。 因此,在数据库服务器与任何正在等待的网络服务之间会发送任意数据。 • UTL_HTTP:允许数据库服务器通过 HTTP 请求并检索数据。如果将这个程序包授 予 PUBLIC,可能会允许通过 HTML 形式将数据发送到恶意 Web 站点。 • UTL_FILE:如果配置不当,则可能允许访问主机操作系统上的任何文本文件。即 使配置正确,这个程序包也不会区别其调用应用程序;因此对 UTL_FILE 具有访问 权的一个应用程序可能会将任意数据写到由另一个应用程序写到的位置。 限制对操作系统目录的访问:通过数据库中的 DIRECTORY 对象可使 DBA 将目录映射到 操作系统路径,从而将这些目录的权限授予各个用户。 限制具有管理权限的用户:请不要为数据库用户提供不必要的权限。一定不能将 DBA 角 色授予非管理员。要实施最少权限,请限制授予下列类型的权限: • 授予系统和对象的权限 • 连接到数据库的 SYS 权限,如 SYSDBA 和 SYSOPER • 其它 DBA 类型的权限,如 DROP ANY TABLE 限制远程数据库验证:默认情况下,REMOTE_OS_AUTHENT 参数设置为 FALSE。一定不 要更改这个设置,除非信任所有的客户机能适当地验证用户。 在远程验证过程中: • 数据库用户通过外部方式来验证 • 远程系统验证用户 • 用户登录数据库,不必接受另外的验证 Oracle Database 10g:数据库管理 - 课堂练习 I 10-10 监视可疑活动 监视或审计是 安全过程的一部分。请 复查下列 各项: • 强制性审计 • 标准数据库审计 • 基于值审计 • 细粒度审计 (FGA) • DBA 审计 > 要求 最少权限 审计 基于值 FGA DBA 安全性更新 版权所有 © 2006,Oracle。保留所有权利。 监视可疑活动 审计意味着捕获并存储关于系统上所发生情况的信息,这会增加系统必须执行的工作量。 审计必须有重点,所以只捕获有意义的事件。如果审计重点设置适当,则会最大程度地减 少对系统性能的影响。如果审计重点设置不当,则会对系统性能产生明显的影响。 • 强制性审计:不管其它审计选项或参数如何设置,所有 Oracle 数据库都会审计特定 的操作。由于数据库需要记录诸如系统启动和关闭等数据库活动,所以存在强制性 审计日志。 • 标准数据库审计:这是通过使用 AUDIT_TRAIL 初始化参数在系统级别设置的。启 用审计后,可选择要审计的对象和权限。 • 基于值审计:这种审计扩展了标准数据库审计的功能,不仅会捕获已发生的审计事 件,还会捕获插入、更新或删除的实际值。基于值审计是通过数据库触发器实施的。 • 细粒度审计 (FGA):FGA 扩展了标准数据库审计的功能,这种审计可捕获已发出的 实际 SQL 语句,而不仅仅是发生了事件的 SQL 语句。 • DBA 审计:将 DBA 与审计者或安全管理员之间的审计责任分离开,审计者或安全 管理员在操作系统审计线索中负责监视 DBA 的活动。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-11 标准数据库审计 DBA 2 1 启用数据库审计 指定审计选项 参数文件 数据库 审计选项 用户 执行命令 服务器进程 生成审计线索 3 复查审计信息 4 维护审计线索 审计线索 操作系统或 XML 审计 线索 版权所有 © 2006,Oracle。保留所有权利。 标准数据库审计 启用数据库审计并指定审计选项(登录事件、行使的系统和对象权限或使用的 SQL 语句) 后,数据库开始收集审计信息。 如果 AUDIT_TRAIL 设置为 OS,审计记录存储在操作系统的审计系统中。在 Windows 环 境下,审计记录存储在事件日志中。在 UNIX 或 Linux 环境下,审计记录存储在文件中。 该文件的位置是用 AUDIT_FILE_DEST 参数指定的位置。 假定 AUDIT_TRAIL 参数设置为 DB,则可复查 DBA_AUDIT_TRAIL 视图中的审计记录, 这个视图是 SYS 方案的一部分。 如果 AUDIT_TRAIL 设置为 XML 或 XML,EXTENDED,审计记录会写入 AUDIT_FILE_DEST 参数指向的目录中的 XML 文件。使用 V$XML_AUDIT_TRAIL 视图 可查看此目录中的所有 XML 文件。 维护审计线索是一项重要的管理任务。根据设置的重点审计选项,审计线索会迅速地增长。 如果维护不当,审计线索会占用大量的空间,以至影响系统的性能。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-12 启用审计 ALTER SYSTEM SET audit_trail=“XML” SCOPE=SPFILE; 请在修改静态 初始化参数之后重新启 动数据库 版权所有 © 2006,Oracle。保留所有权利。 启用审计 必须在指定审计设置之前启用数据库审计。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-13 统一审计线索 请使用 AUDI T_TRAIL 启用数据库审 计 STATEMENTID, AUDIT_TRAIL=DB,EXTENDED ENTRYID DBA_AUDIT_TRAIL DBA_FGA_AUDIT_TRAIL EXTENDED_TIMESTAMP, PROXY_SESSIONID, GLOBAL_UID, INSTANCE_NUMBER, OS_PROCESS, TRANSACTIONID, SCN, SQL_BIND, SQL_TEXT DBA_COMMON_AUDIT_TRAIL 版权所有 © 2006,Oracle。保留所有权利。 统一审计线索 要使用数据库审计,必须先将非动态 AUDIT_TRAIL 参数设置为指向审计记录的存储位 置。这样做可启用数据库审计。 启用标准审计和细粒度审计时,Oracle Database 10g 会跟踪相同的字段。这方便您分析数 据库活动。为实现这一点,标准审计线索和细粒度审计线索包含了彼此互补的属性。 通过标准审计收集的额外信息包括: • 系统更改号 (SCN),记录对系统的每一更改。 • 用户执行的准确 SQL 文本及与 SQL 文本一起使用的绑定变量。只在初始化参数文件 中指定了 AUDIT_TRAIL=DB 时,这些列才会显示。 通过细粒度审计收集的额外信息包括: • 每个审计记录的序列号。 • 将源自一条语句的多个审计条目联系起来的语句编号。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-14 公共属性包括: • 用全球标准时间 (UTC) 表示的全球时间戳。这个字段在监视不同地理位置和不同时 区中的数据库时特别有用。 • 每个 Real Application Clusters (RAC) 实例的唯一实例编号。 • 用于将一个事务处理的审计记录组成一组的事务处理标识符。 DBA_COMMON_AUDIT_TRAIL 视图中组合了标准审计日志记录和细粒度审计日志记录。 Oracle Database 10g:数据库管理 - 课堂练习 I 10-15 Enterprise Manager 审计页 版权所有 © 2006,Oracle。保留所有权利。 Enterprise Manager 审计页 通过单击“Administration(管理)”标签,然后单击“Users & Privileges(用户和权限)” 区域中的“Audit Settings(审计设置)”链接,可访问“Database Control Home(数据库控 制主页)”中的“Audit(审计)”页。 “Audit(审计)”页上包含下列区域: • Configuration(配置):显示当前的配置参数值,包含用于编辑参数值的链接 • Audit Trails(审计线索):可轻松访问已收集的审计信息 请使用以下标签页来设置和取消设置审计选项: • Audited Privileges(审计权限):显示审计的权限 • Audited Objects(审计对象):显示审计的对象 • Audited Statements(审计语句):显示审计的语句 Oracle Database 10g:数据库管理 - 课堂练习 I 10-16
还剩102页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 6 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

nqw2202

贡献于2013-03-20

下载需要 6 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf