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

4 Comments

  • 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;

  • 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