CREATE TABLE t1.PART_MAINT
(
  OWNER               VARCHAR2(30 BYTE) NOT NULL,
  TABLE_NAME          VARCHAR2(30 BYTE) NOT NULL,
  PARTITION_UNIT      CHAR(1 BYTE)      DEFAULT 'D',
  PARTITION_PREFIX    varchar2(20)      DEFAULT 'P_',
  DROP_PARTITION      CHAR(1 BYTE)      DEFAULT 'N',
  ADD_PARTITION       CHAR(1 BYTE)      DEFAULT 'Y',
  ACTIVE_PARTITIONS         NUMBER      DEFAULT 90           NOT NULL,
  UPDATE_DATE                 DATE      DEFAULT sysdate       NOT NULL,
  UPDATE_USERID    VARCHAR2(8 BYTE)  DEFAULT substr(user,1,8)      NOT NULL
)
TABLESPACE PBH_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1040K
            NEXT             512K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


ALTER TABLE C0HARPA.PART_MAINT ADD (
  CHECK (partition_UNIT  IN ('D', 'W', 'M', 'Y')),
  CHECK (DROP_partition  IN ('N', 'Y')),
  CHECK (ADD_partition  IN ('N', 'Y')));

Add records

insert into c0harpa.part_maint
(owner, table_name, drop_partition, ACTIVE_PARTITIONS, partition_prefix)
values
('T1', 'TABLE1', 'Y', 20, 'REQ_FEAT_');
...
...
...


create or replace function get_token(
   the_list  varchar2,
   the_index number,
   delim     varchar2 := ','
)
   return    varchar2
is
   start_pos number;
   end_pos   number;
begin
   if the_index = 1 then
       start_pos := 1;
   else
       start_pos := instr(the_list, delim, 1, the_index - 1);
       if start_pos = 0 then
           return null;
       else
           start_pos := start_pos + length(delim);
       end if;
   end if;

   end_pos := instr(the_list, delim, start_pos, 1);

   if end_pos = 0 then
       return substr(the_list, start_pos);
   else
       return substr(the_list, start_pos, end_pos - start_pos);
   end if;

end get_token;
/


REATE OR REPLACE PACKAGE C0HARPA.part_maint_pkg as
   procedure drop_p;
   procedure add_p;
end part_maint_pkg;
/

