HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Tidy your automatically maintained time partition names in 11g
ORACLE STUFFS
25-Feb-2009
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.
See: Partitioning strategy for fact tabled in 11g

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.

SQL> select partition_name,high_value from user_tab_partitions where table_name='TT3'; PARTITION_NAME HIGH_VALUE -------------------- ------------------------------------------------------------------------------------ P1 TO_DATE(' 1000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') SYS_P41 TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') SYS_P42 TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

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;
/

[Download]

Now we can see much better names in the following list:

SQL> select partition_name,high_value from user_tab_partitions where table_name='TT3'; PARTITION_NAME HIGH_VALUE ------------------- ----------------------------------------------------------------------------------- P1 TO_DATE(' 1000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') P_20080430 TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') P_20080331 TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky