Find new partitions to be created on the table :
DECLARE
long_var LONG;
var_var VARCHAR2(2000);
part_var VARCHAR2(30);
BEGIN
dbms_output.put_line('Create Partitions for following tables. The last partition on the table is...
');
for c in (
select p.table_owner, p.table_name, p.partition_name,t.PARTITIONING_TYPE,
p.high_value
from dba_tab_partitions p , dba_part_tables t
where partition_position = (
select max(partition_position) -1
from dba_tab_partitions
where table_name = p.table_name
and table_owner = p.table_owner
)
and p.table_owner not in ('SYS','SYSTEM')
and p.table_owner = t.owner
and p.table_name = t.table_name
and t.PARTITIONING_TYPE = 'RANGE'
order by p.table_owner, p.table_name
)
loop
var_var := substr(c.high_value,1,2000);
--DBMS_OUTPUT.PUT_LINE(' Out :' || substr(c.high_value,1,2000) );
--DBMS_OUTPUT.PUT_LINE(' Out :' || var_var);
if var_var like '%TO_DATE%' then
--dbms_output.put_line(rpad(c.table_owner||'.'||c.table_name||' : '||c.partition_name,65,' ') ||' '||
-- substr( c.high_value,instr(c.high_value,' ')+1, instr( substr(c.high_value,instr(c.high_value,' ')+1),' ')-1 ) );
begin
select 'Pending' into part_var from dual
where to_date(substr(c.high_value,instr(c.high_value,' ')+1,10 ),'YYYY-MM-DD') - 100 < trunc(sysdate) ;
--dbms_output.put_line('Create Partitions after ...'||part_var);
dbms_output.put_line(rpad(c.table_owner||'.'||c.table_name,65,' ')||' : '||rpad(c.partition_name,65,' ') ||' '||
substr( c.high_value,instr(c.high_value,' ')+1, instr( substr(c.high_value,instr(c.high_value,' ')+1),' ')-1 ) );
exception
When no_data_found then
dbms_output.put_line('');
--dbms_output.put_line('No data found ...'||substr(c.high_value,instr(c.high_value,' ')+1,10 ));
end;
end if;
end loop;
END;
/
