威尼斯网站Oracle分区表删除分区引发错误ORA

来源:http://www.guanqinbiaoye.com 作者:计算机数据 人气:173 发布时间:2020-01-22
摘要:(一)问题: 一、什么是分区表 表分区有以下优点:  1、数据查询:数据被存储到多个文件上,减少了I/O负载,查询速度提高。  2、数据修剪:保存历史数据非常的理想。  3、备份

(一)问题:

一、什么是分区表
表分区有以下优点: 
1、数据查询:数据被存储到多个文件上,减少了I/O负载,查询速度提高。 
2、数据修剪:保存历史数据非常的理想。 
3、备份:将大表的数据分成多个文件,方便备份和恢复。 
4、并行性:可以同时向表中进行DML操作,并行性性能提高,均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。 
5、增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用; 
6、维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可; 
7、改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
8、需要注意的是包含LONG、LONGRAW数据类型的表不能分区,如果表格大于2G需要考虑分区。

最近在做Oracle数据清理,在对分区表进行数据清理时,采用的方法是drop partition,删除的过程中,没有遇到任何问题,大概过了10分钟,开发人员反馈部分分区表上的业务失败。具体错误为:

二、分区表的种类
1、RANGE 范围分区
说明:针对记录字段的值在某个范围。
规则:
(1)、每一个分区都必须有一个VALUES LESS THEN子句,它指定了一个不包括在该分区中的上限值。
分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
(2)、所有分区,除了第一个,都会有一个隐式的下限值,这个值就是此分区的前一个分区的上限值。
(3)、在最高的分区中,MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值,
也可以理解为高于任何分区中指定的VALUE LESS THEN的值,同时包括空值。若不添加maxvalue的分区插入数值一旦超过设置的最大上限会报错。

ORA-01502错误:索引或这类索引的分区处于不可用状态(英文:ora-01502:index 'schema.index_name' or partition of such index is in unusable state)。

例一,按date范围创建分区表

(二)原因分析

