Oracle带局部分区索引的分区表删除旧分区添加新分区

 

为了方便测试，所有的实验步骤都用sys用户操作。

 

1 创建四个数据表空间（分别放四个季度的数据）

SQL> create tablespace QUR_DT01 logging datafile '/oracle/app/oradata/mytablespace/QUR_DT01.dbf' size 128M autoextend on next 100M maxsize 1024M extent management local;

表空间已创建。

 

SQL> create tablespace QUR_DT02 logging datafile '/oracle/app/oradata/mytablespace/QUR_DT02.dbf' size 128M autoextend on next 100M maxsize 1024M extent management local;

表空间已创建。

 

SQL> create tablespace QUR_DT03 logging datafile '/oracle/app/oradata/mytablespace/QUR_DT03.dbf' size 128M autoextend on next 100M maxsize 1024M extent management local;

表空间已创建。

 

SQL> create tablespace QUR_DT04 logging datafile '/oracle/app/oradata/mytablespace/QUR_DT04.dbf' size 128M autoextend on next 100M maxsize 1024M extent management local;

表空间已创建。

 

2 创建四个索引表空间

SQL> create tablespace QUR_IDX01 logging datafile '/oracle/app/oradata/mytablespace/QUR_IDX01.dbf' size 128M autoextend on next 100M maxsize 1024M extent management local;

表空间已创建。

 

SQL> create tablespace QUR_IDX02 logging datafile '/oracle/app/oradata/mytablespace/QUR_IDX02.dbf' size 128M autoextend on next 100M maxsize 1024M extent management local;

表空间已创建。

 

SQL> create tablespace QUR_IDX03 logging datafile '/oracle/app/oradata/mytablespace/QUR_IDX03.dbf' size 128M autoextend on next 100M maxsize 1024M extent management local;

表空间已创建。

 

SQL> create tablespace QUR_IDX04 logging datafile '/oracle/app/oradata/mytablespace/QUR_IDX04.dbf' size 128M autoextend on next 100M maxsize 1024M extent management local;

表空间已创建。

 

3 创建季度分区表

create table t(mydate varchar2(20), amount number) partition by range(mydate)

(

partition p20140101 values less than ('20140101') tablespace QUR_DT01,

partition p20140401 values less than ('20140401') tablespace QUR_DT02,

partition p20140701 values less than ('20140701') tablespace QUR_DT03,

partition p20141001 values less than ('20141001') tablespace QUR_DT04

);

 

验证：

SQL> SELECT tablespace_name,partition_name FROM dba_tab_partitions where table_name='T';

TABLESPACE_NAME                PARTITION_NAME

------------------------------ ------------------------------

QUR_DT01                       P20140101

QUR_DT02                       P20140401

QUR_DT03                       P20140701

QUR_DT04                       P20141001

 

4 对日期列创建局部分区索引

create index local_date_index on t(mydate) local

(

partition p20140101 tablespace QUR_IDX01,

partition p20140401 tablespace QUR_IDX02,

partition p20140701 tablespace QUR_IDX03,

partition p20141001 tablespace QUR_IDX04

);

索引已创建。

 

验证：

SQL> select segment_name,partition_name,tablespace_name from dba_segments where segment_name='LOCAL_DATE_INDEX';

SEGMENT_NAME         PARTITION_NAME       TABLESPACE_NAME

-------------------- -------------------- --------------------

LOCAL_DATE_INDEX     P20140101            QUR_IDX01

LOCAL_DATE_INDEX     P20140401            QUR_IDX02

LOCAL_DATE_INDEX     P20140701            QUR_IDX03

LOCAL_DATE_INDEX     P20141001            QUR_IDX04

 

5 删除第一个分区

SQL> alter table t drop partition p20140101;

表已更改。

 

验证分区表：

SQL> SELECT tablespace_name,partition_name FROM dba_tab_partitions where table_name='T';

TABLESPACE_NAME      PARTITION_NAME

-------------------- --------------------

QUR_DT02             P20140401

QUR_DT03             P20140701

QUR_DT04             P20141001

 

验证分区索引：

SQL> select segment_name,partition_name,tablespace_name from dba_segments where segment_name='LOCAL_DATE_INDEX';

SEGMENT_NAME         PARTITION_NAME       TABLESPACE_NAME

-------------------- -------------------- --------------------

LOCAL_DATE_INDEX     P20140401            QUR_IDX02

LOCAL_DATE_INDEX     P20140701            QUR_IDX03

LOCAL_DATE_INDEX     P20141001            QUR_IDX04

这里可以看出，表分区被删除之后，相应的索引分区也自动被删除。

 

6 添加一个分区并更新索引

SQL> alter table t add partition p20150101 values less than('20150101') tablespace QUR_DT01 update indexes (local_date_index(partition P20150101 tablespace QUR_IDX01));

表已更改。

 

验证分区表：

SQL> SELECT tablespace_name,partition_name FROM dba_tab_partitions where table_name='T';

TABLESPACE_NAME      PARTITION_NAME

-------------------- --------------------

QUR_DT02             P20140401

QUR_DT03             P20140701

QUR_DT04             P20141001

QUR_DT01             P20150101

 

验证分区索引：

SQL> select segment_name,partition_name,tablespace_name from dba_segments where segment_name='LOCAL_DATE_INDEX';

SEGMENT_NAME         PARTITION_NAME       TABLESPACE_NAME

-------------------- -------------------- --------------------

LOCAL_DATE_INDEX     P20140401            QUR_IDX02

LOCAL_DATE_INDEX     P20140701            QUR_IDX03

LOCAL_DATE_INDEX     P20141001            QUR_IDX04

LOCAL_DATE_INDEX     P20150101            QUR_IDX01