分区表维护的常用命令：
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/，如需转载，请注明出处，否则将追究法律责任。
