- 删除分区
ALTER TABLE table_name DROP PARTITION partition_name;
ALTER INDEX table_name REBUILD(如果含有全局索引);
- 增加分区
ALTER TABLE table_name ADD PARTITION partition_name VALUES LESS THAN (TIMESTAMP' 2017-10-31 00:00:00');
- 合并分区
ALTER TABLE table_name MERGE PARTITIONS partition_1, partition_2 INTO PARTITION partition_2;
ALTER TABLE table_name MODIFY PARTITION partition_2 REBUILD UNUSABLE LOCAL INDEXES;
- 重命名分区
ALTER TABLE table_name RENAME PARTITION partition_1 TO partition_1;
- 分区拆分
ALTER TABLE table_name SPLIT PARTITION
PART_1 at
(TO_DATE(' 1994-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
INTO ( PARTITION PART_1 tablespace st1,
PARTITION PART_3 tablespace users);
- truncate分区
ALTER TABLE table_name TRUNCATE PARTITION partition_name;
ALTER INDEX index_name REBUILD;
分区字段更新
- 原则上不允许手动更新分区字段,row movement策略如果是disabled的状态,不能跨分区更新分区字段
select table_name,owner,row_movement from dba_tables where table_name = 'table_name';
- 强制更新分区字段
-- 修改该策略
alter table schema.table_name enable row movement;
-- 执行语句
--- 要执行更新的分区表
-- 修改回来
alter table schema.table_name disable row movement;
分区操作前做失效索引操作
ALTER TABLE table_name DISABLE CONSTRAINT ipx_name;
ALTER TABLE table_name TRUNCATE PARTITTION partition_name;
-- 恢复索引
ALTER TABLE table_name ENABLE CONSTRAINT ipx_name;
- 分区表信息查询
select utp.table_name,utp.partition_name,utp.tablespace_name from user_tab_partitions utp
where utp.table_name='表名';
- 分区信息和索引信息聚合
SELECT INDEX_NAME, NULL PARTITION_NAME, TABLESPACE_NAME, STATUS
FROM USER_INDEXES
WHERE TABLE_NAME = '表名'
AND PARTITIONED = 'NO'
UNION
SELECT INDEX_NAME, PARTITION_NAME, TABLESPACE_NAME, STATUS
FROM USER_IND_PARTITIONS
WHERE INDEX_NAME IN (SELECT INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = '表名')
ORDER BY 1, 2, 3;