Oracle 11g have introduced new functionality of time interval partitions management. One of disadvantages of new approach are messy ssytem generated names of partitions. It would be pity not to use the new sofisticated approach just because of such issue. Better include simple solition to rename generated partitions based on its metadata definition.
Lets see following example of automatically maintained monthly partitions:
create table tt3 (
a integer
,b date
)partition by range (b) interval (NUMTOYMINTERVAL(1,'MONTH'))
(partition P1 values less than(to_date('10000101','yyyymmdd')))
/
Insert into tt3 (a,b) values(1,to_date('20080430','yyyymmdd'))
/
Insert into tt3 (a,b) values(1,to_date('20080330','yyyymmdd'))
/
Following listing of partitions illustrates basic problem of such approach. Names of partitions are generated by system and it looks messy and uncomfortable for maintenance of partitions.
Following sample code tidies partition naming - renames system partitions to standard form.
The sample code supposes there is one date partition key column and it is named by ultimo date of month.
--Set serveroutput on
Declare
c_table_name varchar2(30):='TT3'; --specify name of table
c_table_owner varchar2(30):=USER; --you can specify owner
v_highvalue varchar2(8000);
v_highdate date;
v_newname varchar2(30);
Begin
--DBMS_OUTPUT.ENABLE(1000000);
for r1 in (
select partition_name
from all_tab_partitions
where table_name=c_table_name and table_owner=c_table_owner
and partition_name like 'SYS%'
) LOOP
select high_value into v_highvalue from all_tab_partitions
where table_name=c_table_name and table_owner=c_table_owner
and partition_name=r1.partition_name;
execute immediate 'select '||v_highvalue||' from dual' into v_highdate;
v_newname:='P_'||to_char(v_highdate-1,'YYYYMMDD'); -- or another notation
--DBMS_OUTPUT.PUT_LINE(v_newname);
execute immediate 'Alter table '||c_table_owner||'.'||c_table_name
||' rename partition '||r1.partition_name||' to '||v_newname;
end LOOP;
End;
/