管理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.
成功了!!!

:分区表管理

作用:将在张大表的数据分布到多个表分区段,不同分区彼此独立,从而提高了表的可用性和性能
种类:范围分区,散列分区(使用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




本文转自 vfast_chenxy 51CTO博客,原文链接:http://blog.51cto.com/chenxy/169250,如需转载请自行联系原作者

目录
相关文章
|
2月前
|
Oracle 安全 关系型数据库
【Oracle】玩转Oracle数据库(六):模式对象管理与安全管理
【Oracle】玩转Oracle数据库(六):模式对象管理与安全管理
51 10
|
2月前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(三):数据库的创建和管理
【Oracle】玩转Oracle数据库(三):数据库的创建和管理
135 5
|
10月前
|
SQL 监控 Oracle
Oracle创建和管理表空间
Oracle创建和管理表空间
75 1
|
9月前
|
Oracle 关系型数据库 数据库
9-4 Oracle管理表空间和数据文件
9-4 Oracle管理表空间和数据文件
|
2月前
|
Kubernetes Oracle 关系型数据库
实时计算 Flink版产品使用合集之是否支持 Oracle 分区表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
SQL Oracle 关系型数据库
【ORACLE】 事务 | 锁 | 约束 | 权限、角色与用户管理
【ORACLE】 事务 | 锁 | 约束 | 权限、角色与用户管理
26 1
|
2月前
|
存储 Oracle 关系型数据库
实验三 Oracle数据库的创建和管理
实验三 Oracle数据库的创建和管理
32 1
|
2月前
|
存储 Oracle 网络协议
Oracle 11gR2学习之二(创建数据库及OEM管理篇)
Oracle 11gR2学习之二(创建数据库及OEM管理篇)
|
2月前
|
Oracle 关系型数据库
Oracle 管理诊断数据工具ADRCI
Oracle 管理诊断数据工具ADRCI
37 2
|
2月前
|
存储 Oracle 关系型数据库
Oracle系列之七:表的创建与管理
Oracle系列之七:表的创建与管理

推荐镜像

更多