CREATE TABLE PART_TAB_CUSTOMER_BY_RANGE 
( 
  CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
  FIRST_NAME VARCHAR2(30) NOT NULL, 
  LAST_NAME VARCHAR2(30) NOT NULL, 
  PHONE VARCHAR2(15) NOT NULL, 
  EMAIL VARCHAR2(80), 
  SEX VARCHAR2(10),
  STATUS VARCHAR2(10),
  INSERT_DATE DATE 
) 
PARTITION BY RANGE (INSERT_DATE) --按时间分区
( 
  PARTITION DATE_RANGE1 VALUES LESS THAN (TO_DATE(' 2001-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data1, 
  PARTITION DATE_RANGE2 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data2,
  PARTITION DATE_RANGE3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3 
) 

查看出现问题的分区表,均有一个共同点:表上以“pk_”开头的索引为unusable状态,以“pk_”开头的索引是随创建主键约束而创建的。当用户在创建主键约束或唯一性约束的时候,会在相应的列上创建唯一性索引

例二、按照number范围分区

经过查证,发现是在删除分区的时候,导致分区表上的唯一性全局索引为不可用状态,导致新的数据无法正常插入,从而引发了该错误。

PARTITION BY RANGE (CUSTOMER_ID) --按id分区
( 
 PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE part_Data1, 
 PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE part_Data2,
 PARTITION CUS_PART2 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3
)

是不是索引不可用会导致DML操作失败呢?经过验证,发现以下特点:

 

1.对于非唯一性索引,如果索引不可用,是不会影响到到DML操作的;

2、LIST 列表分区
说明:该分区的特点是某列的值只有有限个值,基于这样的特点我们可以采用列表分区。
规则:默认分区为DEFAULT,若不添加DEFAULT的分区插入数值不属于所设置的分区会报错。
例一、姓氏

2.对于唯一性索引,如果索引不可用,在进行DML操作时,会触发ORA-01502错误;

CREATE TABLE PART_TAB_CUSTOMER_BY_LIST
( 
  CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
  PHONE VARCHAR2(15) NOT NULL, 
  EMAIL VARCHAR2(80), 
  SEX VARCHAR2(10),
  CORP_ID VARCHAR2(3),
  INSERT_DATE DATE,
  SUM_DATE varchar2(4)
) 
PARTITION BY LIST (SEX) 
( 
  PARTITION MALE      VALUES ('男')    TABLESPACE part_Data1, 
  PARTITION FEMALE    VALUES ('女')    TABLESPACE part_Data2 
) 

 

例二、varchar2的日期

这里记录一下哪些操作会导致索引失效:

PARTITION BY LIST (SUM_DATE) 
( 
  PARTITION SUM_DATE1    VALUES ('2012')    TABLESPACE part_Data1, 
  PARTITION SUM_DATE2    VALUES ('2013')    TABLESPACE part_Data2,
  PARTITION SUM_DATE2    VALUES (DEFAULT)    TABLESPACE part_Data2 
) 

威尼斯网站 1

3、HASH 散列分区
说明:这类分区是在列值上使用散列算法,以确定将行放入哪个分区中。
规则:当列的值没有合适的条件,没有范围的规律,也没有固定的值,建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,
使得这些分区大小一致。建议分区的数量采用2的n次方,这样可以使得各个分区间数据分布更加均匀。

                                                              图1.索引失效原因总结

创建hash分区有两种方法:一种方法是指定分区的名字,另一种方法是指定分区数量。

(三)解决方案

例一、常规方法指定分区名字

(3.1)了解唯一性索引

CREATE TABLE PART_TAB_CUSTOMER_BY_HASH
( 
  CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
  FIRST_NAME VARCHAR2(30) NOT NULL, 
  LAST_NAME VARCHAR2(30) NOT NULL, 
  PHONE VARCHAR2(15) NOT NULL, 
  EMAIL VARCHAR2(80), 
  SEX VARCHAR2(10),
  STATUS VARCHAR2(10),
  INSERT_DATE DATE 
) 
PARTITION BY HASH (CUSTOMER_ID) --按id散列
( 
  PARTITION hash1 TABLESPACE part_Data1, 
  PARTITION hash2 TABLESPACE part_Data2
) 

在解决问题之前,我们来分析一下,哪些行为会创建唯一性索引(3种):

例二、指定分区数量
PARTITION BY HASH (empno) PARTITIONS 2 STORE IN (part_Data1,part_Data2);
--往往我们不需要知道bash分区的名字,因为数据放在哪个分区是oracle根据bash算法存放的,并不是用户指定,
所以当用户插入一条记录,并不能确定放在哪个分区,这个不同于range和list

--直接创建唯一性索引。

3、复合分区
说明:顾名思义,复合分区就由range+list+hash两两组合而来,一般分为range+list,list+range,range+hash,list+bash,这里指列出几种常用组合。
规则:如果组合中存在hash,皆把hash分区作为子分区,原因大家可以通过hash分区的性质知道。
例一、range+list,这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区。

     语法为:CREATE UNIQUE INDEX index_name on table_name(col1,col2,…);

CREATE TABLE PART_TAB_SALE_RANGE_LIST 
( 
  PRODUCT_ID VARCHAR2(5), 
  SALES_DATE DATE, 
  SALES_COST NUMBER(10), 
  STATUS VARCHAR2(10),
) 
PARTITION BY RANGE(SALES_DATE) 
  SUBPARTITION BY LIST (STATUS) 
( 
  PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE part_Data1 
  ( 
    SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1, 
    SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
    SUBPARTITION P1SUB3 VALUES (DEFAULT) TABLESPACE part_Data3
  ), 
  PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE part_Data2 
  ( 
    SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1, 
    SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
    SUBPARTITION P2SUB3 VALUES (DEFAULT) TABLESPACE part_Data3 
  ),
  PARTITION P3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3
  ( 
    SUBPARTITION P3SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1,
    SUBPARTITION P3SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
    SUBPARTITION P3SUB3 VALUES (DEFAULT) TABLESPACE part_Data3
  ) 
) 

--创建主键约束时自动创建唯一性索引。

例二、range+bash,这种分区是基于范围分区和散列分区,表首先按某列进行范围分区,然后再按某列进行散列分区。

     语法为:ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(col1,col2,..);

partition by range(transaction_date)
subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)  
(partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
);

--创建唯一性约束时自动创建唯一性索引。

三、分区表的操作
1.DML操作
说明:DML操作和平常的表一样,有异于的地方是增加了可以指定表的特定分区才执行DML操作。

     语法为:ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(col1,col2,…);

例如,查询分区表

 

SELECT * FROM PART_TAB_SALE_RANGE_LIST;--不指定分区直接查询
SELECT * FROM PART_TAB_SALE_RANGE_LIST PARTITION(P2);--指定分区查询
SELECT * FROM PART_TAB_SALE_RANGE_LIST SUBPARTITION(P1SUB2);--指定小分区查询

这里,我总结了3套方案来对应ORA-01502问题。

对于分区表,指定分区执行DML效率更高,但,如果指定了分区,而条件中的数据又不在该分区中时,将不会产生任何DML操作。

(3.2)方案一:删除唯一性索引

2.DDL操作
1)添加分区
(1)对range分区表添加分区
ALTER TABLE PART_TAB_SALE_RANGE_LIST ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2009-06-01','YYYY-MM-DD')); 
注意:增加一个分区的时候,增加的分区的条件必须大于现有分区的最大值,否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。
(2)对range分区表list子分区添加分区
ALTER TABLE PART_TAB_SALE_RANGE_LIST MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE'); 

