管理Oracle约束与分区表

1．约束

创新互联坚持“要么做到，要么别承诺”的工作理念，服务领域包括：网站建设、网站制作、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的峨边彝族网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！

作用：

约束用于确保数据库数据的完整性,在oracle数据库中,可以使用约束,触发器和应用代码(过程,函数)3种方法实现数据完整性,这3种方法中,因为约束易于维护,并且具有***的性能,所以实现数据完整性***约束.

分类：

约束的种类有：not null,unique,primary key,foreign key,check

Not null确保字段值不能为空

Unique:确保字段值唯一性

Primary key,最常用的约束（主键约束），主键约束的列值不仅不能重复，也不能为NULL，注意一张表最多只能有一个主键约束，当定义主键约束后oracle自动建立一个以主键为关键字段的索引。

Foreign key:定义了主从表之间的关系，foreign要定义在从表上，但主表必须具有主键约束或唯一约束，当定义froeign key后外部键列的数据必须在主表的主键列中存在，或者为NULL

Check::用于强制表行数据必须满足的条件，如工资表，工人工资必须在2000-5000之间

约束状态

enable validate:是默认,新旧数据同时满足约束规则

enable novalidate:旧数据可以不满足,检新插入的数据要满足约束

disable validate:不允许在表上执行任何DML操作,主要用在分区表,对于主键和唯一约事,会删除相应的唯一索引,但约束状态任可用

disable novalidate数据可不满足约束规则,对于主键和唯一约事,会删除相应的唯一索引,

约束常用语句

 
 
 
  
  
  create table t(i number,v mubmer not null)    
  
  create table t(i number,v mubmer unique)    
  
  create table t(i number constraint pk_i primary key,v number)    
  
  create table t2(c number,d number,constraint fk_d foreign key(c),references t1(v));    
  
  alter table t add constraint pk_i primary key (i)    
  
  alter table t modify i not null;    
  
  alter table t add constraint t_i unique(i)[(create index ind_name on t(i))];    
  
  alter table t add constraint t_i check(i in (1,2,3,4,5));    
  
  alter table t disable novalidate constraint i    
  
  alter table t enable novalidate constraint check_i    
  
  alter table t drop constraint i;    
  
  alter table t drop primary key i;

#常用的数据字典

 
 
 
  
  
  dba_constraints    
  
  dba_cons_columns    
  
  user_cons_columns    
  
  user_constraints

简单应用

检验当为一个表建立主键索时后，这个字段是否满足约束非空，唯一性，而且自动建立一个索引，并查看当把约束状态关闭再次插入相同的记录,是否还能把把约束设为enable ividate状态。

 
 
 
  
  
  SQL> create table t(i number constraint pk_i primary key,v number);    
  
  SQL> insert into t values(1,2);    
  
  SQL> insert into t values(3,4);    
  
  SQL> commit;    
  
  SQL> select * from t;    
  
           I          V    
  
  ---------- ---------------------------    
  
           1          2    
  
           3          4

现在表中有两条记录，然后给它插主键为空或相同的值

 
 
 
  
  
  SQL> insert into t values('',10);    
  
  ERROR at line 1:    
  
  ORA-01400: cannot insert NULL into ("Y"."T"."I")    
  
  SQL> insert into t values(1,10);    
  
  ERROR at line 1:    
  
  ORA-00001: unique constraint (Y.PK_I) violated

可以看到全部报错，此时主键不能为空或重复

查看是否建立索引

 
 
 
  
  
  SQL> select index_name from user_indexes;    
  
  INDEX_NAME    
  
  ------------------------------    
  
  PK_I

把约束关闭再次做同样的操用

 
 
 
  
  
  SQL> alter table t disable novalidate constraint pk_i;    
  
  Table altered.    
  
  SQL> insert into t values('',10);    
  
  1 row created.    
  
  SQL> insert into t values(1,10);    
  
  1 row created.    
  
  SQL> commit;    
  
  Commit complete.    
  
  SQL> select * from t;    
  
           I          V    
  
  ---------- ----------    
  
           1          2    
  
           3          4    
  
                     10    
  
           1         10    
  
  SQL>  select index_name from user_indexes;    
  
  no rows selected

可见当把约束关闭后就可以何意给表插数据了，而具索引也自动删除了。

