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

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

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

?

1 2 3 4 5 6 7 8

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

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

?

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

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

?

1 2 3 4 5 6 7

select   table_name ,   partition_name,   subpartition_name ,   tablespace_name from user_tab_subpartitions where subpartition_name like 'SYS%' ;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

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字段获得该分区的范围描述，其他没什么了：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

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这个错误：

?

1	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。