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 thoughts on “last partition

  1. Marko Sutic

    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

  2. Laurent Schneider Post author

    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;

  3. Marko Sutic

    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

Comments are closed.