分区表之split拆分分区实验

1.模拟环境

create table test

( seq  number not null,

  id int,

  constraint test_seq primary key(seq)

  )

  partition by range(seq)

  ( partition test_part1 values less than(10000) tablespace users,

    partition test_part2 values less than(20000) tablespace users,

    partition test_part3 values less than(30000) tablespace users,

    partition test_part4 values less than(40000) tablespace users,

    partition test_max values less than (maxvalue) tablespace users

  );

 

create sequence seq_test

 increment by 1000

 start with 1

 nomaxvalue

 nocycle

 nocache;

 

insert into test values(seq_test.nextval,1);

insert into test values(seq_test.nextval,2);

select * from test;

commit;

 

select * from dba_tab_partitions where table_name='TEST';

select count(*) from test partition(test_part1);

select count(*) from test partition(test_part2);

select count(*) from test partition(test_part3);

select count(*) from test partition(test_part4);

select count(*) from test partition(test_max);

 

 

----准备批量导入数据

begin

  for i in 3 .. 1000 loop

    insert into test values (seq_test.nextval, i);

  end loop;

  commit;

end;

 

 

SELECT COUNT(*) FROM test;

         1000

 

此时test_part1 共10条数据

此时test_part2 共10条数据

此时test_part3 共10条数据

此时test_part4 共10条数据

此时test_max 共960条数据

 

如：

select * from test partition(test_part1);

1       1       1

2       1001         2

3       2001         3

4       3001         4

5       4001         5

6       5001         6

7       6001         7

8       7001         8

9       8001         9

10     9001         10

 

 

select min(seq) from test partition(test_max);

1       40001

select max(seq) from test partition(test_max);

1       999001

 

----截断分区1、2

alter table test truncate partition(test_part1);

alter table test truncate partition(test_part2);

 

 

2.开始工作(split 拆分分区)

----背景说明：  分区表中分区1和分区2没有数据，分区3、4和max分区有数据，目前分区max数据太多。既然分区1和分区2已经没有数据，可以将分区1和分区2删除，删除后，再将max分区的数据拆分到新的分区5中

 

----查看TEST表的分区情况

set linesize 999

set pagesize 999

col TABLE_NAME for a20

col PARTITION_NAME for a20

col HIGH_VALUE for a10

col TABLESPACE_NAME for a10

 

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='TEST';

TABLE_NAME      PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

TEST                 TEST_MAX             MAXVALUE                   8 USERS

TEST                 TEST_PART1           10000                      5 USERS

TEST                 TEST_PART2           20000                      5 USERS

TEST                 TEST_PART3           30000                      5 USERS

TEST                 TEST_PART4           40000                      5 USERS

 

 

----因为有maxvalue区，直接添加分区是不成功的

SQL> alter table test add partition t_part5 values less than (50000);

alter table test add partition t_part5 values less than (50000)

                               *

ERROR at line 1:

ORA-14074: partition bound must collate higher than that of the last partition

 

----需要使用split拆分

SQL> alter table test split partition test_max at (50000) into (partition t_part5,partition test_max);

Table altered.

 

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='TEST';

 

TABLE_NAME           PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

TEST                 TEST_MAX             MAXVALUE                   8 USERS

TEST                 TEST_PART1           10000                      5 USERS

TEST                 TEST_PART2           20000                      5 USERS

TEST                 TEST_PART3           30000                      5 USERS

TEST                 TEST_PART4           40000                      5 USERS

TEST                 T_PART5              50000                      5 USERS

6 rows selected.

 

SQL> select count(*) from test partition(t_part5);

  COUNT(*)

----------

        10

SQL> select count(*) from test partition(test_max);

  COUNT(*)

----------

       950

----此时max分区少了10条数据

 

 

----删除test_part1分区

SQL> alter table test drop partition test_part1;

Table altered.

 

----此时无法新插入数据，因为索引失效了，需要重建索引

SQL> insert into test values('102',100);

insert into test values('102',100)

*

ERROR at line 1:

ORA-01502: index 'SYS.TEST_SEQ' or partition of such index is in unusable state

 

----查看TEST表索引是否失效了

SQL>  select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='TEST';

INDEX_NAME        INDEX_TYPE                  TABLESPACE  TABLE_TYPE  STATUS

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

TEST_SEQ               NORMAL            SYSTEM     TABLE       UNUSABLE

 

----重建索引

SQL> alter index TEST_SEQ rebuild online;

Index altered.

 

SQL>  select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='TEST';

INDEX_NAME      INDEX_TYPE                  TABLESPACE   TABLE_TYPE  STATUS

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

TEST_SEQ          NORMAL                      SYSTEM     TABLE       VALID

 

 

----再次插入数据成功

SQL> insert into test values('102',100);

1 row created.

 

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='TEST';

TABLE_NAME   PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

TEST                 TEST_MAX             MAXVALUE                   8 USERS

TEST                 TEST_PART2           20000                      5 USERS

TEST                 TEST_PART3           30000                      5 USERS

TEST                 TEST_PART4           40000                      5 USERS

TEST                 T_PART5              50000                      5 USERS

 

SQL> select count(*) from test partition(test_part2);

  COUNT(*)

----------

         1

 

----添加test_part1分区

----直接添加test_part1分区是失败的

SQL> alter table test add partition test_part1 values less than(10000) tablespace users;

alter table test add partition test_part1 values less than(10000) tablespace users

                               *

ERROR at line 1:

ORA-14074: partition bound must collate higher than that of the last partition

 

----需要使用split拆分分区方式添加test_part1分区

SQL> alter table test split partition test_part2 at (10000) into (partition test_part1,partition test_part2);

Table altered.

 

----使用拆分后，索引没有失效。这时test_part2分区的那条数据跑到了test_part1分区中

SQL> select * from test partition(test_part2);

no rows selected

 

SQL> select * from test partition(test_part1);

       SEQ         ID

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

       102        100

 

 

----查看分区表子分区所在表空间

select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='TEST';

TABLE_NAME           PARTITION_NAME       TABLESPACE

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

TEST                 TEST_MAX             USERS

TEST                 TEST_PART1           USERS

TEST                 TEST_PART2           USERS

TEST                 TEST_PART3           USERS

TEST                 TEST_PART4           USERS

TEST                 T_PART5              USERS

6 rows selected.

 

SQL> select table_name,partition_name,tablespace_name from dba_tab_partitions where table_name='TEST' and table_owner='SYS';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2123156/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2123156/