Find if you need to add new partitions
                                                 Last update (2010-07-09 21:11:28)
                                                                                                                   Date added (2008-02-29 08:44:19)

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;


CURRENT_MAX_VALUE_NUMBER NUMBER;
CURRENT_MAX_VALUE_NUMBER2 NUMBER;
v_statement   VARCHAR2(200);
v_rec        dba_tab_partitions%ROWTYPE;
v_oldtname   dba_tab_partitions.table_name%TYPE := NULL;
v_maxhvalue  dba_tab_partitions.high_value%TYPE;
v_minhvalue  dba_tab_partitions.high_value%TYPE;
v_newhvalue  dba_tab_partitions.high_value%TYPE;
v_newpname   dba_tab_partitions.partition_name%TYPE;
BEGIN
FOR i IN c1 LOOP
	v_statement           := i.s1;
    EXECUTE IMMEDIATE v_statement INTO CURRENT_MAX_VALUE_NUMBER;
    IF CURRENT_MAX_VALUE_NUMBER > 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;

Tip:It works for partitions based on NUMBER columns, not DATE