Automatic Partition Management for Oracle 10g


If you have a moving window partitioning scheme then you should know that in Oracle 11g you now have a brand new way of creating new partitions. Oracle 11g creates the partitions in an automatic fashion, so that when the row arrives at the table, if the partition does not exist, Oracle will create it. This is awsome, but imagine two things:

* You can’t migrate to Oracle 11g on the fly. You’re stuck in 10g
* You also need to drop the oldest partition, because you have lack of storage issues

Then if you are in this situation we have the solution for you. It’s bullet-proof tested and proven at production sites.

Every night you need to add another partition and remove the oldest. Pure moving window stuff. The format of the partition name is: PXXXX_YYYY_MM_DD, where XXXX is a sequential number that increments every day. This is daily partitioning and should only have a job running this code everynight:

declare
 x varchar2(90);
 s varchar2(900);
begin
 -- Fetchs the name of the oldest partition
 select partition_name
 into x
 from sys.dba_tab_partitions
 where partition_position = 1
 and table_name = 'MYTABLE'
 and table_owner = 'MYOWNER';
 -- Builds the name-string
 s := 'ALTER TABLE MYOWNER.MYTABLE DROP PARTITION '||x||' UPDATE INDEXES';
 -- Uses a customized report and sends it by email 
 -- so you can see the partitions state before and after
 MYOWNER.my_pkg.sends_email('REPORT6');
 -- Drops the Partition
 execute immediate s;
 -- And now adds another
 MYOWNER.add_partition;
 MYOWNER.my_pkg.sends_email('REPORT6');
 --dbms_output.put_line(s);
end;

The procedure “add_partition” is another piece of automatic code that you must create prior to the previous PL/SQL block:

CREATE OR REPLACE procedure MYOWNER.add_partition
is
next_part varchar2(40);
less_than_char varchar2(20);
comando_add varchar2(1000);
BEGIN
-- Generates the name of the partition
select 'P'||to_char(to_number(substr(partition_name,2,
         instr(partition_name,'_',1)-2))+1)||'_'||
         to_char(to_date(substr(partition_name,
         instr(partition_name,'_',1)+1),'yyyy_mm_dd')+1,'yyyy_mm_dd'),
         replace(to_char(to_date(substr(partition_name,
        instr(partition_name,'_',1)+1),'yyyy_mm_dd')+2,'yyyy_mm_dd'),'_','-')
into next_part,less_than_char
from dba_tab_partitions
where table_owner = 'MYOWNER'
and table_name = 'MYTABLE'
and partition_position = (select max(partition_position)
from dba_tab_partitions where table_owner = 'MYOWNER'
and table_name = 'MYTABLE');
-- Builds the statement string
comando_add :=              'ALTER TABLE MYOWNER.MYTABLE ADD PARTITION '||next_part;
comando_add := comando_add||' VALUES LESS THAN (to_date('||chr(39)||less_than_char;
comando_add := comando_add||chr(39)||','||chr(39)||
'yyyy-mm-dd'||chr(39)||')) TABLESPACE DATA_PARTITIONED';
-- Executes the statement
execute immediate(comando_add);
--dbms_output.put_line(comando_add);
end;
/

You will have permission issues that you can resolve reading this.

If you need any further help applying this, please let me know.

LMC.

About these ads

