SOA DB Maintenance

soa_db_maintenance.sql

-- Drop Message Reports Data
truncate table OSB1213_SOAINFRA.WLI_QS_REPORT_DATA;
alter table OSB1213_SOAINFRA.WLI_QS_REPORT_DATA DISABLE constraint FK_WLI_QS_REPORT_DATA;
truncate table OSB1213_SOAINFRA.WLI_QS_REPORT_ATTRIBUTE;
alter table OSB1213_SOAINFRA.WLI_QS_REPORT_DATA ENABLE constraint FK_WLI_QS_REPORT_DATA;

-- Create Message Report Index
create index OSB1213_SOAINFRA.IDX$$_36870001 on
    OSB1213_SOAINFRA.WLI_QS_REPORT_ATTRIBUTE("DB_TIMESTAMP","MSG_GUID");
    
-- Scripts Drop Partições SOA

set serveroutput on size 30000;

DECLARE 
  v_schema varchar2(100) := '&SCHEMA';
BEGIN
  FOR part IN (with xml as (
  select  dbms_xmlgen.getxmltype('
     select partition_name, high_value, table_owner, table_name from dba_tab_partitions'
  ) x
  from dual
), vals as (
  select TO_DATE(REGEXP_REPLACE("hv", 'TIMESTAMP'' (.*)''', '\1'), 'yyyy-mm-dd HH24:MI:SS')  hv,
         "ow" ow,
         "tbl" tbl,
         "pn" pn
  from   xml,
          xmltable(
            '/ROWSET/ROW' 
            passing xml.x 
            columns 
               "hv" path 'HIGH_VALUE',
               "pn" path 'PARTITION_NAME',
               "ow" path 'TABLE_OWNER',
               "tbl" path 'TABLE_NAME'
))
  select *
  from   vals
  where ow = v_schema
    and pn not in ('P0')
    and hv < to_date('2018-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS')
  order by 1)
  LOOP
    dbms_output.put_line('Dropping partition from: ' || v_schema || '.' || part.tbl || ' with name: ' || part.pn || ' with max date: ' || part.hv);
    dbms_output.put_line('alter table ' || v_schema || '.' || part.tbl || '  drop partition ' || part.pn);
    EXECUTE IMMEDIATE 'alter table ' || v_schema || '.' || part.tbl || '  drop partition ' || part.pn;
  END LOOP;
END;


DECLARE 
  v_schema varchar2(100) := '&SCHEMA';
BEGIN
  FOR idx IN (SELECT INDEX_NAME FROM ALL_INDEXES WHERE OWNER = v_schema AND STATUS <> 'VALID')
  LOOP
    dbms_output.put_line( 'alter index ' || v_schema || '.' || idx.index_name || ' rebuild online;');
    EXECUTE IMMEDIATE 'alter index ' || v_schema || '.' || idx.index_name || ' rebuild online';
  END LOOP;
END;