分区表部分分区数据导出

最近有一个需求,随着业务的不断发展,每天备份的dmp越来越大,占用了很多的磁盘空间。
看了下备份的log,发现排在第一个的居然是一个日志log表,记录系统中的每一步操作。这个表只是单纯做查询使用,而且使用频率也极低,除非为了查询某些特殊操作是何人所为的情况。数据记录一旦形成就不会在改变。既然如此,重复的备份该表的历史部分也就没有多大意义。
于是考虑将该表改为分区表,设置2个分区,一个分区存储最近三个月的数据,另一个分区存储三个月以前的数据。并将历史数据存入另一个表空间,将表空间设置为只读。

开干:

  1. 新建一个表空间olddata,用于存储历史的不再改变的数据。
  2. 在业务停止的时候,创建一个分区表。
create table C_MONITORLOG_TMP
(
  logid    ,
  qname    ,
  lx       ,
  category ,
  type     ,
  lasttime ,
  datasize ,
  param    ,
  name     ,
  created  default sysdate  ,
  createby ,
  updated  default sysdate  ,
  updateby ,
  orgid    ,
  note     
)
partition by range (CREATED)
(
  partition P1 values less than (TO_DATE('2017-07-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ))
    tablespace OLDDATA,
  partition PMAX values less than (MAXVALUE)
    tablespace USERS
)
as
select * from C_MONITORLOG;
  1. 确认数据无误后,将C_MONITORLOG drop掉。
  2. 修改 C_MONITORLOG_TMP 表名为 C_MONITORLOG。
  3. 备份oldata表空间数据,将C_MONITORLOG:P1 数据进行一次单独备份。
expdp system/xxxxxx directory=expdpdir dumpfile=c_monitorlog_p1.dmp  tables=longyyy.C_MONITORLOG:P1
这个备份需要保存好,以后不会再每天对这个分区进行备份了。
如果对分区数据进行了修改和合并,则必须重新备份该分区。

编辑dmp导出脚本,增加过滤选项将p1的数据分区数据过滤掉。

expdp system/xxxxxx directory=expdpdir dumpfile=test.dmp  exclude=table:\"in\(\'C_MONITORLOG:P1\'\)\"
 
然而exclude 并不能对表的分区进行排除。如果这么写,还是会将C_MONITORLOG进行全表导出。
怎么办呢,应该使用exclude=table_data 来对表分区进行排除。

expdp system/xxxxxx directory=expdpdir dumpfile=test.dmp tablespaces=users exclude=table_data:\"in  (select partition_name from dba_tab_partitions where table_name=\'C_MONITORLOG\' and partition_name<>\'PMAX\')\"

通过上面的语句,就可以将C_MONITORLOG表中除PMAX的分区全部排除掉。

最后,每三个月对PMAX分区的数据进行一次切割,然后合并到P1分区里面。这样,我们每次备份到处的dmp就不需要保存那么多重复不变的历史日志了。

--查看表的分区
select * from dba_tab_partitions
where table_name='C_MONITORLOG';

--分区拆分
alter table C_MONITORLOG split partition  PMAX at (to_date('2017/10/01','yyyy/mm/dd'))
into (partition P2  tablespace olddata, partition PMAX);

--分区合并
--底部分区不能作为新的合并分区。
alter table C_MONITORLOG merge partitions p1,p2 into partition p2;

--分区改名
alter table C_MONITORLOG rename partition p2 to p1;

--分区移动,可以在不同表空间之间移动。
alter table  C_MONITORLOG move partition p1  tablespace olddata;

最后,将olddata表空间设置为read only,直到下次需要操作时再打开read write
alter tablespace olddata read only;
alter tablespace olddata read write;

推荐阅读更多精彩内容