[原]给Oracle 11g Interval分区进行重命名

      Oracle 11g 众多新特性中,我最喜欢的就是分区表增强,众所周知很多大问题“化整为零”之后就不是个问题,分区表就是一种非常好用的“化整为零”的手段。

      还是说回正题吧,使用Interval分区不难,为分区/子分区的重命名也不难,我的分区表大致定义如下:

CREATE TABLE Partition_Table
(
  ....
  ....
  ....
)
PARTITION BY RANGE( MSGDATE ) INTERVAL( NUMTOYMINTERVAL(1,'MONTH') )
SUBPARTITION BY LIST( DAY_V )

      分区和子分区的重命名语法如下:

alter table <table_name> rename partition <partition_name> to <new_partition_name>;
alter table <table_name> rename subpartition <subpartition_name> to <new_subpartition_name>;

      每当新数据触发新建分区后,分区名字是系统给的,虽然不影响分区表的使用,但是看着很让人迷茫:

select 
  table_name ,
  partition_name,
  subpartition_name ,
  tablespace_name
from user_tab_subpartitions 
where subpartition_name like 'SYS%' ;
TABLE_NAME                     PARTITION_NAME  SUBPARTITION_NAME  TABLESPACE_NAME
------------------------------ --------------- ------------------ ------------------------------
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP100        FIREWALL16
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP101        FIREWALL17
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP102        FIREWALL18
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP103        FIREWALL19
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP104        FIREWALL20
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP105        FIREWALL21
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP106        FIREWALL22
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP107        FIREWALL23
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP108        FIREWALL24
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP109        FIREWALL25
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP110        FIREWALL26

TABLE_NAME                     PARTITION_NAME  SUBPARTITION_NAME  TABLESPACE_NAME
------------------------------ --------------- ------------------ ------------------------------
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP111        FIREWALL27
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP112        FIREWALL28
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP113        FIREWALL29
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP114        FIREWALL30
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP115        FIREWALL31
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP85         FIREWALL01
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP86         FIREWALL02
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP87         FIREWALL03
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP88         FIREWALL04
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP89         FIREWALL05
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP90         FIREWALL06

TABLE_NAME                     PARTITION_NAME  SUBPARTITION_NAME  TABLESPACE_NAME
------------------------------ --------------- ------------------ ------------------------------
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP91         FIREWALL07
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP92         FIREWALL08
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP93         FIREWALL09
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP94         FIREWALL10
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP95         FIREWALL11
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP96         FIREWALL12
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP97         FIREWALL13
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP98         FIREWALL14
P_MYSYSLOG_IL_2010             SYS_P116        SYS_SUBP99         FIREWALL15

      将分区/子分区的名字改成得有意义才是王道。以下是用一个过程配合游标来改分区名,重点是是从high_value字段获得该分区的范围描述,其他没什么了:

declare 
 v_sql varchar(400);
 v_table_name user_tab_partitions.table_name%type;
 v_partition_name user_tab_partitions.partition_name%type;
 v_high_value varchar(200);
 v_tmp_partition_name user_tab_partitions.partition_name%type;
 cursor cur is 
   select 
      table_name ,
      partition_name ,
      high_value 
    from user_tab_partitions 
    where partition_name like 'SYS%' ;
begin
  open cur;
  loop
    fetch cur into v_table_name,v_partition_name,v_high_value;
    exit when cur%notfound;
    v_tmp_partition_name := substr(v_high_value,11,10);
    v_tmp_partition_name := to_char( to_date(v_tmp_partition_name,'yyyy-mm-dd')-1 , 'yyyymm');
    v_sql := 'alter table '||v_table_name||' rename partition '
      ||v_partition_name
      ||' to P'||v_tmp_partition_name;
    dbms_output.put_line( v_sql );
    execute immediate v_sql;
  end loop;
  close cur;
end;
/

      由于改分区名属于DDL语句,对于忙碌的系统来说很容易遇到ORA-00054这个错误:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

      Oracle 11g 同时也引入了一个新特性——DDL Wait ,以前遇到这种情况要不就是找出那个该死的Transaction kill掉,要不就雇个人拼命坚持不懈地敲键盘跑这条DDL,直到成功执行,现在好了,这个人可以下岗了。只要设定ddl_lock_timeout这个参数就可以了,这个参数可以在实例级别和会话级别上设置,如果该值为0,遇到未提交事务时就会马上报ORA-00054,如果设定为10,DDL语句会为这个事务最多等10秒,10秒内事务提交,DDL语句就会执行成功,否则10秒后再报ORA-00054。

posted @ 2011-01-06 16:53  killkill  阅读(3603)  评论(0编辑  收藏