清理分区

背景：

分区表按照月进行分区，定期删除分区清理数据，记录操、操作方案

1、查询分区情况

select a.TABLE_OWNER,a.TABLE_NAME,a.PARTITION_NAME,b.column_name,a.HIGH_VALUE from dba_tab_partitions a,dba_part_key_columns b where a.table_name='tablenameXXX_XX' and a.table_owner='XXX_XX' and a.table_owner=b.owner and a.table_name=b.name;

 

TABLE_OWNE TABLE_NAME        PARTITION_NAME  COLUMN_NAME          HIGH_VALUE

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

XXX_XX  tablenameXXX_XX P0              INSERTTIMEFORHIS     TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P118843     INSERTTIMEFORHIS     TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P120223     INSERTTIMEFORHIS     TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P121762     INSERTTIMEFORHIS     TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P123543     INSERTTIMEFORHIS     TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P124906     INSERTTIMEFORHIS     TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P126762     INSERTTIMEFORHIS     TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P128402     INSERTTIMEFORHIS     TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P129902     INSERTTIMEFORHIS     TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P131642     INSERTTIMEFORHIS     TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P133204     INSERTTIMEFORHIS     TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P137863     INSERTTIMEFORHIS     TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

XXX_XX  tablenameXXX_XX SYS_P142202     INSERTTIMEFORHIS     TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

2、查询表上索引情况

SQL> select index_name,status,GLOBAL_STATS from dba_indexes where table_name='tablenameXXX_XX';

INDEX_NAME                     STATUS   GLO

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

PK_QT_PRXXX             VALID    YES

3、清理分区数据并且重建索引

alter table tablenameXXX_XX drop partition SYS_P133204 update global indexes

{
plan：
1、alter table 表名 drop partition 分区名;
2、alter index 全局索引名  rebuild online parallel 8；
3、alter index 全局索引名 noparallel;
}

4、确认索引状态

select index_name,status,GLOBAL_STATS from dba_indexes where table_name='tablenameXXX_XX';