ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle分区测试

oracle分区测试

原创 Linux操作系统 作者:心飘 时间:2011-05-03 13:58:41 0 删除 编辑

分区表维护的常用命令:

ALTER TABLE
-- DROP -- PARTITION
-- ADD |
-- RENAME |
-- MODIFITY |
-- TRUNCATE |
-- SPILT |
-- MOVE |
-- EXCHANGE |

分区索引的常用维护命令:

ALTER INDEX
-- DROP -- PARTITION
-- REBUILD |
-- RENAME |
-- MODIFITY |
-- SPILT |
-- PARALLEL
-- UNUSABLE

相关的数据字典
USER/ALL/DBA_PART_TABLES
USER/ALL/DBA_PART_TINDEXES
USER/ALL/DBA_PART_KEY_COLUMNS
USER/ALL/DBA_TAB_PARTITIONS
USER/ALL/DBA_IND_PARTITIONS
USER/ALL/DBA_PART_COL_STATISTICS
USER/ALL/DBA_TAB_COL_STATISTICS
USER/ALL/DBA_PART_HISTOGRAMS
USER/ALL/DBA_TAB_HISTOGRAMS
USER/ALL/DBA_OBJECTS
USER/ALL/DBA_TABLES
USER/ALL/DBA_INDEXES
USER/ALL/DBA_TAB_COLUMNS

1. 创建测试使用分区表

create table test_part(id number,comm varchar(100)) partition by range(id) (partition p1 values less than (10),partition p2 values less than (20))

2. 插入测试数据

insert into test_part values(1,'aa')
insert into test_part values(2,'aa')
insert into test_part values(11,'aa')
insert into test_part values(15,'aa')

3.新增分区,存储大于20的id记录

alter table test_part add partition pmax values less than (maxvalue)

insert into test_part values(22,'aa')
insert into test_part values(25,'aa')
insert into test_part values(33,'aa')
insert into test_part values(35,'aa')
insert into test_part values(40,'aa')

4.分裂pmax分区,拆成多个子分区

alter table test_part split partition pmax at(40) into (partition p4,partition pmax)

 

5. exchange交换分区

create table testnormal as select * from test_part

alter table test_part exchange partition p2 with table testnormal

select * from testnormal

select * from test_part partition(p2)

6.创建分区索引

create index test_part_idx on test_part(id) local

SQL> select owner,table_name,index_name,partition_count from dba_part_indexes where table_name='TEST_PART';

OWNER                          TABLE_NAME                     INDEX_NAME                     PARTITION_COUNT
------------------------------ ------------------------------ ------------------------------ ---------------
MONITOR                        TEST_PART                      TEST_PART_IDX                                5

SQL> select index_owner,index_name,partition_name,status,tablespace_name from dba_ind_partitions where index_name='TEST_PART_IDX';

INDEX_OWNER                    INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ -------- ------------------------------
MONITOR                        TEST_PART_IDX                  P1                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P2                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P3                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P4                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  PMAX                           USABLE   INTERFACE

7. truncate partition,分区索引状态正常

SQL> alter table test_part truncate partition p2;

Table truncated.

SQL> select index_owner,index_name,partition_name,status,tablespace_name from dba_ind_partitions where index_name='TEST_PART_IDX';

INDEX_OWNER                    INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ -------- ------------------------------
MONITOR                        TEST_PART_IDX                  P1                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P2                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P3                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P4                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  PMAX                           USABLE   INTERFACE

8. drop partition,分区索引自动删除


SQL> alter table test_part drop partition p3;

Table altered.

SQL> select index_owner,index_name,partition_name,status,tablespace_name from dba_ind_partitions where index_name='TEST_PART_IDX';

INDEX_OWNER                    INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ -------- ------------------------------
MONITOR                        TEST_PART_IDX                  P1                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P2                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  P4                             USABLE   INTERFACE
MONITOR                        TEST_PART_IDX                  PMAX                           USABLE   INTERFACE

9.重命名分区

SQL> alter table test_part rename partition pmax to pnew;

Table altered.

SQL> select table_owner,table_name,partition_name from dba_tab_partitions  where table_name='TEST_PART';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MONITOR                        TEST_PART                      P1
MONITOR                        TEST_PART                      P2
MONITOR                        TEST_PART                      PNEW
MONITOR                        TEST_PART                      P4

10.维护分区会导致全局索引不可用

创建一个global的索引
SQL> create index test_part_g on test_part(id,comm);

Index created.

SQL> select table_owner,table_name,partition_name from dba_tab_partitions where table_name='TEST_PART';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MONITOR                        TEST_PART                      P1
MONITOR                        TEST_PART                      P2
MONITOR                        TEST_PART                      PNEW
MONITOR                        TEST_PART                      P4


SQL> select index_owner,index_name,partition_name,status from dba_ind_partitions where index_name='TEST_PART_IDX';

INDEX_OWNER                    INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ ------------------------------ --------
MONITOR                        TEST_PART_IDX                  P1                             USABLE
MONITOR                        TEST_PART_IDX                  P2                             USABLE
MONITOR                        TEST_PART_IDX                  P4                             USABLE
MONITOR                        TEST_PART_IDX                  PMAX                           USABLE


SQL> select owner,index_name,table_owner,table_name,status from dba_indexes where index_name='TEST_PART_G';

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
MONITOR                        TEST_PART_G                    MONITOR                        TEST_PART                      VALID


对分区表进行维护,truncate分区
SQL> alter table test_part truncate partition p1;

Table truncated.


分区索引状态正常
SQL>  select index_owner,index_name,partition_name,status from dba_ind_partitions where index_name='TEST_PART_IDX';

INDEX_OWNER                    INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ ------------------------------ --------
MONITOR                        TEST_PART_IDX                  P1                             USABLE
MONITOR                        TEST_PART_IDX                  P2                             USABLE
MONITOR                        TEST_PART_IDX                  P4                             USABLE
MONITOR                        TEST_PART_IDX                  PMAX                           USABLE


全局索引变为不可用了
SQL> select owner,index_name,table_owner,table_name,status from dba_indexes where index_name='TEST_PART_G';

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
MONITOR                        TEST_PART_G                    MONITOR                        TEST_PART                      UNUSABLE

11.合并分区

select table_owner,table_name,partition_name from dba_tab_partitions where table_name='TEST_PART';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MONITOR                        TEST_PART                      P3
MONITOR                        TEST_PART                      P4
MONITOR                        TEST_PART                      P5
MONITOR                        TEST_PART                      PMAX
MONITOR                        TEST_PART                      PNEW
SQL> select owner,index_name,table_owner,table_name,status from dba_indexes where index_name='TEST_PART_G';

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
MONITOR                        TEST_PART_G                    MONITOR                        TEST_PART                      VALID

合并分区p3,p4到分区p1中,同时更新global索引

SQL> alter table test_part merge partitions p3,p4 into partition p1 update global indexes;

Table altered.

SQL> select table_owner,table_name,partition_name from dba_tab_partitions where table_name='TEST_PART';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MONITOR                        TEST_PART                      P1
MONITOR                        TEST_PART                      P5
MONITOR                        TEST_PART                      PMAX
MONITOR                        TEST_PART                      PNEW

SQL> select owner,index_name,table_owner,table_name,status from dba_indexes where index_name='TEST_PART_G';

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
MONITOR                        TEST_PART_G                    MONITOR                        TEST_PART                      VALID

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

请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    29
  • 访问量
    118738