Oracle
11g 众多新特性中,我最喜欢的就是分区表增强,众所周知很多大问题“化整为零”之后就不是个问题,分区表就是一种非常好用的“化整为零”的手段。
还是说回正题吧,使用Interval分区不难,为分区/子分区的重命名也不难,我的分区表大致定义如下:
1 | CREATE TABLE Partition_Table |
2 | ( |
3 | .... |
4 | .... |
5 | .... |
6 | ) |
7 | PARTITION BY RANGE( MSGDATE ) INTERVAL( NUMTOYMINTERVAL(1, 'MONTH' ) ) |
8 | SUBPARTITION BY LIST( DAY_V ) |
分区和子分区的重命名语法如下:
1 | alter table <table_name> rename partition <partition_name> to <new_partition_name>; |
2 | alter table <table_name> rename subpartition <subpartition_name> to <new_subpartition_name>; |
每当新数据触发新建分区后,分区名字是系统给的,虽然不影响分区表的使用,但是看着很让人迷茫:
1 | select |
2 | table_name , |
3 | partition_name, |
4 | subpartition_name , |
5 | tablespace_name |
6 | from user_tab_subpartitions |
7 | where subpartition_name like 'SYS%' ; |
01 | TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME |
02 | ------------------------------ --------------- ------------------ ------------------------------ |
03 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP100 FIREWALL16 |
04 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP101 FIREWALL17 |
05 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP102 FIREWALL18 |
06 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP103 FIREWALL19 |
07 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP104 FIREWALL20 |
08 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP105 FIREWALL21 |
09 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP106 FIREWALL22 |
10 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP107 FIREWALL23 |
11 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP108 FIREWALL24 |
12 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP109 FIREWALL25 |
13 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP110 FIREWALL26 |
14 | |
15 | TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME |
16 | ------------------------------ --------------- ------------------ ------------------------------ |
17 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP111 FIREWALL27 |
18 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP112 FIREWALL28 |
19 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP113 FIREWALL29 |
20 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP114 FIREWALL30 |
21 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP115 FIREWALL31 |
22 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP85 FIREWALL01 |
23 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP86 FIREWALL02 |
24 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP87 FIREWALL03 |
25 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP88 FIREWALL04 |
26 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP89 FIREWALL05 |
27 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP90 FIREWALL06 |
28 | |
29 | TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME |
30 | ------------------------------ --------------- ------------------ ------------------------------ |
31 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP91 FIREWALL07 |
32 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP92 FIREWALL08 |
33 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP93 FIREWALL09 |
34 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP94 FIREWALL10 |
35 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP95 FIREWALL11 |
36 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP96 FIREWALL12 |
37 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP97 FIREWALL13 |
38 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP98 FIREWALL14 |
39 | P_MYSYSLOG_IL_2010 SYS_P116 SYS_SUBP99 FIREWALL15 |
将分区/子分区的名字改成得有意义才是王道。以下是用一个过程配合游标来改分区名,重点是是从high_value字段获得该分区的范围描述,其他没什么了:
01 | declare |
02 | v_sql varchar (400); |
03 | v_table_name user_tab_partitions.table_name%type; |
04 | v_partition_name user_tab_partitions.partition_name%type; |
05 | v_high_value varchar (200); |
06 | v_tmp_partition_name user_tab_partitions.partition_name%type; |
07 | cursor cur is |
08 | select |
09 | table_name , |
10 | partition_name , |
11 | high_value |
12 | from user_tab_partitions |
13 | where partition_name like 'SYS%' ; |
14 | begin |
15 | open cur; |
16 | loop |
17 | fetch cur into v_table_name,v_partition_name,v_high_value; |
18 | exit when cur%notfound; |
19 | v_tmp_partition_name := substr(v_high_value,11,10); |
20 | v_tmp_partition_name := to_char( to_date(v_tmp_partition_name, 'yyyy-mm-dd' )-1 , 'yyyymm' ); |
21 | v_sql := 'alter table ' ||v_table_name|| ' rename partition ' |
22 | ||v_partition_name |
23 | || ' to P' ||v_tmp_partition_name; |
24 | dbms_output.put_line( v_sql ); |
25 | execute immediate v_sql; |
26 | end loop; |
27 | close cur; |
28 | end ; |
29 | / |
由于改分区名属于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。