Get max value in partition

Set serverout on size 1000000
declare
    cursor tab_part_cur IS
      select table_name,partition_name from user_tab_partitions where table_name='MESSAGE_LOG' order by 1,2;
    v_date date;
    v_pass integer := 0;
begin
    dbms_output.put_line('These partitions can be dropped.');
    for tab_part_rec in tab_part_cur
    loop
        exit when tab_part_cur%notfound;
        v_date := GET_MAX_LOG_TS(tab_part_rec.table_name,tab_part_rec.partition_name);
        if v_date <= (sysdate - 10) then
           dbms_output.put_line(tab_part_rec.table_name||' '||tab_part_rec.partition_name|| ' '||v_date );
        end if;
    end loop;
end;
/




CREATE  or replace FUNCTION GET_MAX_LOG_TS (
    p_TableName     IN VARCHAR2,
    p_PatitionName  IN VARCHAR2
) RETURN DATE
IS
   v_DateVal Date;
   v_string varchar2(400);
BEGIN      
       v_string := 'SELECT max(log_ts)  v_DateVal FROM MESSAGE_LOG PARTITION('||p_PatitionName||')';

dbms_output.put_line(v_string);
dbms_output.put_line(chr(10));
execute immediate v_string into into v_DateVal;;

    RETURN v_DateVal;
END GET_max_log_ts;
/

list max value in partition

function

CREATE OR REPLACE FUNCTION GET_HIGH_VALUE_AS_DATE (
    p_TableName     IN VARCHAR2,
    p_PatitionName  IN VARCHAR2
) RETURN DATE
IS
   v_LongVal LONG;
BEGIN
    SELECT HIGH_VALUE INTO v_LongVal
      FROM USER_TAB_PARTITIONS
     WHERE TABLE_NAME = p_TableName
       AND PARTITION_NAME = p_PatitionName;

    RETURN TO_DATE(substr(v_LongVal, 11, 19), 'YYYY-MM-DD HH24:MI:SS');
END GET_HIGH_VALUE_AS_DATE;
/

calls function
Set serverout on size 1000000
declare
    cursor tab_part_cur IS
      select table_name,partition_name from user_tab_partitions where table_name='BOMR_MESSAGE_LOG' order by 1,2;
    v_date date;
    v_pass integer := 0;
begin
    dbms_output.put_line('These partitions can be dropped.');
    for tab_part_rec in tab_part_cur
    loop
        exit when tab_part_cur%notfound;
        v_date := GET_HIGH_VALUE_AS_DATE(tab_part_rec.table_name,tab_part_rec.partition_name);
        if v_date <= (sysdate - 10) then
           dbms_output.put_line(tab_part_rec.table_name||' '||tab_part_rec.partition_name|| ' '||v_date );
        end if;
    end loop;
end;
/

# of partitions in schema

-- space used schema wise

select owner, sum(bytes/(1024*1024*1024))  from dba_segments group by rollup(owner)

-- find partitios, subpartition high level info along with # of partition

with

   temp_low as (

               select table_name, PARTITION_NAME from user_tab_partitions a where  PARTITION_POSITION in (

               select min(PARTITION_POSITION) from user_tab_partitions b where a.table_name=b.table_name)

              )

  ,temp_high as (

               select table_name, PARTITION_NAME from user_tab_partitions a where  PARTITION_POSITION in (

               select max(PARTITION_POSITION)-1 from user_tab_partitions b where a.table_name=b.table_name)

              )

select a.table_name, a.PARTITION_NAME, b.PARTITION_NAME, c.PARTITION_COUNT, decode(c.SUBPARTITIONING_KEY_COUNT,1,'YES','NO') as SUBPARTITION

from temp_low a, temp_high b, user_part_tables c

where a.table_name=b.table_name

and a.table_name=c.table_name

/

sample report

TABLE_NAME                     starting_partition     end_partition                        #         SUBP

------------------------------ ------------------ ------------------ ---------------- ------

BOS_MESSAGE_LOG                P_20130307         P_20140802                      514  NO