Oracle 11g 众多新特性中,我最喜欢的就是分区表增强,众所周知很多大问题“化整为零”之后就不是个问题,分区表就是一种非常好用的“化整为零”的手段。
还是说回正题吧,使用Interval分区不难,为分区/子分区的重命名也不难,我的分区表大致定义如下:
1 |
CREATE TABLE Partition_Table |
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>; |
每当新数据触发新建分区后,分区名字是系统给的,虽然不影响分区表的使用,但是看着很让人迷茫:
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 |
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 |
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字段获得该分区的范围描述,其他没什么了:
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; |
12 |
from user_tab_partitions |
13 |
where partition_name like 'SYS%' ; |
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 ' |
23 |
|| ' to P' ||v_tmp_partition_name; |
24 |
dbms_output.put_line( v_sql ); |
25 |
execute immediate v_sql; |
1 |
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired |