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
Nice query – fast, simple and very useful for me.
Too bad that we cannot use functions in the WITH clause in earlier versions.
Thanks!
Regards,
Marko
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;
this is only valid for partitions where the leading column is of the right datatype (date). Other datatypes must be converted differently.
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