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 Replies to “last partition”

  1. 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. 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. 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.

*