注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

如何删除间隔分区的某个历史分区  

2012-10-15 09:33:13|  分类: 分区表 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

这个题目也可以这么说:根据间隔分区的某条记录确定该记录属于哪个分区.
间隔分区可以根据插入的记录自动添加分区,给维护分区表带来了很大方便。下面是一个按月建立的间隔分区:
create table interval_partition(c1 number,c2 date)
partition by range(c2)
interval(numtoyminterval (1,'month'))
(partition part2012_01 values less than (to_date('2012-02-01','yyyy-mm-dd')));

但遗憾的是系统生成的分区名不可控,都是sys_p65这样的格式。根据分区名,我们无法猜出该分区存放的是哪部分数据。
假如我们使用了间隔分区,定期做历史数据清理时,就需要删除某个历史分区,怎么知道哪个分区是最早的分区呢?下面给出了两种方法。

首先,建立了一个每天一个分区的间隔分区表,开始测试:
SQL> create table interval_test
  2  (id number,operate_time date)
  3  partition by range(operate_time)
  4  interval(numtodsinterval (1,'day'))
  5  (partition p20121001 values less than (to_date('2012-10-02','YYYY-MM-DD')));
 
Table created

SQL> insert into interval_test values(1,sysdate);

1 row inserted

SQL> insert into interval_test values(1,sysdate-1);

1 row inserted

SQL> insert into interval_test values(1,sysdate-2);

1 row inserted

SQL> insert into interval_test values(1,sysdate-3);

1 row inserted

SQL> commit;

查询该表有哪些分区:
SQL> select partition_name,high_value from user_tab_partitions where table_name='INTERVAL_TEST'; 
PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P20121001                      TO_DATE(' 2012-10-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P44                        TO_DATE(' 2012-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P43                        TO_DATE(' 2012-10-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42                        TO_DATE(' 2012-10-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41                        TO_DATE(' 2012-10-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
这个分区表中,虽然分区名中SYS_P后面的数字是逐渐增大的(从21到24),但由于我们插入的数据不是按照日期渐增插入的,所以根据分区名不好判断一条记录属于哪个分区。
系统表user_tab_partitions中有个high_value列,貌似根据这一列判断出某个分区存放的是哪部分数据,但遗憾的是high_value列是long型,varchar2的N多用都它无法使用。
经人提醒,用游标可以把long类型数据取出来,取出来之后就可以当varchar2使用了,试了一下,果然可以,如下:
删除两天前的历史分区:
SQL> set serveroutput on
SQL> begin
  2  for rec in (select partition_name,high_value from user_tab_partitions where table_name='INTERVAL_TEST') loop
  3  execute immediate 'alter session set nls_date_format=''yyyy-mm-dd''';
  4  if substr(rec.high_value,11,10)=to_char(sysdate-1) then
  5  --to_char(sysdate-1)为某条记录中operate_time列的时间,可以根据某条记录查某个历史分区,也可以查两个天前是哪个分区
  6  dbms_output.put_line(rec.partition_name);
  7  execute immediate 'alter table interval_test drop partition '||rec.partition_name ||' update global indexes';
  8  end if;
  9  end loop;
 10  end;
 11  /
 
SYS_P43
 
PL/SQL procedure successfully completed

查看两天前的历史分区是否被删除:
SQL> select partition_name,high_value from user_tab_partitions where table_name='INTERVAL_TEST';
 
PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P20121001                      TO_DATE(' 2012-10-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P44                        TO_DATE(' 2012-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42                        TO_DATE(' 2012-10-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41                        TO_DATE(' 2012-10-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

可以看到SYS_P43已经被删除了。

注意:间隔分区中人工建的第一个分区,这里是P20121001,不管有没有值,系统都不允许删除,如下测试:
SQL> alter table interval_test drop partition P20121001;
 
alter table interval_test drop partition P20121001
 
ORA-14758: 不能删除范围段中的最后一个分区

 

个人感觉以上方法是最好的查找历史分区的方法了,不过以下也是一个查历史分区的方法:

从dba_extents中可以查出每个分区的的块头:
SQL> select partition_name,block_id from dba_extents where owner='SCOTT' and segment_name='INTERVAL_TEST';
 
PARTITION_NAME                   BLOCK_ID
------------------------------ ----------
SYS_P44                              7936
SYS_P42                              7784
SYS_P41                              7776
P20121001                            7768
注意:dba_extents有block_id列,user_extents中却没有该列。

根据interval_test表每一行的rowid,可以直接查出每一行所在的块号及每一行所在的区的第一个块号。
SQL> select id,operate_time,dbms_rowid.rowid_block_number(rowid) block_id,trunc(dbms_rowid.rowid_block_number(rowid)/8)*8 extent_block_id from interval_test;
 
        ID OPERATE_TIME   BLOCK_ID EXTENT_BLOCK_ID
---------- ------------ ---------- ---------------
         1 2012-10-9 21       7939            7936
         1 2012-10-11 2       7787            7784
         1 2012-10-12 2       7779            7776
可以看出EXTENT_BLOCK_ID 跟dba_extents查出的BLOCK_ID数值是一致的。这两张表一关联,就可以确定出每一行所在的分区名,如下:
SQL> select id,operate_time,partition_name,block_id,extent_block_id from
  2  (select partition_name,block_id from dba_extents where owner='SCOTT' and segment_name='INTERVAL_TEST') a,
  3  (select id,operate_time,trunc(dbms_rowid.rowid_block_number(rowid)/8)*8 extent_block_id from interval_test) b 
  4  where a.block_id=b.extent_block_id;
--在第三行interval_test后加个operate_time的范围,可以让查询范围更小,执行更快。
 
        ID OPERATE_TIME PARTITION_NAME                   BLOCK_ID EXTENT_BLOCK_ID
---------- ------------ ------------------------------ ---------- ---------------
         1 2012-10-9 21 SYS_P44                              7936            7936
         1 2012-10-11 2 SYS_P42                              7784            7784
         1 2012-10-12 2 SYS_P41                              7776            7776

根据某条记录OPERATE_TIME列的时间就可以确定出该记录所在的分区,也就可以确定要删除哪个分区了。

  评论这张
 
阅读(684)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018