与业务方面沟通,确认唯一性索引是否可以删除,如果可以,直接删除索引,删除语法为:

2)删除分区
(1)对range分区表删除分区
ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP PARTITION P3; 
(2)对range分区表list子分区删除子分区
ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP SUBPARTITION P4SUB1; 

SQL> DROP INDEX schema.index_name;

注意:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表。

对于由主键约束或唯一性约束创建的唯一性索引,不能直接删除

3)截断分区
说明:截断某个分区是指清空某个分区中的数据,并不会删除分区,也不会删除其它分区中的数据。当表中即使只有一个分区时,也可以截断该分区。
注意:如果截断的分区表有约束,需要先关闭约束。alter table sales disable/enable constraint restraint_name,截断分区会使全局索引无效,需要重建。
(1)清空分区:
   ALTER TABLE SALES TRUNCATE PARTITION P2;--这种方式使全局分区索引无效
   ALTER TABLE SALES TRUNCATE PARTITION P2 update indexes;--这种方式使全局分区索引有效UPDATE GLOBAL INDEXES

SQL> drop index lijiaman.sale_pk;drop index lijiaman.sale_pkORA-02429: cannot drop index used for enforcement of unique/primary key

(2)清空子分区:
   ALTER TABLE PART_TAB_SALE_RANGE_LIST TRUNCATE SUBPARTITION P2SUB2; 
   ALTER TABLE PART_TAB_SALE_RANGE_LIST TRUNCATE SUBPARTITION P2SUB2 update indexes;

正确的方法是删除相应的约束。

4)合并分区
说明:合并分区是将相邻的分区合并成一个分区,结果分区将采用较高分区的界限,值得注意的是,不能将分区合并到界限较低的分区。
ALTER TABLE PART_TAB_威尼斯网站,SALE_RANGE_LIST MERGE PARTITIONS P1,P2 INTO PARTITION P2; 
注意:在本例中将原有的表的part_01分区和part_02分区进行了合并,合并后的分区为part_02,
如果在合并的时候把合并后的分区定为part_01的时候,系统将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。

SQL> alter table sales drop constraint sale_pk;Table altered