现在激活约束

 
 
 
  
  
  SQL> alter table t enable validate constraint pk_i;    
  
  alter table t enable validate constraint pk_i    
  
  ERROR at line 1:    
  
  ORA-02437: cannot validate (SYS.PK_I) - primary key violated

因为表中主键有相同的值所以不能恢复到enable validate状态了

再次测试回复到enable novalidate

 
 
 
  
  
  SQL> alter table t enable novalidate constraint pk_i;    
  
  alter table t enable validate constraint pk_i    
  
  ERROR at line 1:    
  
  ORA-02437: cannot validate (SYS.PK_I) - primary key violated

也失败了,

因为表中主键有了空值和相同的值,所以恢复不到enable validate状态,但enable novalidate不检查旧数据所以应该还能恢复到enable novalidate.

要想恢复到enable novalidate必须建立主键索引(关闭约束时自动删除的那个索引)如下:

 
 
 
  
  
  SQL> create index pk_i on t(i);    
  
  Index created.

然后恢复到enable disvalidate,以后再插数据不能为空,主键也不能重复了.

 
 
 
  
  
  SQL> alter table t enable novalidate constraint pk_i;    
  
  Table altered.    
  
  SQL> insert into t values(1,14);    
  
  insert into t values(1,14)    
  
  ERROR at line 1:    
  
  ORA-00001: unique constraint (SYS.PK_I) violated

2.修正约束数据

当给一个表作主键约束时,因为已存数据不满足约束规则,会提示错误信息,些时必须对数据进行修正

要修正数据先找出不满足约束的数据

如下表,有不满足约束的数据

 
 
 
  
  
  SQL> select * from t;    
  
           I          V    
  
  ---------- ------------------------    
  
           1          2    
  
           3          4    
  
           15         12    
  
           15         10

如果一个表数据量多可通过如下方法查找

 
 
 
  
  
  SQL> alter table t drop constraint pk_i;      
  
  Table altered.    
  
  SQL>conn y / 123    
  
  SQL> @$ORACLE_HOME/rdbms/admin/utlexcpt.sql    
  
  Table created.    
  
  SQL> alter table t add constraint pk_i primary key (i) exceptions into exceptions;    
  
  select * from t where rowid in (select row_id from exceptions)    
  
           I          V    
  
  ---------- ------------------------    
  
           15         12    
  
           15         10

找到了重复的记录

修正

 
 
 
  
  
  SQL>update t set i=10 where v=12;    
  
  SQL> select * from t;    
  
           I          V    
  
  ---------- ----------    
  
           1          2    
  
           3          4    
  
           10         12    
  
           15         10

再建主键约束

 
 
 
  
  
  alter table t add constraint pk_i primary key (i)    
  
  Table altered.

成功了!!!

#p#

二:分区表管理

作用:将在张大表的数据分布到多个表分区段,不同分区彼此独立,从而提高了表的可用性和性能

种类:范围分区,散列分区(使用HASH算法,最常使用),列表分区,范围/散列组合分区,范围/列表组合分区

范围分区表

创建范围分区表

 
 
 
  
  
  create table t(v number,b number)    
  
  partition by range(v) (    
  
  partition p1 values less than ('11') tablespace test1,    
  
  partition p2 values less than ('21') tablespace test2);

增加与删除分区

#增加分区

 
 
 
  
  
  alter table t add partition p3 values less than ('31') tablespace test3;    
  
  alter table t drop partition p3

一个时间分区的例子

 
 
 
  
  
  alter session set nls_data_lanage=AMERICAN;    
  
  alter session set nls_data_format='DD-MON-YYYY'   
  
  create table t(v_date date,b number)    
  
  partition by range(v_date)(    
  
  partition p1 values less than ('01-APR-2009') tablespace test1,    
  
  partition p2 values less than ('01-JUN-2009') tablespace test2);

2.散列分区表(最常用)

创建

 
 
 
  
  
  create table t1(    
  
  v number,b number)    
  
  partition by hash(v)    
  
  (partition p1 tablespace test1,    
  
  partition p2 tablespace test2);

增加分区

 
 
 
  
  
  alter table t add partition p3 tablespace test3;

删除分区

 
 
 
  
  
  alter table t drop coalesce partition;

3.列表分区

