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

Your email address will not be published.

*