管理Oracle约束与分区表.doc

1、1约束作用：约束用于确保数据库数据的完整性,在 oracle 数据库中,可以使用约束,触发器和应用代码(过程,函数)3 种方法实现数据完整性,这 3 种方法中,因为约束易于维护,并且具有最好的性能,所以实现数据完整性首选约束.分类：约束的种类有：not null,unique,primary key,foreign key,checkNot null 确保字段值不能为空Unique:确保字段值唯一性Primary key,最常用的约束（主键约束），主键约束的列值不仅不能重复，也不能为 NULL，注意一张表最多只能有一个主键约束，当定义主键约束后 oracle 自动建立一个以主键为关键字段的索引

2、。Foreign key:定义了主从表之间的关系，foreign 要定义在从表上，但主表必须具有主键约束或唯一约束，当定义 froeign key 后外部键列的数据必须在主表的主键列中存在，或者为 NULLCheck:用于强制表行数据必须满足的条件，如工资表，工人工资必须在 2000-5000 之间约束状态enable validate:是默认,新旧数据同时满足约束规则enable novalidate:旧数据可以不满足,检新插入的数据要满足约束disable validate:不允许在表上执行任何 DML 操作,主要用在分区表,对于主键和唯一约事,会删除相应的唯一索引,但约束状态任可用dis

