Categories
dba sql

last partition

if you really need to quickly find the latest partition per table, I have written this little gem


WITH FUNCTION d (b BLOB, len number) RETURN DATE IS
d DATE;
BEGIN
IF DBMS_LOB.SUBSTR (b, 1, 1) = hextoraw('07') and len=83
THEN
DBMS_STATS.convert_raw_value (DBMS_LOB.SUBSTR (b, 12, 2), d);
ELSE
d := NULL;
END IF;
RETURN d;
END;
SELECT
u.name owner,
o.name table_name,
max(d (bhiboundval, hiboundlen)) last_partition
FROM sys.tabpart$ tp
JOIN sys.obj$ o USING (obj#)
JOIN sys.user$ u ON u.user# = o.owner#
WHERE u.name='SCOTT'
group by u.name, o.name
order by last_partition desc;

It doesn’t cover all partitioning type, but it is pretty fast and simple

By Laurent Schneider

Oracle Certified Master

4 replies on “last partition”

You could also do the conversion manually, it is a bit less simple, but not slower
SELECT
u.name owner,
o.name table_name,
max(case when DBMS_LOB.SUBSTR (bhiboundval, 1, 1) = hextoraw('07')
then
to_date(
to_char(
(utl_raw.cast_to_binary_integer( cast(DBMS_LOB.SUBSTR (bhiboundval, 1, 2) as raw(1)))-100)*1000000000000+ -- Century
(utl_raw.cast_to_binary_integer( cast(DBMS_LOB.SUBSTR (bhiboundval, 1, 3) as raw(1)))-100)*10000000000+ -- Year
(utl_raw.cast_to_binary_integer( cast(DBMS_LOB.SUBSTR (bhiboundval, 1, 4) as raw(1))))*100000000+ -- Month
(utl_raw.cast_to_binary_integer( cast(DBMS_LOB.SUBSTR (bhiboundval, 1, 5) as raw(1))))*1000000+ -- Day
(utl_raw.cast_to_binary_integer( cast(DBMS_LOB.SUBSTR (bhiboundval, 1, 6) as raw(1)))-1)*10000+ -- Hour
(utl_raw.cast_to_binary_integer( cast(DBMS_LOB.SUBSTR (bhiboundval, 1, 7) as raw(1)))-1)*100+ -- Minute
(utl_raw.cast_to_binary_integer( cast(DBMS_LOB.SUBSTR (bhiboundval, 1, 8) as raw(1)))-1)*1 -- Second
,'00000000000000')
,'YYYYMMDDHH24MISS')
end
) last_partition
FROM sys.tabpart$ tp
JOIN sys.obj$ o USING (obj#)
JOIN sys.user$ u ON u.user# = o.owner#
WHERE u.name='SCOTT'
group by u.name, o.name
order by last_partition desc;

Hello Laurent,

thank you for this query.
This will be my new tool for quarterly check for a new partitions.

Nice manipulation with hiboundval LONG column. Useful for some other cases too.

Regards,
Marko

Leave a Reply

Your email address will not be published.