I was required to find the oldest and newest interval partition to be able to do some automated maintenance, but found query USER_TAB_PARTITIONS, ALL_TAB_PARTITIONS and DBA_TAB_PARTITIONS was a bit tricky as the HIGH_VALUE is a LONG data type 😦

A quick search on the web and I found a perfect solution from my good friend Tim Hall (Oracle Base), who created a function to convert the LONG into a DATE by executing the HIGH VALUE 🙂

Script: part_hv_to_date.sql

For ease of access, script below (please check Tim’s blog for updates and always ask for permission if you’re going to quote 🙂 ):

CREATE OR REPLACE FUNCTION part_hv_to_date (p_table_owner IN VARCHAR2,
 p_table_name IN VARCHAR2,
 p_partition_name IN VARCHAR2)
 RETURN DATE
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/miscellaneous/part_hv_to_date.sql
-- Author : Tim Hall
-- Description : Create a function to turn partition HIGH_VALUE column to a date.
-- Call Syntax : @part_hv_to_date
-- Last Modified: 19/01/2012
-- Notes : Has to re-select the value from the view as LONG cannot be passed as a parameter.
-- Example call:
--
-- SELECT a.partition_name, 
-- part_hv_to_date(a.table_owner, a.table_name, a.partition_name) as high_value
-- FROM all_tab_partitions a;
--
-- Does no error handling. 
-- -----------------------------------------------------------------------------------
AS
 l_high_value VARCHAR2(32767);
 l_date DATE;
BEGIN
 SELECT high_value
 INTO l_high_value
 FROM all_tab_partitions
 WHERE table_owner = p_table_owner
 AND table_name = p_table_name
 AND partition_name = p_partition_name;
 
 EXECUTE IMMEDIATE 'SELECT ' || l_high_value || ' FROM dual' INTO l_date;
 RETURN l_date;
END;
/

With this you can get the oldest interval partition by running the following SQL:

SELECT PARTITION_NAME
FROM DBA_TAB_PARTITIONS P
WHERE TABLE_OWNER = 'ZEDDBA'
AND TABLE_NAME = 'EXAMPLE'
AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
SELECT MIN(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = P.TABLE_OWNER
AND TABLE_NAME = P.TABLE_NAME);

Output:

SQL> SELECT PARTITION_NAME
   2 FROM DBA_TAB_PARTITIONS P
   3 WHERE TABLE_OWNER = 'ZEDDBA'
   4 AND TABLE_NAME = 'EXAMPLE'
   5 AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
   6 SELECT MIN(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
   7 FROM DBA_TAB_PARTITIONS
   8 WHERE TABLE_OWNER = P.TABLE_OWNER
   9 AND TABLE_NAME = P.TABLE_NAME);

PARTITION_NAME
--------------------
SYS_P10590

SQL>

And the newest interval partition by running the following SQL:

SELECT PARTITION_NAME
FROM DBA_TAB_PARTITIONS P
WHERE TABLE_OWNER = 'ZEDDBA'
AND TABLE_NAME = 'EXAMPLE'
AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
SELECT MAX(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = P.TABLE_OWNER
AND TABLE_NAME = P.TABLE_NAME);

Output:

SQL> SELECT PARTITION_NAME
   2 FROM DBA_TAB_PARTITIONS P
   3 WHERE TABLE_OWNER = 'ZEDDBA'
   4 AND TABLE_NAME = 'EXAMPLE'
   5 AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
   6 SELECT MAX(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
   7 FROM DBA_TAB_PARTITIONS
   8 WHERE TABLE_OWNER = P.TABLE_OWNER
   9 AND TABLE_NAME = P.TABLE_NAME);

PARTITION_NAME
--------------------
SYS_P17718

SQL>

Updates

8th July 2017, changed the query so the sub-query join onto the parent query to avoid having to set the predicates in the sub-query.

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)