Add Oracle range partitions quickly

Find the partition with the maximum HIGH_VALUE and see if you need to add new RANGE partitions which use date partitioning.

I hear Oracle 11g will do this automatically. About time!

USER's partitions

select table_name, partition_name, high_value from user_Tab_partitions a
where partition_position in (
select max(partition_position) from user_tab_partitions b
where b.table_name=a.table_name
group by table_name)

You can enhance this script further by investigating all partitions in your database when logged in as SYSDBA and find out the maxiumum partition HIGH_VALUE for all user schemas (except SYS,SYSTEM) in your database as follows:

DBA (All) partitions

SELECT a.table_owner,
a.table_name ,
a.partition_position ,
b.high_value
FROM dba_tab_partitions b,
(SELECT table_owner ,
table_name ,
MAX(partition_position) partition_position
FROM
(SELECT p.table_owner ,
p.table_name ,
p.partition_position,
p.high_value
FROM dba_tab_partitions p
WHERE p.table_owner NOT IN ('SYS','SYSTEM')
ORDER BY p.partition_position DESC,
p.table_name
)
GROUP BY table_owner,
table_name
) a
WHERE a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.partition_position = b.partition_position
order by a.table_owner