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

rebuild unusable indexes

Set serverout on size 1000000
declare
    cursor idx_cur IS
      select owner, index_name  from dba_indexes where status = 'UNUSABLE';
    v_string varchar2(400);
   
begin
    dbms_output.put_line('These indexes are in unusable.');
    for idx_rec in idx_cur
    loop
        exit when idx_cur%notfound;
           dbms_output.put_line(idx_rec.owner||' '||idx_rec.index_name);
           v_string := 'alter index '|| idx_rec.owner ||'.'|| idx_rec.index_name||' rebuild;';
           dbms_output.put_line(v_string);
           dbms_output.put_line(chr(10));
           execute immediate v_string ;
    end loop;
end;
/

stream_pool_size

check the spilling status and pool size recommendation of your Streams pool with following query.

select inst_id,STREAMS_POOL_SIZE_FOR_ESTIMATE,estd_spill_time, estd_unspill_time, streams_pool_size_factor

from gv$streams_pool_advice order by 1,5;