建列表分区

 
 
 
  
  
  create table t(    
  
  v varchar2(10),    
  
  b number    
  
  )partition by list(v)    
  
  (partition p1 values('a','b') tablespace test1,    
  
  partition p2 values('c','d') tablespace test2);

#插入数据

 
 
 
  
  
  SQL> insert into t values('a',10);    
  
  SQL> insert into t values('d',20);

#注意,插入数据时***个字段只能为a,b,c,d

 
 
 
  
  
  SQL> insert into t values('f',30);    
  
  ERROR at line 1:    
  
  ORA-14400: inserted partition key does not map to any partition

#查询

 
 
 
  
  
  select * from t;    
  
  select * from t partition(p1);    
  
  select * from t partition(p2);    
  
  select * from t where v=XXX

增加分区

 
 
 
  
  
  alter table t add partition p3 values('31','32') tablespace test3;

删除分区

 
 
 
  
  
  alter table t drop partition p3

4.范围/散列组合分区

建立散列组合分区

 
 
 
  
  
  create table t(    
  
  v number,b number)    
  
  partition by range(v)    
  
  subpartition by hash(b) subpartitions 2    
  
  store in (test1,test2)(    
  
  partition p1 values less than ('11'),    
  
  partition p2 values less than ('21'));

查询

 
 
 
  
  
  select * from t;    
  
  select * from t partition(p1);    
  
  select * from t where ....

增加主分区和子分区

 
 
 
  
  
  alter table t add partition p3 values less than ('31') tablespace test3;    
  
  alter table t modify partition p3 add subpartition;

删除分区

 
 
 
  
  
  alter table t coalesce partition;    
  
  alter table t modify partition p1 coalesce subpartition;

5.范围/列表组合分区

创建

 
 
 
  
  
  create table t(    
  
  v number,b number)    
  
  partition by range(v)    
  
  subpartition by list(b)    
  
  (partition p1 values less than ('11') tablespace test1(    
  
  subpartition p1_1 values('1','3'),    
  
  subpartition p1_2 values('5','6')    
  
  ),    
  
  partition p2 values less than ('21') tablespace test2(    
  
  subpartition p2_1 values('13','14'),    
  
  subpartition p2_2 values('15','16')    
  
  ));

查询

 
 
 
  
  
  select * from t    
  
  select * from t partition(p1)    
  
  select * from t subpartition(p1_1)    
  
  select * from t where .....    
  
  select segment_name,partition_name,tablespace_name    
  
  from user_segments where segment_name='T';

增加分区和子分区

 
 
 
  
  
  alter table t add partition p3 values less than ('31') tablespace test3(    
  
  subpartition p3_1 values('25','26'),    
  
  subpartition p3_2 values('22','23'));    
  
  alter table t modify partition r3    
  
  add subpartition r3_3 tablespace test3 values('28','29');

删除分区

 
 
 
  
  
  alter table t modify partition p1 coalesce subpartition;

其它设置

 
 
 
  
  
  交换分区数据    
  
  alter table t exchange partition p1 with table tt;    
  
  载断分区    
  
  alter table t truncate partition p1;    
  
  修改分区名    
  
  alter table t rename partition p2_1 to p2;    
  
  合并分区    
  
  alter table t merge partitions p1,p2 into partition p01    
  
  重组分区    
  
  alter table t move partition p1 tablespace test04    
  
  为列表分区和子分区加值    
  
  alter table t modify partition p1 add values('111');    
  
  alter table t modify subpartition p3_1 add values('111');    
  
  从列表分区和子分区中删除值    
  
  alter table t modify partition p1 drop values('111')    
  
  alter table t modify subpartition p3_1 drop values('111')

分区表常用的数据字典

 
 
 
  
  
  分区表信息:  dba_part_tables    
  
  显示分区:    dba_tab_partitions    
  
  显示子分区:  dba_tab_subpartitions    
  
  显示分区列:  dba_part_key_columns    
  
  显示子分区列:dba_subpart_dey_columns    
  
  显示分区索引:dba_part_indexes    
  
  显示索引分区:dba_ind_partitions

来源：本文出自 “追求” 博客。

标题名称：管理Oracle约束与分区表
网站路径：http://www.csdahua.cn/qtweb/news7/550957.html

网站建设、网络推广公司-快上网，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：快上网

成都快上网为您推荐相关内容