CREATE OR REPLACE PACKAGE BODY C0HARPA.part_maint_pkg as
procedure drop_p is

   p_partition_position number := 0;
   update_global_index  number := 0;

   p_partition_name      varchar2(30);
   val                   varchar2(30);
   date_value_str        varchar2(256);

   sql_stmt varchar2(2048);
   high_value_str varchar2(4000);

   d date;

  v_foundrows boolean := false;

  BEGIN
   --
   -- To prevent ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
   --
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);

   for r in ( select * from c0harpa.part_maint where drop_partition = 'Y' )
   loop


      SELECT max(partition_position) into p_partition_position
      FROM dba_tab_partitions  WHERE
      table_name = r.table_name AND
      table_owner = r.owner;

      if   upper(r.partition_unit) = 'D'                     -- Daily
      then
         d   := (SYSDATE - r.active_partitions);
         val := TO_CHAR (d, 'YYYY-MM-DD');
      elsif upper(r.partition_unit) = 'W'                    -- Weekly
      then

 d   := NEXT_DAY((sysdate  - (r.active_partitions *7) ), 'SUN');
         val := TO_CHAR (d, 'YYYY-MM-DD');
      elsif upper(r.partition_unit) = 'M'                    -- First of month
      then
         d   := ADD_MONTHS (SYSDATE, - r.active_partitions);
         val :=  TO_CHAR(TRUNC(d,'MM') , 'YYYY-MM-DD');
      END IF;

      dbms_output.put_line (val || ' = val');
      dbms_output.put_line (p_partition_position || ' = p_partition_position');

      for x in ( SELECT *  FROM dba_tab_partitions WHERE
               table_name = r.table_name AND
               table_owner = r.owner AND
               partition_position < p_partition_position)
      loop

         v_foundrows := true;

           --
         -- Convert a long into a string. This is the simplest way I know
         -- how to do this.
         --

      -- dbms_output.put_line (x.partition_position || ' = x.partition_position'
);
      -- dbms_output.put_line (x.partition_name || ' = x.partition_name');

         SELECT high_value into high_value_str FROM dba_tab_partitions WHERE
                table_name = r.table_name AND
                table_owner = r.owner AND
                partition_position = x.partition_position;

        -- The variable high_value will return a value in one of these formats:
        --
        -- 1)  TO_DATE(' 2011-08-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
        --              'NLS_CALENDAR=GREGORIAN')
        --
        -- 2) TIMESTAMP' 2011-08-14 00:00:00'
        --
        -- The following select statement get the second token between the
        -- "'" single quote (Ie ' 2011-08-14 00:00:00') and than remove the
        -- time portion, which will leave us with the date in this format.
        --
        -- Ie 2011-08-14  (YYYY-MM-DD)

         select get_token(trim(get_token(high_value_str ,2, '''')),1,' ')
        into date_value_str FROM dual;

        if to_date(date_value_str, 'YYYY-MM-DD') <=
           to_date(val, 'YYYY-MM-DD')
        then

           -- Tere is no need to check if table has global indexes or not.
           -- Just add update global indexes and oracle will figure out
           -- itself if table has any global indexes and if it has, it will
           -- do the update of the global indexes.

           -- select count(1) into update_global_index from dba_part_indexes
           -- where owner = r.owner AND
           -- table_name = r.table_name  AND
           -- locality = 'GLOBAL';

           sql_stmt:= 'alter table ' || r.owner ||'.'||r.table_name|| ' drop par
tition '|| x.partition_name || ' update global indexes parallel(degree 8)';

          dbms_output.put_line (sql_stmt);

execute immediate sql_stmt ;

        end if;

      end loop;

   end loop;

   if not v_foundrows then
      raise no_data_found;
   end if;

exception
   when no_data_found then
      dbms_output.put_line('process complete');

END;

procedure add_p is

   found_partitions_to_add   boolean := false;
   p_max_partition_str  varchar2(30);
   val                  varchar2(30);
   val1                 varchar2(30);
   date_value_str       varchar2(256);
   sql_stmt             varchar2(2048);

   d         date;
   d1        date;
   high_date date;

   vdtp  dba_tab_partitions%ROWTYPE;

BEGIN
   --
   -- To prevent ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
   --
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);

   for r in ( select * from c0harpa.part_maint where add_partition = 'Y' )
   loop
      found_partitions_to_add := true;

      --
      -- Get the name of the highest partition
      --

      select * into vdtp from dba_tab_partitions
         where partition_position = ( select max(partition_position)
         from   dba_tab_partitions where
                table_owner = r.owner AND
                table_name= r.table_name )
         and  table_owner = r.owner
         and  table_name  = r.table_name;

      p_max_partition_str := vdtp.partition_name;

      if   upper(vdtp.high_value) = 'MAXVALUE'
      then

        --
         -- High value is not a date so get the next to last partition
         -- postition and use that high_value
         --
         -- This should only apply to range partitions.
         --

         select * into vdtp from dba_tab_partitions
            where partition_position = ( select max(partition_position) -1
            from   dba_tab_partitions where
                   table_owner = r.owner AND
                   table_name= r.table_name )
            and  table_owner = r.owner
            and  table_name  = r.table_name;

      END IF;

       -- The variable high_value will return a value in one of these formats:
      --
      -- 1)  TO_DATE(' 2011-08-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
      --              'NLS_CALENDAR=GREGORIAN')
      --
      -- 2) TIMESTAMP' 2011-08-14 00:00:00'
      --
      -- The following select statement get the second token between the
      -- "'" single quote (Ie ' 2011-08-14 00:00:00') and than remove the
      -- time portion, which will leave us with the date in this format.
      --
      -- Ie 2011-08-14  (YYYY-MM-DD)

      select get_token(trim(get_token(vdtp.high_value ,2, '''')),1,' ')
      into date_value_str FROM dual;

      high_date := to_date(date_value_str, 'YYYY-MM-DD');

      if   upper(r.partition_unit) = 'D'        -- Daily
      then

         --
         -- Next day
         --

         d   := (high_date)+1;
         val := TO_CHAR (d, 'YYYY-MM-DD');
     
          --
         -- Next today
         --

         d1   := (high_date);
         val1 := TO_CHAR (d1, 'YYYYMMDD');

      elsif upper(r.partition_unit) = 'W'        -- Weekly
      then

         --
         -- Next Sunday
         --

         d   := NEXT_DAY(high_date, 'SUN');
         val := TO_CHAR (d, 'YYYY-MM-DD');

         --
         -- Next Saturday
         --

         d1   := NEXT_DAY(high_date, 'SAT');
         val1 := TO_CHAR (d1, 'YYYYMMDD');

      elsif upper(r.partition_unit) = 'M'       -- Monthly
      then

         --
         -- First day of next month
         --

         d   := LAST_DAY (high_date)+1;
         val := TO_CHAR (d, 'YYYY-MM-DD');
     
        --
         -- Last day of current month
         --

         d1   := LAST_DAY (high_date);
         val1 := TO_CHAR (d1, 'YYYYMMDD');

      END IF;

      sql_stmt:= 'alter table ' || r.owner ||'.'||r.table_name||
                 ' split partition '|| p_max_partition_str || ' at ('||
                 ' to_date('''||val||''','||'''YYYY-MM-DD'''||'))' ||
                 ' into (PARTITION '||r.partition_prefix||val1||',' ||
                 ' PARTITION ' || p_max_partition_str ||
                 ' ) update global indexes parallel(degree 8);';

      dbms_output.put_line (sql_stmt);
      -- execute immediate sql_stmt;

   end loop;

 if not found_partitions_to_add
   then
      raise no_data_found;
   end if;

exception
    when no_data_found then
       dbms_output.put_line('No active records in part_maint table to drop parti
tions.');
END;
/


how to run in a script


sqlplus / <<EOT >> $LOG 2>&1
set serveroutput on
set timing on;
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF;
set heading off;
set serveroutput on
exec t1.part_maint_pkg.drop_p;
exit;
EOT