5)拆分分区
说明:拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE PART_TAB_SALE_RANGE_LIST SBLIT PARTITION P2 AT(TO_DATE('2003-03-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22); 

小结:该方法简单粗暴,前提是在约束或索引在业务方面可以删除的情况下才能使用。

6)接合分区
说明:接合分区是将散列分区中的数据接合到其它分区中,当散列分区中的数据比较大时,可以增加散列分区,然后进行接合,
值得注意的是,接合分区只能用于散列分区中。
ALTER TABLE PART_TAB_SALE_RANGE_LIST COALESCA PARTITION;

(3.3)方案二:重建唯一性索引(针对非分区表)

7)重命名表分区
ALTER TABLE SALES RENAME PARTITION P21 TO P2;

语法为:

8)移动分区
说明:把分区移动到令一个表空间,移动后要重建索引
alter table sales move partiton sp1 tablespace tablespace_name;
alter index index_name rebuild;

SQL> ALTER INDEX [schema.]index_name REBUILD [ONLINE] [TABLESPACE tablespace name]

--查询是否移动成功 SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';

小结:该方法可以使索引可用。但对于分区表而言,依然存在问题:在下一次删除分区后,索引状态又会变为不可用。

四、表分区相关的数据字典表
--显示表分区信息 显示数据库所有分区表的详细分区信息: select * from DBA_TAB_PARTITIONS
--显示子分区信息 显示数据库所有组合分区表的子分区信息: select * from DBA_TAB_SUBPARTITIONS
--显示数据库所有分区表的信息: select * from DBA_PART_TABLES
--显示数据库可访问的所有分区表的分区列信息:select * from DBA_PART_KEY_COLUMNS 

(3.4)方案三:删除不可用的索引,创建唯一性分区索引(针对分区表)

DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS

创建唯一性分区索引:

--查询索引信息
select object_name,object_type,tablespace_name,sum(value) 
from v$segment_statistics 
where statistic_name IN ('physical reads','physical write','logical reads') and object_type='INDEX' 
group by object_name,object_type,tablespace_name 
order by 4 desc 

SQL> CREATE UNIQUE INDEX index_name on [schema.]table(col1,col2,...);

五、局部索引与全局索引
表可以按range,hash,list分区,表分区后,其上的索引和普通表上的索引有所不同,oracle对于分区表上的索引分为2类,即局部索引和全局索引。

对于主键约束、唯一性约束,可以使用以下语法添加唯一性局部分区索引:

关于由Oracle分区表引出的局部索引与全局索引,我们将在“Oracle 索引”中做详细讲述

SQL> ALTER TABLE [schema.]table_name ADD CONSTRAINT constarint [PRIMARY KEY | UNIQUE](col1,col2)  USING INDEX LOCAL TABLESPACE tablespace_name;

小结:该方法可以有效解决分区表因删除分区导致的索引不可用问题。

 

 

附录:模拟实验

(1)首先模拟生产情况,创建一张表:

create table sales( prod_id number, cust_id number, time_id date, quantity_sold number(3))partition by range(time_id)( partition sales_q1_2017 values less than(to_date('1-4-2017','dd-mm-yyyy')) , partition sales_q2_2017 values less than(to_date('1-7-2017','dd-mm-yyyy')) , partition sales_q3_2017 values less than(to_date('1-10-2017','dd-mm-yyyy')) , partition sales_q4_2017 values less than(to_date('1-1-2018','dd-mm-yyyy')));

插入数据,确保每个分区都有数据

insert into sales(prod_id,cust_id,time_id,quantity_sold)values(1,11,to_date('2017-02-01','yyyy-mm-dd'),103);insert into sales(prod_id,cust_id,time_id,quantity_sold)values(2,12,to_date('2017-06-01','yyyy-mm-dd'),103);insert into sales(prod_id,cust_id,time_id,quantity_sold)values(3,14,to_date('2017-08-01','yyyy-mm-dd'),103);insert into sales(prod_id,cust_id,time_id,quantity_sold)values(4,14,to_date('2017-12-01','yyyy-mm-dd'),103);

检查一下数据库的数据信息

本文由威尼斯游戏网站发布于计算机数据,转载请注明出处:威尼斯网站Oracle分区表删除分区引发错误ORA

关键词:

上一篇:没有了

下一篇:没有了

最火资讯