34 thoughts on “Automatic Partition Management for Oracle 10g

  1. Hi..

    Nice one!!! Will definitely try it out..What changes need to be done, if want to drop and add monthly partitions.

    Regards,
    Anand

  2. Thanks Anand.
    These scripts assume that the daily partitions of the table have the following format:

    PXXXX_YYYY_MM_DD

    where XXXX is a number, and the rest is a date mask. So if you want to adapt this to monthly I think that you just have to decide first on a format for your partition names. Assuming you don’t want to adapt too much my scripts, just wipe off the “_DD” part of format, and replace in the script “YYYY_MM_DD” with” YYYY_MM” and everything should work fine.

    Cheers,

    LMC.

  3. Hi, this looked to be exactly what I needed (much appreciate that it’s visible on the web and thanks for sharing). However, on trying the create part, my SQLDevelper tool says it’s ignoring the statement:
    select ‘P’||to_char(to_number(substr(partition_name,2,instr(partition_name,’_’,1)-2))+1)||’_’||to_char(to_date(substr(partition_name,instr(partition_name,’_’,1)+1),
    then goes onto say my table does not exist at this point:
    and partition_position = (select max(partition_position) from dba_tab_partitions where table_owner = ‘table_owner’ AND table_name = ‘table_name’);
    (I tried the above section as a simple select statement and it returns as expected).
    Admittedly, my SQL skills are a bit rusty.
    Any suggestions would be greatly appreciated.

    Cheers

    Lex

  4. I would check the quotes first, because it seems to be two of them in the statement. WordPress posts are hard to format and probably the fault is mine. Anyway, reduce everything to the same basic simple quote => ‘

    Thanks for reading and commenting.

    LMC.

  5. i need similar type solution but for number of tables say 10 or 50 .how your script can be used?

  6. Easy.

    Transform the Anonymous block (the one that starts with DECLARE) into a procedure.

    Where you read DECLARE substitute with:

    create or replace procedure drop_and_create_partition (tabname varchar2)

    Substitute the following block:

    and table_name = ‘MYTABLE’
    and table_owner = ‘MYOWNER';
    — Builds the name-string
    s := ‘ALTER TABLE MYOWNER.MYTABLE DROP PARTITION ‘||x||’ UPDATE INDEXES';

    By this one:

    and table_name = tabname
    and table_owner = ‘MYOWNER';
    — Builds the name-string
    s := ‘ALTER TABLE MYOWNER.’||tabname||’ DROP PARTITION ‘||x||’ UPDATE INDEXES';

    Then in the other procedure make the following adjustments:

    Where you read:
    CREATE OR REPLACE procedure MYOWNER.add_partition
    You should code it like:
    CREATE OR REPLACE procedure MYOWNER.add_partition (tabname varchar2)

    And where you read:
    and table_name = ‘MYTABLE’); (actually there are two lines like this)
    You should code it like:
    and table_name = tabname);

    And where you read:
    comando_add :=’ALTER TABLE MYOWNER.MYTABLE ADD PARTITION ‘||next_part;
    You should code it like:
    comando_add :=’ALTER TABLE MYOWNER.’||tabname||’ ADD PARTITION ‘||next_part;

    And in the end just invoke:

    SQL> exec drop_and_create_partition(‘Table1′);
    SQL> exec drop_and_create_partition(‘Table2′);
    SQL> exec drop_and_create_partition(‘Table3′);
    (…)
    SQL> exec drop_and_create_partition(‘Table50′);

    Hope it helped.

    LMC.

  7. Hi,
    Its really gud one.thanks for your inputs.
    right now my scenraio is we have 100 tables which are monthly partioned,and management wanted to drop future partitions(say from april to dec partitons) in all the 100 tables and wanted to convert them as daily partitons.how do i need to go ahead.as am newbie to partitions.
    your inputs are more valuable to me.

    thanks

  8. You can partition by Month and then subpartition by day.
    If you want to read more about these features, please click “Go Tropical” in this page on the right top side and choose your database version.
    There you’ll have lots of info to chew on for the next century ;-)

    Cheers,

    LMC:

  9. thanks for your reply.
    all the tables were already monthly partioned.
    am not good enough in pl/sql so asked for converting into dauly partitions at one stretch.
    thnaks in advance.

  10. Without giving it a second thought I would say subpartitioned the table would be the closest to a “one-strech” operation you can get.
    If someone out there has some other pre-cooked stuff it’s time to show the recipe.

    You’re welcomed manjula.

    LMC.

  11. Hi Luis,

    You have posted very useful article with automatic partition by days.
    I have the table partition by hours, do you have any idea how to maintain hourly partition?

    CREATE TABLE TXN2
    (
    DATETIME DATE NOT NULL,
    IMSI NUMBER NOT NULL
    )
    PARTITION BY RANGE (DATETIME)
    (
    PARTITION P0000_2011_02_14_00 VALUES LESS THAN (to_date(‘14.02.2011 00:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_01 VALUES LESS THAN (to_date(‘14.02.2011 01:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_02 VALUES LESS THAN (to_date(‘14.02.2011 02:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_03 VALUES LESS THAN (to_date(‘14.02.2011 03:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_04 VALUES LESS THAN (to_date(‘14.02.2011 04:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_05 VALUES LESS THAN (to_date(‘14.02.2011 05:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_06 VALUES LESS THAN (to_date(‘14.02.2011 06:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_07 VALUES LESS THAN (to_date(‘14.02.2011 07:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_08 VALUES LESS THAN (to_date(‘14.02.2011 08:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_09 VALUES LESS THAN (to_date(‘14.02.2011 09:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_10 VALUES LESS THAN (to_date(‘14.02.2011 10:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_11 VALUES LESS THAN (to_date(‘14.02.2011 11:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_12 VALUES LESS THAN (to_date(‘14.02.2011 12:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_13 VALUES LESS THAN (to_date(‘14.02.2011 13:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_14 VALUES LESS THAN (to_date(‘14.02.2011 14:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_15 VALUES LESS THAN (to_date(‘14.02.2011 15:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_16 VALUES LESS THAN (to_date(‘14.02.2011 16:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_17 VALUES LESS THAN (to_date(‘14.02.2011 17:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_18 VALUES LESS THAN (to_date(‘14.02.2011 18:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_19 VALUES LESS THAN (to_date(‘14.02.2011 19:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_20 VALUES LESS THAN (to_date(‘14.02.2011 20:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_21 VALUES LESS THAN (to_date(‘14.02.2011 21:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_22 VALUES LESS THAN (to_date(‘14.02.2011 22:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_14_23 VALUES LESS THAN (to_date(‘14.02.2011 23:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_00 VALUES LESS THAN (to_date(‘15.02.2011 00:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_01 VALUES LESS THAN (to_date(‘15.02.2011 01:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_02 VALUES LESS THAN (to_date(‘15.02.2011 02:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_03 VALUES LESS THAN (to_date(‘15.02.2011 03:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_04 VALUES LESS THAN (to_date(‘15.02.2011 04:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_05 VALUES LESS THAN (to_date(‘15.02.2011 05:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_06 VALUES LESS THAN (to_date(‘15.02.2011 06:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_07 VALUES LESS THAN (to_date(‘15.02.2011 07:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_08 VALUES LESS THAN (to_date(‘15.02.2011 08:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_09 VALUES LESS THAN (to_date(‘15.02.2011 09:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_10 VALUES LESS THAN (to_date(‘15.02.2011 10:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_11 VALUES LESS THAN (to_date(‘15.02.2011 11:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_12 VALUES LESS THAN (to_date(‘15.02.2011 12:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_13 VALUES LESS THAN (to_date(‘15.02.2011 13:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_14 VALUES LESS THAN (to_date(‘15.02.2011 14:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_15 VALUES LESS THAN (to_date(‘15.02.2011 15:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_16 VALUES LESS THAN (to_date(‘15.02.2011 16:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_17 VALUES LESS THAN (to_date(‘15.02.2011 17:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_18 VALUES LESS THAN (to_date(‘15.02.2011 18:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_19 VALUES LESS THAN (to_date(‘15.02.2011 19:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_20 VALUES LESS THAN (to_date(‘15.02.2011 20:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_21 VALUES LESS THAN (to_date(‘15.02.2011 21:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_22 VALUES LESS THAN (to_date(‘15.02.2011 22:00:00′,’dd.mm.syyyy hh24:mi:ss’)),
    PARTITION P0000_2011_02_15_23 VALUES LESS THAN (to_date(‘15.02.2011 23:00:00′,’dd.mm.syyyy hh24:mi:ss’))
    )
    )

    thanks in advance.

  12. hi, thanks for the code but when i execute the code…
    declare
    x varchar2(90);
    s varchar2(900);
    Begin
    — Fetchs the name of the oldest partition
    select partition_name
    into x
    from user_tab_partitions
    where partition_position = 1
    and table_name = ‘EMP3′
    — and table_owner = ‘SCOTT';
    — Builds the name-string
    s:=’ALTER TABLE EMP3 DROP PARTITION ‘ || x ||’ UPDATE INDEXES;
    — Drops the Partition
    execute immediate s;
    end;
    /

    ERROR at line 13:
    ORA-06550: line 13, column 2:
    PL/SQL: ORA-00933: SQL command not properly ended
    ORA-06550: line 6, column 2:
    PL/SQL: SQL Statement ignored

    i have ignored the

  13. The is a character missing!
    Where you have: s:=’ALTER TABLE EMP3 DROP PARTITION ‘ || x ||’ UPDATE INDEXES;
    You should have: s:=’ALTER TABLE EMP3 DROP PARTITION ‘ || x ||’ UPDATE INDEXES';

    Hope it runs now.

    LMC

  14. You are an oracle angel, Of course, this code can be used by trigger or scheduler. I just need to add a parttion, any cooked code for trigger?

  15. I’m afraid not Bill. Nothing in the hoven either :-)
    To implement this as a trigger, it’s just a dangerous way of doing it, but if well done, might be the cherry on top of the cake.
    If you sort it out please share.

    Cheers,

    LMC

  16. My case is partition by numeric range, and next number is created by a sequencer. I’m think when sequencer is called then trigger the add_partition, I need to figure out the trigger, seq.nextval –>add_partiotion, any idea?

  17. OK, I create a dummy table once I have the nextval, I insert into the table which has a trigger after insertion, any better idea?

  18. Just tried running this. And somehow it is complaining for table not existing. Not sure where it is reading it from.

    CREATE OR REPLACE procedure PACKMAN.add_partition
    2 is
    next_part varchar2(40);
    4 less_than_char varchar2(20);
    5 comando_add varchar2(1000);
    6 BEGIN
    7 select ‘P’||to_char(to_number(substr(partition_name,2,instr(partition_name,’_’,1)-2))+1)|| ‘_’ ||to_char(to_date(substr(partition_name,instr(partition_name,’_’,1)+1),’yyyy_mm_dd’)+1,’yyyy_mm_dd’),replace(to_char(to_date(substr(partition_name,instr(partition_name,’_’,1)+1),’yyyy_mm_dd’)+2,’yyyy_mm_dd’),’_’,’-‘) from dba_tab_partitions where table_name =’DUMMY_xxx_EVxx_TRACKER_N';
    8 end;
    9 /

    Warning: Procedure created with compilation errors.

    DBANEW>show error
    Errors for PROCEDURE PACKMAN.ADD_PARTITION:

    LINE/COL
    ——————————————————————————–
    ERROR
    ——————————————————————————–
    7/1
    PL/SQL: SQL Statement ignored

    7/318
    PL/SQL: ORA-00942: table or view does not exist

  19. You have to have permissions to the underlying tables that support the “dba_tab_partitions” view.
    If you have problems getting those and the schema where you’re creating this procedure is the owner of the tables then try using “user_tab_partitions” instead.

    LMC

  20. hey many thanks for the wonderfull post. I have a requirement of creating a backup table on an existing table which is critical for the business atleast once a week so as to preserve a weeks data. This table is around 500MB with approx 1 million rows.
    How do you think i must go about it. Need your suggestions.

    Chris

  21. What is the Oracle Database version? If it’s 11g you can use interval partitioning to have your last week in a specific partition/tablespace, backup that tablespace with rman and then split the partition and move on to the next week. If it’s below 11g (10g or 9/8i) then you can export part of that table every week. Though the best would be to have a backup strategy for the whole database that could include this specific table backup requirements.

  22. Hi Luis, Please help me to create partition daily in 10g? I’ll use this:
    PARTITION P_20602 VALUES LESS THAN (20602). What changes in your script that need to be done?

    Thank you very much!

  23. Have you ever thought about including a little bit more than just your articles?

    I mean, what you say is valuable and everything. However imagine if you added some great images or video
    clips to give your posts more, “pop”! Your content is excellent but with images and videos, this blog could definitely be one of the most beneficial in
    its niche. Wonderful blog!

  24. I would like to implement this dynamic Partitioning in Oracle 10g by addiing / spliting a default partition and am looking for more information how to handle indexes after this change, in the cases of default partition space around 35 GB. Please guide.

  25. Ran into the same issue, did some digging, apparently there’s a product that supports automatic partitioning maintenance from version 9i, including archiving or dropping old partitions, statistics copy between partitions, all date resolutions (hourly, daily, monthly, yearly, w/e) and more stuff, looks cool – http://www.xyrosoft.com.

    gonna check it out and post back.

    thanks for the blog, great reading.

  26. update

    Installed the product I mentioned in the post above on one of our test environment, agentless (automated processes seems to run on database jobs). the GUI is pretty nice, took me 10 minutes to configure automation of partition cycle on 23 tables. still testing the partition archiving process, but so far looks pretty good.

    hope it helps the rest of you… goodluck!

  27. I want to create dynamic partition based on month. So
    I created a procedure to add partition as you mentioned in example and i’m calling procedure to create partition like below

    create or replace
    trigger check_date
    before insert or update of p_date on mytable
    for each row
    declare
    l_date VARCHAR2(10) := NULL;
    begin

    select to_char(to_date(substr(max(partition_name),instr(max(partition_name),’_’,1)+5),’yyyy_mm’),’yyyy_mm’) INTO l_date
    from
    dba_tab_partitions where table_owner = ‘WAREHOUSE’
    and table_name = ‘MYTABLE’
    and partition_position =
    (select min(partition_position)
    from dba_tab_partitions
    where table_owner = ‘WAREHOUSE’ and table_name =’MYTABLE’);

    IF to_date(:new.p_date,’dd-mm-yyyy’) > to_date(l_date,’yyyy-mm’) THEN
    add_partition;
    END IF;
    end;

    When i’m inserting date value which is more than the partition range , it is not creating new partition and it is throwing an error :
    One error saving changes to table “WAREHOUSE”.”MYTABLE”:
    Row 3: ORA-14400: inserted partition key does not map to any partition

    Please help!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s