- 浏览: 395963 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (347)
- java基础 (58)
- ajax (10)
- s2sh (10)
- 版本控制 (4)
- 数据库 (34)
- 服务器 (4)
- 开发工具 (8)
- javascript (15)
- soockte (5)
- ext (2)
- 环境搭建 (7)
- struts2 (9)
- 找工作中的面试技巧 (2)
- 承接网站零活 (0)
- JNI+JONSE+OGNL (8)
- 性能优化 (4)
- Android开发 (5)
- xul (8)
- jquery (2)
- 线程 (3)
- jsp+jdbc (7)
- servlet (2)
- java对xml操作 (1)
- IO流的操作 (10)
- 项目开发前配置 (1)
- css (0)
- 上传、下载 (2)
- 知识探讨 (2)
- html (2)
- HQL (0)
- 工作技巧 (1)
- IT (1)
- Hibernate杂谈 (10)
- Spring杂谈 (35)
- DWR (5)
- JUnit测试 (3)
- EasyMock测试web (1)
- ibatis (6)
- maysql (5)
- C++ (0)
- 正则表达式(解剖) (1)
- 密码安全 (2)
- 上传 (1)
- socket (1)
- jni(java与c++结合) (1)
- jdk版本问题 (0)
- tomcat版本问题 (5)
- linux基本命令(初学) (7)
- linux项目发布 (1)
- 3年的经验总结 (1)
- 加解密 (2)
- 高级java阶段 (2)
- java内存分区 (1)
- 浏览器 (1)
- 职业规划 (1)
- 管理 (5)
- java语音 (1)
- SSH (1)
- jsp (3)
- extjs (1)
- uml (2)
- 加密 (1)
- web (2)
- Ant (1)
- 自述 (1)
- Linux (1)
- ssh源码解剖 (1)
- 代码优化 (1)
- 设计模式 (0)
- xml (2)
- JOSN (1)
- scala (0)
- hadoop (0)
- spark (0)
- hana (1)
- shior (1)
- java Word (6)
- java PDF (4)
- java Excel (0)
最新评论
-
高级java工程师:
ztao2333 写道谢谢。收藏下这个总结。呵呵
温习jdk和tomcat -
ztao2333:
大写的,不是大学的
温习jdk和tomcat -
ztao2333:
谢谢。收藏下这个总结。
温习jdk和tomcat -
the_small_base_:
你好,可以提供调用方法吗?需要的Jar,能发下源码吗?谢谢
java实现语音 -
高级java工程师:
文思涌动 写道楼主新年好。可否再传一遍给我,我没有收到, 不清 ...
s2sh整合
Oracle约束的启用和停用
关于Oracle的约束概念和基本操作,我已经在以前的《Constraint基础概念》、《Constraint的简单操作》两篇文章中有过比较详细的介绍了,但是对于如何停用和启用constraint没有作特别的描述,以至于在使用PLSQL中无法忽略constraint而逐步进行数据的更改,所以在这里专门记录一下关于constraint的停用和启用相关知识。
一、约束的状态
可以指定启用(ENABLE)或者停用(DISABLE)约束。如果启用约束,当在数据库中输入或者更新数据时要进行数据检查,不符合约束规则的数据不能输入数据库。如果停用约束,那么不符合规则的数据也能输入到数据库中。 另外,也能制定表中的现有数据必须符合约束(VALIDATE);相反地,如果指定为(NOVALIDATE),就不能确保现有的数据符合约束。
所以由上述的特性可知,表上的约束可能处在以下4种状态:
● ENABLE , VALIDATE
● ENABLE , NOVALIDATE
● DISABLE, VALIDATE
● DISABLE, NOVALIDATE
1、停用约束的情况
一般来说,约束都是需要始终处于启用状态的,但是在以下情况中可能需要约束停用:
● 当往表中装载大量数据时
● 当执行批操作使得表有大规模的改变时
● 到一次性导入或导出一个表时
停用都是为了提高性能,特别是在数据仓库中,这种操作的作用尤其明显。而且由于停用约束有可能造成违反约束的数据输入到表中,所以在进行完以上的动作之后,需要立即启用约束。
2、启用约束的一些问题
在停用约束时,违反约束的行可以插入到表中,这种行为被认为是对约束异常。而且如果约束处于启用非校验状态(ENABLE,NOVALIDATE),则在停用时插入的违反约束的记录仍然保留,需要更改后,约束方可出在已校验状态。
当表的约束处于启用非校验状态时,表中原本的数据可以不符合约束,但后面新增的数据必须进行校验,这对于数据仓库来说特别有用。因为基本上数据仓库都是使用增量更新,在停用约束后如果采用启用校验约束状态,则需要对大量数据进行校验,影响性能。
另外说明:校验一个已经启用的约束,在校验期间不需要任何DML锁,因此在校验期间可以保证没有引入违反约束的数据。
3、完整性约束状态:过程和效益
一般可以按照以下的顺序来使用约束,以确保最佳的效益:
① 停用状态
② 执行操作(装载、导出、导入)
③ 启用非校验状态
④ 启用状态
好处是:
● 不留锁
● 所有的约束能够同时处于启用状态
● 约束启用是并行实现的
● 允许表上进行同步操作
二、在定义中设置约束属性
在CREATE TABLE和ALTER TABLE语句中就可以设置约束的ENABLE/DISABLE、VALIDATE/NOVALIDATE。
注:默认的是ENABLE/VALIDATE的。
1、在定义中停用约束
CREATE TABLE emp( empno NUMBER(5) PRIMARY KEY DISABLE,
deptno NUMBER(2));
ALTER TABLE emp ADD PRIMARY KEY(empno) DISABLE;
2、在定义中启用约束
CREATE TABLE emp( empno NUMBER(5) PRIMARY KEY,
deptno NUMBER(2));
ALTER TABLE emp ADD PRIMARY KEY(empno);
注:有可能创建约束失败,因为原始数据与约束冲突,此时语句回滚。
3、修改或删除现有的约束
注:在使用外键参考了PRIMARY或UNIQUE键时,不能停用或删除被参考约束
停用:
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;
ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX,
DISABLE UNIQUE (dname, loc) KEEP INDEX;
启用非校验:
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;
ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY,
ENABLE NOVALIDATE UNIQUE (dname, loc);
启用校验:
ALTER TABLE dept MODIFY CONSTRAINT dname_ukey VALIDATE;
ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;
若要停用/删除相关的FOREIGN KEY约束,则:
ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;
删除:
ALTER TABLE dept DROP UNIQUE (dname, loc);
ALTER TABLE emp DROP PRIMARY KEY KEEP INDEX,
DROP CONSTRAINT dept_fkey;
三、延时约束检查
Oracle可以再事务中先不检查约束,直到事务结束时才统一检查。这样就可以让事务在处理的过程中暂时得违反约束,例如某时候在往表里添加数据时,某字段暂时留空,在下一步中再update数据,以逐步插入数据,这样就必须用到这个性能。使用SET CONSTRAINTS语句进行设置。
在使用这个特性时,需要注意几点:
● 不能在触发器内发布SET CONSTRAINTS语句
● 可延时的唯一键和主键必须使用非唯一索引
1、所有约束设置成延时
SET CONSTRAINTS ALL DEFERRED;
注:在所有处理前执行该语句,至事务结束时为止,只适用于当前会话。
2、检查提交(可选)
在事务COMMIT之前,可以用 SET CONSTRAINTS ALL IMMEDIATE 语句来手工检查约束的违反情况。虽然在事务结束时会自动隐性执行这条语句,但是也是有一定的存在意义的,例如希望在错误时定义某些操作。
四、报告约束异常
如果校验约束时存在异常,则返回一个错误,且完整性约束仍保持未被校验状态。当约束存在异常时,一个语句就不能正确执行,则此语句被回滚。如果存在异常,必须要更新或删除了约束的所有异常后,才可以校验约束。但是在使用ALTER TABLE语句不能确定哪一行违反约束,为了确定哪一行,在发布ENABLE子句中带有EXCEPTION选项的ALTER TABLE语句。
EXCEPTION选项将ROWID、OWNER、TABLE、ROWID、CONSTRAINT放到一个指定的表中。在启用约束前,硬创建一个合适的异常报告表,用来接收ENABLE子句的EXCEPTION选项信息,可以直接执行'D:\oracle\ora92\rdbms\admin\utlexcpt.sql'或'D:\oracle\ora92\rdbms\admin\utlexcpt1.sql'脚本来进行创建。注意:这两个脚本的区别在于数据库的兼容性级别和所分析的表的类型。
使用的语法如下:
ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;
如果存在异常,则直接查询 SELECT * FROM EXCEPTIONS; 即可。如果需要更加详细的信息,则可以使用ROWID与原表的ROWID进行关联,这样就可以查出原始表中当前行的所有信息,并进行修改。
关于Oracle的约束概念和基本操作,我已经在以前的《Constraint基础概念》、《Constraint的简单操作》两篇文章中有过比较详细的介绍了,但是对于如何停用和启用constraint没有作特别的描述,以至于在使用PLSQL中无法忽略constraint而逐步进行数据的更改,所以在这里专门记录一下关于constraint的停用和启用相关知识。
一、约束的状态
可以指定启用(ENABLE)或者停用(DISABLE)约束。如果启用约束,当在数据库中输入或者更新数据时要进行数据检查,不符合约束规则的数据不能输入数据库。如果停用约束,那么不符合规则的数据也能输入到数据库中。 另外,也能制定表中的现有数据必须符合约束(VALIDATE);相反地,如果指定为(NOVALIDATE),就不能确保现有的数据符合约束。
所以由上述的特性可知,表上的约束可能处在以下4种状态:
● ENABLE , VALIDATE
● ENABLE , NOVALIDATE
● DISABLE, VALIDATE
● DISABLE, NOVALIDATE
1、停用约束的情况
一般来说,约束都是需要始终处于启用状态的,但是在以下情况中可能需要约束停用:
● 当往表中装载大量数据时
● 当执行批操作使得表有大规模的改变时
● 到一次性导入或导出一个表时
停用都是为了提高性能,特别是在数据仓库中,这种操作的作用尤其明显。而且由于停用约束有可能造成违反约束的数据输入到表中,所以在进行完以上的动作之后,需要立即启用约束。
2、启用约束的一些问题
在停用约束时,违反约束的行可以插入到表中,这种行为被认为是对约束异常。而且如果约束处于启用非校验状态(ENABLE,NOVALIDATE),则在停用时插入的违反约束的记录仍然保留,需要更改后,约束方可出在已校验状态。
当表的约束处于启用非校验状态时,表中原本的数据可以不符合约束,但后面新增的数据必须进行校验,这对于数据仓库来说特别有用。因为基本上数据仓库都是使用增量更新,在停用约束后如果采用启用校验约束状态,则需要对大量数据进行校验,影响性能。
另外说明:校验一个已经启用的约束,在校验期间不需要任何DML锁,因此在校验期间可以保证没有引入违反约束的数据。
3、完整性约束状态:过程和效益
一般可以按照以下的顺序来使用约束,以确保最佳的效益:
① 停用状态
② 执行操作(装载、导出、导入)
③ 启用非校验状态
④ 启用状态
好处是:
● 不留锁
● 所有的约束能够同时处于启用状态
● 约束启用是并行实现的
● 允许表上进行同步操作
二、在定义中设置约束属性
在CREATE TABLE和ALTER TABLE语句中就可以设置约束的ENABLE/DISABLE、VALIDATE/NOVALIDATE。
注:默认的是ENABLE/VALIDATE的。
1、在定义中停用约束
CREATE TABLE emp( empno NUMBER(5) PRIMARY KEY DISABLE,
deptno NUMBER(2));
ALTER TABLE emp ADD PRIMARY KEY(empno) DISABLE;
2、在定义中启用约束
CREATE TABLE emp( empno NUMBER(5) PRIMARY KEY,
deptno NUMBER(2));
ALTER TABLE emp ADD PRIMARY KEY(empno);
注:有可能创建约束失败,因为原始数据与约束冲突,此时语句回滚。
3、修改或删除现有的约束
注:在使用外键参考了PRIMARY或UNIQUE键时,不能停用或删除被参考约束
停用:
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;
ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX,
DISABLE UNIQUE (dname, loc) KEEP INDEX;
启用非校验:
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;
ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY,
ENABLE NOVALIDATE UNIQUE (dname, loc);
启用校验:
ALTER TABLE dept MODIFY CONSTRAINT dname_ukey VALIDATE;
ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;
若要停用/删除相关的FOREIGN KEY约束,则:
ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;
删除:
ALTER TABLE dept DROP UNIQUE (dname, loc);
ALTER TABLE emp DROP PRIMARY KEY KEEP INDEX,
DROP CONSTRAINT dept_fkey;
三、延时约束检查
Oracle可以再事务中先不检查约束,直到事务结束时才统一检查。这样就可以让事务在处理的过程中暂时得违反约束,例如某时候在往表里添加数据时,某字段暂时留空,在下一步中再update数据,以逐步插入数据,这样就必须用到这个性能。使用SET CONSTRAINTS语句进行设置。
在使用这个特性时,需要注意几点:
● 不能在触发器内发布SET CONSTRAINTS语句
● 可延时的唯一键和主键必须使用非唯一索引
1、所有约束设置成延时
SET CONSTRAINTS ALL DEFERRED;
注:在所有处理前执行该语句,至事务结束时为止,只适用于当前会话。
2、检查提交(可选)
在事务COMMIT之前,可以用 SET CONSTRAINTS ALL IMMEDIATE 语句来手工检查约束的违反情况。虽然在事务结束时会自动隐性执行这条语句,但是也是有一定的存在意义的,例如希望在错误时定义某些操作。
四、报告约束异常
如果校验约束时存在异常,则返回一个错误,且完整性约束仍保持未被校验状态。当约束存在异常时,一个语句就不能正确执行,则此语句被回滚。如果存在异常,必须要更新或删除了约束的所有异常后,才可以校验约束。但是在使用ALTER TABLE语句不能确定哪一行违反约束,为了确定哪一行,在发布ENABLE子句中带有EXCEPTION选项的ALTER TABLE语句。
EXCEPTION选项将ROWID、OWNER、TABLE、ROWID、CONSTRAINT放到一个指定的表中。在启用约束前,硬创建一个合适的异常报告表,用来接收ENABLE子句的EXCEPTION选项信息,可以直接执行'D:\oracle\ora92\rdbms\admin\utlexcpt.sql'或'D:\oracle\ora92\rdbms\admin\utlexcpt1.sql'脚本来进行创建。注意:这两个脚本的区别在于数据库的兼容性级别和所分析的表的类型。
使用的语法如下:
ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;
如果存在异常,则直接查询 SELECT * FROM EXCEPTIONS; 即可。如果需要更加详细的信息,则可以使用ROWID与原表的ROWID进行关联,这样就可以查出原始表中当前行的所有信息,并进行修改。
发表评论
-
SQL总结存储过程
2017-02-16 10:13 362概念 存储过程(Stored Procedure):已预编译 ... -
oracle设置定时任务job调度执行存储过程或pl/sql代码块
2017-02-16 09:53 401目前oracle数据库设置定时任务job调度一般有两种方式,分 ... -
sql大全
2013-07-12 19:02 1003经典SQL语句大全 一、基础 1、说明: ... -
Linux安装mysql——源码安装
2013-06-05 09:17 7541.假设已经有mysql-5.5.10.tar.gz以及cma ... -
hql语句集合
2013-04-21 19:13 1041/** * */ package com.b510 ... -
oracle命令导入dmp文件与给用户受权
2012-05-03 15:57 988创建账号:CREATE USER vdrs IDENTIFIE ... -
oracle对日期进行操作
2012-03-19 15:41 1224一下心血来潮,把部分Oracle对时间的一些处理,整理了一下, ... -
如何查出同一张表中字段值重复的记录
2012-03-17 09:45 1548比如现在有一人员表(表名:peosons) 若想将姓名、身份证 ... -
数据库驱动大全
2011-12-26 09:47 889经常因为链接字符串记不住或者记错的原因浪费时间,终于有时间把常 ... -
用JSP导出ORACLE的数据表DDL
2011-12-08 10:47 1053EXPDDL.JSP <%@ page cont ... -
jsp脚本实现本分数据库
2011-12-08 10:44 855JSP导出Oracle数据表是一个在开发应用时的常用的操作,那 ... -
oracle创建id自增及相关增删改查操作
2011-11-21 16:46 955新建下载权限记录表 DOWNLOADPOPE_TAB ... -
java.sql.SQLException: Io 异常: The Network Adapter could not establish the connec
2011-10-28 15:43 1250WARN: SettingsFactory,103 - Cou ... -
oracle安装及使用
2011-10-16 23:50 880安装后数据库 1.使用plsql打开,一般选项 口 ... -
递归数
2011-10-10 17:30 1054有的情况下,我们需要用递归的方法整理数据,这才程序中很容易 ... -
mysql中递归树状结构
2011-10-10 17:20 11304在Oracle 中我们知道有一个 Hierarchic ... -
级联删除和级联修改
2011-06-22 14:08 2783曾经因为级联删除的问题浪费了N多时间,顾此在这里写下小小心得, ... -
mysql解决乱码问题?
2011-06-15 14:33 785mysql数据库,乱码解决 ... -
MySql
2011-06-14 16:12 7941.安装之后 2.创建数据库 s2sh(库名) creat ... -
数据库死锁的解释?以及什么是死锁
2011-04-25 10:41 822什么是数据库死锁 每个使用关系型数据库的程序都可能 ...
相关推荐
第15章 Oracle中的控制语句.ppt 第16章 SQL查询.ppt 第17章 SQL更新数据.ppt 第18章 数据库速度优化与数据完整性.ppt 第19章 数据一致性与事务管理.ppt 第20章 并发控制.ppt 第21章 Oracle中的正则表达式.ppt 第22章...
第15章 Oracle中的控制语句(教学视频:16分钟) 282 第16章 SQL查询(教学视频:55分钟) 290 第17章 SQL更新数据(教学视频:34分钟) 319 第四篇 Oracle编程高级应用 第18章 数据库速度优化与数据完整性...
《Oracle 从入门到精通》中的视频教程和PPT资料。 Oracle 11G从入门到精通视频的PPT 第1章-Oracle 11g数据库简介 认识Oracle 11g 回忆Oracle的产品版本 学习Oracle 11g的新特性 第2章-Oracle 11g的安装与测试...
数据库开发 Oracle数据库 SQL开发教程 第11章 数据操作与事务控制(共52页).pdf 数据库开发 Oracle数据库 SQL开发教程 第12章 Oracle数据库网络连接(共39页).pdf 数据库开发 Oracle数据库 SQL开发教程 第13章 ...
4. PL/SQL条件控制和循环控制 91 5. PL/SQL中动态执行SQL语句 101 6. PL/SQL的异常处理 102 7. 本章总结 108 8. 本章练习 109 Oracle应用于.Net平台 111 1. 回顾ADO.NET 112 2. 使用ADO.NET连接Oracle 113 3. 抽象...
Day03-约束和序列.pdf Day04-SQL和操作符.pdf Day05-Oracle常用函数.pdf Day06-Oracle高级查询一.pdf Day07-Oracle高级查询二.pdf Day08-Oracle分页查询和视图.pdf Day09-存储过程.pdf Day10-变量定义和循环控制.pdf...
1.2.6 数据完整性约束 1.2.7 联机事务处理和联机分析处理 1.2.8 数据仓库 1.3 Oracle基本术语 1.3.1 数据字典 1.3.2 数据文件 1.3.3 控制文件 1.3.4 日志文件 1.3.5 表空间 1.3.6 段 1.3.7 区 ...
2. Oracle介绍与安装 2.1 Oracle安装 2.2 Oracle 默认用户 2.3 服务器连接 3. PL/SQL Developer工具 4. Oracle用户和权限 4.1用户和权限 4.2角色 第2章 SQL数据操作和查询 1. SQL简介 2. 查询 2.1查询...
第三章 ORACLE SQL 单行函数 第四章 从多表中查询数据 第五章 用组函数合计数据 第六章 子查询 第七章 操纵数据 第八章 创建和管理表 第九章 内置约束 第十章 创建视图 第十一章 其他数据库对象 第十二章 控制用户...
第一章:oracle体系结构 ORACLE 10G的卸载与安装 客户端连接工具 Oracle 10g体系结构 第二章:sql基础 ORACLE数据类型 SQL基本语法 简单查询 SQL函数 多数据表查询 事务控制 第三章:plsql基础 声明及...
数据库开发 Oracle数据库 SQL开发教程 第11章 数据操作与事务控制(共52页).pdf 数据库开发 Oracle数据库 SQL开发教程 第12章 Oracle数据库网络连接(共39页).pdf 数据库开发 Oracle数据库 SQL开发教程 第13章 ...
韩顺平老师 oracle 教程笔记 1.Oracle 认证,与其它数据库比较,安装 2.Oracle 的基本使用--基本命令 ...19.pl/sql 的进阶--控制结构(分支,循环,控制) 20.PL/SQL 分页 21.例外处理 22.oracle 的视图
第3篇以数据库对象为主线讲述了包括视图、约束、游标、触发器、序列、用户角色与权限控制、函数与存储过程等内容;第4篇则重点讲述了oracle性能优化、一致性与事务管理、并发控制等内容;第5篇讲述了oracle在主流...
第3篇以数据库对象为主线讲述了包括视图、约束、游标、触发器、序列、用户角色与权限控制、函数与存储过程等内容;第4篇则重点讲述了oracle性能优化、一致性与事务管理、并发控制等内容;第5篇讲述了oracle在主流...
数据库开发 Oracle数据库 SQL开发教程 第11章 数据操作与事务控制(共52页).pdf 数据库开发 Oracle数据库 SQL开发教程 第12章 Oracle数据库网络连接(共39页).pdf 数据库开发 Oracle数据库 SQL开发教程 第13章 ...
(1)触发器可以声明为在对记录进行操作之前,在之前(检查约束之前和 INSERT,UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT, UPDATE 或 DELETE 操作)触发.。 (2)一个 FOR EACH ROW 执行指定操作...
Toad DBA Suite for Oracle 9.7.2 Commercial安装文件下载BT...控制代码访问和开发(或没有第三方版本控制产品)使用Toad的合作源控制功能。 创建项目,更轻松地管理你的工作 逐句通过代码执行 调试PL/SQL脚本和Java
在案例精讲中对数据块校验、表的移动、数据文件丢失或损坏、丢失控制文件、数据文件更名以及数据库内文件的复制传输等问题的解决做了全面细致的讲解。第3章SQL及PL/SQL的运行环境及其他开发工具。第4章 Oracle数据...
走进Oracle 1. Oracle简介 2. Oracle安装 3. Oracle客户端工具 4. Oracle服务 5. Oracle启动和关闭 6. Oracle用户和权限 ...4. PL/SQL条件控制和循环控制 5. PL/SQL中动态执行SQL语句 ....