Oracle 分区表自动维护脚本

--create base table
create table PART_T_MAINTENANCE
(
table_owner varchar2(20),
table_name varchar2(40),
partition_name varchar2(40),
TABLESPACE_NAME varchar2(20),
PERIOD_TIME number,
create_time varchar2(20) default to_char(sysdate,'yyyymmddhh24miss|sssss')
)
tablespace DMS_DEV_DAT
/

--modify the base table for all of partition table require maintenance automaticly
insert into PART_T_MAINTENANCE
  select distinct table_owner,
         table_name,
         '',--partition_name,
         '',--tablespace_name,
         12,
         to_char(sysdate, 'yyyymmddhh24miss|sssss')
    from dba_tab_partitions
   where table_owner not in ('SYS', 'SYSTEM')  
     and table_owner in ('TEST', 'TNTEST') 
     and table_name not like 'BIN%';

commit;

--update partition table retention period policy
update part_t_maintenance set period_time=3 where table_name in ('T_PARTITION');