3、able novalidate 数据可不满足约束规则,对于主键和唯一约事,会删除相应的唯一索引,约束常用语句1. create table t(i number,v mubmer not null) 2. create table t(i number,v mubmer unique) 3. create table t(i number constraint pk_i primary key,v number) 4. create table t2(c number,d number,constraint fk_d foreign key(c),references t1(v); 5. al

4、ter table t add constraint pk_i primary key (i) 6. alter table t modify i not null; 7. alter table t add constraint t_i unique(i)(create index ind_name on t(i); 8. alter table t add constraint t_i check(i in (1,2,3,4,5); 9. alter table t disable novalidate constraint i 10. alter table t enable noval

5、idate constraint check_i 11. alter table t drop constraint i; 12. alter table t drop primary key i; #常用的数据字典1. dba_constraints 2. dba_cons_columns 3. user_cons_columns 4. user_constraints 简单应用检验当为一个表建立主键索时后，这个字段是否满足约束非空，唯一性，而且自动建立一个索引，并查看当把约束状态关闭再次插入相同的记录,是否还能把把约束设为 enable ividate 状态。1. SQL create t

6、able t(i number constraint pk_i primary key,v number); 2. SQL insert into t values(1,2); 3. SQL insert into t values(3,4); 4. SQL commit; 5. SQL select * from t; 6. I V 7. - - 8. 1 2 9. 3 4 现在表中有两条记录，然后给它插主键为空或相同的值1. SQL insert into t values(,10); 2. ERROR at line 1: 3. ORA-01400: cannot insert NULL

7、 into (“Y“.“T“.“I“) 4. SQL insert into t values(1,10); 5. ERROR at line 1: 6. ORA-00001: unique constraint (Y.PK_I) violated 可以看到全部报错，此时主键不能为空或重复查看是否建立索引1. SQL select index_name from user_indexes; 2. INDEX_NAME 3. - 4. PK_I 把约束关闭再次做同样的操用1. SQL alter table t disable novalidate constraint pk_i; 2. Tab

8、le altered. 3. SQL insert into t values(,10); 4. 1 row created. 5. SQL insert into t values(1,10); 6. 1 row created. 7. SQL commit; 8. Commit complete. 9. SQL select * from t; 10. I V 11. - - 12. 1 2 13. 3 4 14. 10 15. 1 10 16. SQL select index_name from user_indexes; 17. no rows selected 可见当把约束关闭后就

9、可以何意给表插数据了，而具索引也自动删除了。现在激活约束1. SQL alter table t enable validate constraint pk_i; 2. alter table t enable validate constraint pk_i 3. ERROR at line 1: 4. ORA-02437: cannot validate (SYS.PK_I) - primary key violated 因为表中主键有相同的值所以不能恢复到 enable validate 状态了再次测试回复到 enable novalidate1. SQL alter table t e

10、nable novalidate constraint pk_i; 2. alter table t enable validate constraint pk_i 3. ERROR at line 1: 4. ORA-02437: cannot validate (SYS.PK_I) - primary key violated 也失败了,因为表中主键有了空值和相同的值,所以恢复不到 enable validate 状态,但 enable novalidate 不检查旧数据所以应该还能恢复到 enable novalidate.要想恢复到 enable novalidate 必须建立主键索引

11、(关闭约束时自动删除的那个索引)如下:1. SQL create index pk_i on t(i); 2. Index created. 然后恢复到 enable disvalidate,以后再插数据不能为空,主键也不能重复了.1. SQL alter table t enable novalidate constraint pk_i; 2. Table altered. 3. SQL insert into t values(1,14); 4. insert into t values(1,14) 5. ERROR at line 1: 6. ORA-00001: unique cons

12、traint (SYS.PK_I) violated 2.修正约束数据当给一个表作主键约束时,因为已存数据不满足约束规则,会提示错误信息,些时必须对数据进行修正要修正数据先找出不满足约束的数据如下表,有不满足约束的数据1. SQL select * from t; 2. I V 3. - - 4. 1 2 5. 3 4 6. 15 12 7. 15 10 如果一个表数据量多可通过如下方法查找1. SQL alter table t drop constraint pk_i; 2. Table altered. 3. SQLconn y / 123 4. SQL $ORACLE_HOME/rdb

13、ms/admin/utlexcpt.sql 5. Table created. 6. SQL alter table t add constraint pk_i primary key (i) exceptions into exceptions; 7. select * from t where rowid in (select row_id from exceptions) 8. I V 9. - - 10. 15 12 11. 15 10 找到了重复的记录修正1. SQLupdate t set i=10 where v=12; 2. SQL select * from t; 3. I

14、V 4. - - 5. 1 2 6. 3 4 7. 10 12 8. 15 10 再建主键约束1. alter table t add constraint pk_i primary key (i) 2. Table altered. 成功了!二:分区表管理作用:将在张大表的数据分布到多个表分区段,不同分区彼此独立,从而提高了表的可用性和性能种类:范围分区,散列分区(使用 HASH 算法,最常使用),列表分区,范围/散列组合分区,范围/列表组合分区范围分区表创建范围分区表1. create table t(v number,b number) 2. partition by range(v)

15、( 3. partition p1 values less than (11) tablespace test1, 4. partition p2 values less than (21) tablespace test2); 增加与删除分区#增加分区1. alter table t add partition p3 values less than (31) tablespace test3; 2. alter table t drop partition p3 一个时间分区的例子1. alter session set nls_data_lanage=AMERICAN; 2. alter

16、 session set nls_data_format=DD-MON-YYYY 3. create table t(v_date date,b number) 4. partition by range(v_date)( 5. partition p1 values less than (01-APR-2009) tablespace test1, 6. partition p2 values less than (01-JUN-2009) tablespace test2); 2.散列分区表(最常用)创建1. create table t1( 2. v number,b number) 3

17、. partition by hash(v) 4. (partition p1 tablespace test1, 5. partition p2 tablespace test2); 增加分区1. alter table t add partition p3 tablespace test3; 删除分区1. alter table t drop coalesce partition; 3.列表分区建列表分区1. create table t( 2. v varchar2(10), 3. b number 4. )partition by list(v) 5. (partition p1 va

18、lues(a,b) tablespace test1, 6. partition p2 values(c,d) tablespace test2); #插入数据1. SQL insert into t values(a,10); 2. SQL insert into t values(d,20); #注意,插入数据时第一个字段只能为 a,b,c,d1. SQL insert into t values(f,30); 2. ERROR at line 1: 3. ORA-14400: inserted partition key does not map to any partition #查询

19、1. select * from t; 2. select * from t partition(p1); 3. select * from t partition(p2); 4. select * from t where v=XXX 增加分区1. alter table t add partition p3 values(31,32) tablespace test3; 删除分区1. alter table t drop partition p3 4.范围/散列组合分区建立散列组合分区1. create table t( 2. v number,b number) 3. partition

20、 by range(v) 4. subpartition by hash(b) subpartitions 2 5. store in (test1,test2)( 6. partition p1 values less than (11), 7. partition p2 values less than (21); 查询1. select * from t; 2. select * from t partition(p1); 3. select * from t where . 增加主分区和子分区1. alter table t add partition p3 values less t

21、han (31) tablespace test3; 2. alter table t modify partition p3 add subpartition; 删除分区1. alter table t coalesce partition; 2. alter table t modify partition p1 coalesce subpartition; 5.范围/列表组合分区创建1. create table t( 2. v number,b number) 3. partition by range(v) 4. subpartition by list(b) 5. (partiti

22、on p1 values less than (11) tablespace test1( 6. subpartition p1_1 values(1,3), 7. subpartition p1_2 values(5,6) 8. ), 9. partition p2 values less than (21) tablespace test2( 10. subpartition p2_1 values(13,14), 11. subpartition p2_2 values(15,16) 12. ); 查询1. select * from t 2. select * from t parti

23、tion(p1) 3. select * from t subpartition(p1_1) 4. select * from t where . 5. select segment_name,partition_name,tablespace_name 6. from user_segments where segment_name=T; 增加分区和子分区1. alter table t add partition p3 values less than (31) tablespace test3( 2. subpartition p3_1 values(25,26), 3. subpart

24、ition p3_2 values(22,23); 4. alter table t modify partition r3 5. add subpartition r3_3 tablespace test3 values(28,29); 删除分区1. alter table t modify partition p1 coalesce subpartition; 其它设置1. 交换分区数据 2. alter table t exchange partition p1 with table tt; 3. 载断分区 4. alter table t truncate partition p1;

25、5. 修改分区名 6. alter table t rename partition p2_1 to p2; 7. 合并分区 8. alter table t merge partitions p1,p2 into partition p01 9. 重组分区 10. alter table t move partition p1 tablespace test04 11. 为列表分区和子分区加值 12. alter table t modify partition p1 add values(111); 13. alter table t modify subpartition p3_1 ad

26、d values(111); 14. 从列表分区和子分区中删除值 15. alter table t modify partition p1 drop values(111) 16. alter table t modify subpartition p3_1 drop values(111) 分区表常用的数据字典1. 分区表信息: dba_part_tables 2. 显示分区: dba_tab_partitions 3. 显示子分区: dba_tab_subpartitions 4. 显示分区列: dba_part_key_columns 5. 显示子分区列:dba_subpart_dey_columns 6. 显示分区索引:dba_part_indexes 7. 显示索引分区:dba_ind_partitions 来源：网络 编辑：联动北方技术论坛