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_TYPE  from 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分區的腳本如下：

from  datetime 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
from  datetime 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}  and  previsus 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腳本如下：

alter table xx1 truacate partition p3                                 ; commit;
alter table xx2 truacate partition p3                                 ; commit;
alter table xx3 truacate partition p3                                 ; commit;
           

确定分區後，通過定時任務執行對應的SQL即可。

定時腳本

通過crontab定時任務完成

5 4 * * * --daily 
5 4 1 * * ---monthly