Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

Package to manage partitions

I want to share with You one package, that I was created to manage table partitions, exactly it adds daily partitions to extend table and drops older partitions to clear expired data. This very simple package can helpful in archiving solutions, when You will need for example to store some data in the database for specified days, for example for one week 7 days. Then You can create daily partitioned table and every day export partition with expired data and run this procedure to adjust partitions, it like moving window, You will shift partitions forward by creating new one and by dropping oldest. No deletion of rows that can take many resources and generate redo.

Here is the code:

create or replace package sys.adjust_partitions as

------------------------------------------------------------------------------------------------------------
-- FUNCTION
--   To adjust table partitions, adds and removes partitions according to the arguments given,
--   list of arguments can be found lower.
-- NOTES
--   Before execute, enable serveroutput to display the output of the procedure.
--
-- CREATED BY
-- Aychin Gasimov	07/2011		aychin.gasimov@gmail.com
--
-- MODIFIED
-- ...
--
-- ARGUMENTS
--   TAIL         - The number of days (partitions) to leave before the curr_date, the default is 7 days,
--                  all partitions older 7 days will be dropped.
--   HEAD         - The number of days (partitions) to leave after the curr_date, the default is 3 days,
--                  if there will be less than 3 partitions after curr_date new partitions will be added
--		    if there is more than 3 partitions then no action will be done (they will not be deleted).
--   TOEXEC       - If true (default) then script will be printed and table will be modified, if false just
--                  script will be printed.
--   CURR_DATE    - The day from which to start the count, default is sysdate.
--   STEP_IN_DAYS - When adding partitions, the default step is one day, but if needed can be adjusted to
--                  step by 2 days for example.
------------------------------------------------------------------------------------------------------------

type tabrec is record (part_name varchar2(100), ddate date);
type gtab is table of tabrec;

function vtab (towner in varchar2, tname in varchar2) return gtab pipelined;
procedure adjust (towner in varchar2, tname in varchar2, tail in number default 7, head number default 3,
                  toexec in boolean default true, curr_date in date default sysdate,
		  step_in_days in number default 1);

end adjust_partitions;
/

create or replace package body sys.adjust_partitions as

function vtab (towner in varchar2, tname in varchar2) return gtab pipelined is
  cursor cur1 (tabowner varchar2, tabname varchar2) is
   select partition_name, high_value ,high_value_length from dba_tab_partitions where table_owner=tabowner and table_name=tabname;
  cur1_rec cur1%ROWTYPE;
  a varchar2(1000);
  b date;
  trec tabrec;
begin
   for cur1_rec in cur1 (towner,tname)
    loop
     a:=substr(cur1_rec.high_value,1,cur1_rec.high_value_length);
     execute immediate 'select '||a||' from dual' into b;
      trec.part_name:=cur1_rec.partition_name;
      trec.ddate:=b-1;
      pipe row (trec);
    end loop;
end vtab;

procedure adjust (towner in varchar2, tname in varchar2, tail in number default 7, head number default 3,
                  toexec in boolean default true, curr_date in date default sysdate,
		  step_in_days in number default 1) as
 curr_user varchar2(100);
 var1 integer;
 mindate date;
 maxdate date;
 deldate date;
 adddate date;
 currdate date := trunc(curr_date);
 pnamedate date;
 loopidx pls_integer:=0;
begin
 select min(ddate), max(ddate) into mindate, maxdate from table(vtab(towner,tname));
 adddate:=currdate+head;
 pnamedate:=maxdate;
 if adddate>maxdate then
   LOOP
     pnamedate:=pnamedate+step_in_days;
     dbms_output.put_line('alter table '||towner||'.'||tname||' add partition P'||to_char(pnamedate,'yyyymmdd') ||
	                      ' values less than (to_date('''||to_char(pnamedate+1,'dd.mm.yyyy')||''',''dd.mm.yyyy''));');
     if toexec then
      execute immediate 'alter table '||towner||'.'||tname||' add partition P'||to_char(pnamedate,'yyyymmdd') ||
	                    ' values less than (to_date('''||to_char(pnamedate+1,'dd.mm.yyyy')||''',''dd.mm.yyyy''))';
      dbms_output.put_line('Partition P'||to_char(pnamedate,'yyyymmdd')||' added.');
     end if;
     loopidx:=loopidx+step_in_days;
     EXIT WHEN loopidx>=adddate-maxdate;
   END LOOP;
 else
   dbms_output.put_line('No partitions to add.');
 end if;
 deldate:=currdate-tail;
 if deldate>mindate then
   FOR c1 IN (select part_name from table(vtab(towner,tname)) where ddate < deldate) LOOP
     dbms_output.put_line('alter table '||towner||'.'||tname||' drop partition '||c1.part_name||' update indexes;');
     if toexec then
      execute immediate 'alter table '||towner||'.'||tname||' drop partition '||c1.part_name||' update indexes';
      dbms_output.put_line('Partition '||c1.part_name||' dropped.');
     end if;
   END LOOP;
 else
    dbms_output.put_line('No partitions to drop.');
 end if;
end adjust;

end adjust_partitions;
/

How it will work, lets see on example, if partitions is:

P20110701 - 01.07.2011
P20110702 - 02.07.2011
P20110703 - 03.07.2011
P20110704 - 04.07.2011
P20110705 - 05.07.2011
P20110706 - 06.07.2011
P20110707 - 07.07.2011
P20110708 - 08.07.2011
P20110709 - 09.07.2011
P20110710 - 10.07.2011

Then, if SYSDATE (or curr_date) is 08.07.2011, then after execution of

exec sys.adjust_partitions.adjust('OWNER','TABLE',4,3);

Partitions P20110701, P20110702, P20110703 will be dropped and Partition P20110711 will be added to the table.

If, SYSDATE (or curr_date) will be 05.07.2011, then NOTHING will be done!

If, SYSDATE (or curr_date) will be 12.07.2011, then after execution of the same command Partitions from P20110701 to P20110707 will be dropped and Partitions from P20110711 to P20110715 will be added. Table will have partitions from P20110708 to P201107.

P.S.: If You will want to make some changes or improvements to the procedure, please don’t hesitate to do so, then send me Your modifications, I will add them to this post under the MODIFIED section.


(c) Aychin Gasimov, 07/2011, Azerbaijan Republic


Advertisements

4 responses to “Package to manage partitions

  1. tabreaz July 19, 2011 at 16:46

    Can you please email me complete package or update the code above.

    Thanks in advance.

  2. Jason Christian Dior 2012 Resort Collection. 7 February 8, 2012 at 04:49

    One primary difference between the Parent Partition and the Child partitions is seen in the following graphics.

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

%d bloggers like this: