Jan 302013
 
create table use_interval_partitioning (
  and_new_partitions            number,
  will_be_created_automatically varchar2(1)
                                default 'Y',
  for_each_new                  date,
  so_your_system_wont_break     varchar2(1)
                                default 'Y',
  if_maintenance_is_forgotten   varchar2(100)
                                default 'Yay!'
) partition by range (for_each_new)
  interval (numtodsinterval(1,'DAY')) (
    partition first values less than (
      date '2013-01-30')
);
A very handy feature, I just wish there was a way to specify a regexp for new partition names; 
I don't like systems cluttered up with SYS_Pxxx partitions...

  2 Responses to “Saving you from partition maintenance headaches”

  1. I just wish there was a way to specify a regexp for new partition names

    Sigh, yes, all these SYS-names in our data-dictionaries.

    So, how do you like the following workaround:

    create function no_SYS_PXXX_partitions(vTableName in varchar2, vPartitionName in varchar2) return varchar2
    is
    l varchar2(1024);
    begin
    select high_value
    into l
    from user_tab_partitions
    where table_name = vTableName
    and partition_name = vPartitionName;

    return 'Part_' || replace(substr(l, 11, 10), '-', '_');

    end no_SYS_PXXX_partitions;
    /

    create view user_nicer_tab_partitions
    as
    select
    cast(
    no_SYS_PXXX_partitions(
    u.table_name,
    u.partition_name
    )
    as varchar2(40))
    as nice_partition_name,
    u.*
    from user_tab_partitions u
    /

    insert into use_interval_partitioning ( for_each_new )
    select sysdate + level from dual
    connect by sysdate + level <= sysdate + 7;

    select partition_position, nice_partition_name from user_nicer_tab_partitions where table_name = 'USE_INTERVAL_PARTITIONING' order by partition_position;

    PARTITION_POSITION NICE_PARTITION_NAME
    ------------------ -------------------
    1 Part_2013_01_30
    2 Part_2013_02_01
    3 Part_2013_02_02
    4 Part_2013_02_03
    5 Part_2013_02_04
    6 Part_2013_02_05
    7 Part_2013_02_06
    8 Part_2013_02_07

    ?

    • Great thinking as always Matthias; that’s a neat solution to get back the meaningful partition names.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)