Add New Partitions

Summary 

If you have partitions in your database it is very common in the future to need a method knowing in which partition you are currently inserting data and if this is the last available partition. If yes, then you must add very soon new partitions to the table. 

In this example partitions have been setup for tables in AX, AR, GL schemas of an E-Business Suite ERP. The algorith is very simple. It checks if rows exist in the last partition of the table, if yes then it creates the ddl sql to add the new partition. 

Connect as sys from sqlplus and set

sql>set serveroutput on

DECLARE
CURSOR c1 IS
SELECT 'select count(*) from '|| a.table_owner || '.' || a.table_name ||' partition('||a.partition_name||')' s1,a.*
FROM dba_tab_partitions a,dba_part_tables b
WHERE a.table_owner IN ('AX','AR','GL')
AND a.table_name NOT LIKE 'HZ%' 
AND a.table_name NOT LIKE 'AX_DOC%'
AND a.partition_position = b.partition_count
AND b.owner = a.table_owner
AND b.table_name = a.table_name;

CURSOR c2(v_owner VARCHAR2,v_tname VARCHAR2) IS
SELECT high_value,partition_name
   FROM (SELECT * FROM dba_tab_partitions 
             WHERE table_owner = v_owner  
             AND   table_name  = v_tname 
         ORDER BY partition_position DESC) WHERE ROWNUM <3 :="i.s1;" begin="" c1="" current_max_value_number2="" current_max_value_number="" dba_tab_partitions.high_value="" dba_tab_partitions.partition_name="" dba_tab_partitions.table_name="" dba_tab_partitions="" execute="" for="" i="" if="" immediate="" in="" into="" loop="" number="" v_maxhvalue="" v_minhvalue="" v_newhvalue="" v_newpname="" v_oldtname="" v_rec="" v_statement="" varchar2=""> 0 THEN 
    FOR j IN c2(i.table_owner,i.table_name) LOOP
        IF i.table_name = v_oldtname THEN
        v_minhvalue := j.high_value;
     ELSE
        v_maxhvalue := j.high_value;   
     END IF;
     v_oldtname := i.table_name;
    END LOOP;
    v_newhvalue := TO_NUMBER(v_maxhvalue) + (TO_NUMBER(v_maxhvalue)-TO_NUMBER(v_minhvalue));
       v_newpname  := SUBSTR(i.partition_name,1,LENGTH(i.partition_name)-3)||
       LTRIM(TO_CHAR(SUBSTR(i.partition_name,LENGTH(i.partition_name)-2)+1,'099'));
       DBMS_OUTPUT.PUT_LINE('The table '|| i.table_owner || '.'|| i.table_name||' needs partition');
    DBMS_OUTPUT.NEW_LINE();
       DBMS_OUTPUT.PUT_LINE('Alter table '||i.table_name||' add partition '||v_newpname || 
       ' VALUES LESS THAN ('||v_newhvalue||')');
    DBMS_OUTPUT.PUT_LINE(' LOGGING NOCOMPRESS TABLESPACE '||i.tablespace_name);
    DBMS_OUTPUT.PUT_LINE(' PCTFREE '||i.pct_free);
    DBMS_OUTPUT.PUT_LINE(' INITRANS '||i.ini_trans||' MAXTRANS '||i.max_trans);
    DBMS_OUTPUT.PUT_LINE(' STORAGE    (INITIAL '||i.initial_extent);
    DBMS_OUTPUT.PUT_LINE(' NEXT '||i.next_extent);
    DBMS_OUTPUT.PUT_LINE(' MINEXTENTS '||i.min_extent);
    DBMS_OUTPUT.PUT_LINE(' MAXEXTENTS '||i.max_extent);
    DBMS_OUTPUT.PUT_LINE(' PCTINCREASE '||i.pct_increase);
    DBMS_OUTPUT.PUT_LINE(' BUFFER_POOL      DEFAULT)');
    DBMS_OUTPUT.NEW_LINE();
    END IF;
END LOOP;
END;