ORACLE|ORACLE 历史数据管理策略--数据清理 ORACLE历史数据管理策略--数据清

背景由于性能数据每天导入量，数据库表空间每天增长很快，且不需要太长的保存周期，为避免爆表，因此需要定制定期清理计划。
数据的清理可以有多种方案，根据场景的不同可以分为离线，在线。后续又在可以细分。这里仅考虑在线方式数据里比如DELETE与 REDEFINITION，这种方式带来的问题就是会产生大量的LOG,同时产生回滚段，需要定期进行redefinition。为避免场景复杂，这里采用分区表方式。
分区方案目前有两种方案，一种是按照ingerval分区，未定义分区oracle会智能分区，分区简单，但是带来的问题就是分区名字无法直接确定，后期维护不方便
这里不做重点介绍
使用虚拟列，固定分区名字，引入问题需要新增虚拟列，即本文使用方案。
关于索引
表分区以后，同时需要同步修改索引，这里根据我们的应用场景，需要构建LNP(LOCAL NON PREFIXED) INDEX--引入的虚拟列作为分区字段，没有其它功能。
如果需要构建唯一索引，LNP index必须包含分区键。
对于程序访问路径带来的变化就是最好显式的指定分区，如果不指定，即使匹配索引，也是匹配所有表的LNP IDNEX

select INDEX_NAME,PARTITIONING_TYPE,LOCALITY, ALIGNMENT from all_part_indexes where table_name='xxx' select index_name,status from user_indexes where index_name='xxx' select INDEX_NAME,PARTITION_NAME,status from User_Ind_Partitions a where a.Index_Name='xxx'

新增虚拟列新增虚拟列语法

v_month as (substr(datadate,6,2)) partition by list(v_month) ( partition p1 values('01'), partition p2 values('02'), partition p3 values('03'), partition p4 values('04') );

新增虚拟列不会增加存储空间消耗，但是会增加CPU消耗，即新增列的信息仅写入metadata.

SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM user_tab_partitions WHERE TABLE_NAME= select TABLE_NAME,PARTITIONING_TYPEfrom user_part_tables where table_name=' select segment_name||' '||partition_name||' '||segment_type from user_segments where segment_name like

应用程序变化
SELECT

SELECT *

会现实虚拟列
INSERT 不支持

insert into table xx values()

需要显式指定插入列：

insert into table xx(col1,col2,...) values()

update 同insert
按月份分区数据清理表按照月分区，共12个分区，数据保留3个月，每个月出清理三个月之前的分区数据，即清理脚本每月执行
生成truncate分区的脚本如下：

fromdatetime import date,timedelta from monthdelta import MonthDelta current_day = date.today() prev_2month = current_day- MonthDelta(2) month_of_partition = prev_2month.month print 'current day is:{0} and previous day of last 2 months is:{1},so the partition need to truncate is:{2}'.format(current_day,prev_2month,month_of_partition) with open("partition_by_day_table") as f: for table in f: print 'alter table {0} truacate partition p{1}'.format(table.strip(),month_of_partition)

确定分区后，通过定时任务执行对应的SQL即可。
按天分区数据清理表按照天分区，数据至少保留7天以上
表分区原则：表按天分区，共31个分区，每天清理8天前的分区，清理脚本每月执行
生成truncate分区的脚本如下：

#!/usr/bin/python fromdatetime import date,timedelta,datetime current_day = date.today() prev_8day = current_day-timedelta(days=8) day_of_partition = prev_8day.day print 'current day is: {0}andprevisus day of 8 day is:{1},so the partition need to trucate is:{2}'.format(current_day,prev_8day,day_of_partition) print '#'*72 fout=open('/home/oracle/scripts/minute.log','a') with open("/home/oracle/scripts/partition_by_day_tables") as f: for table in f: syntax= 'alter table {0} truacate partition p{1}'.ljust(72,' ').format(table.strip(),day_of_partition)+'; commit; \n' #print syntax fout.write(syntax) now=datetime.now().strftime('%Y-%m-%d %H:%M:%S') fout.write(now+'\n') f.close() print '#'*72

对应的SQL脚本如下：