Segue uma procedure Oracle funcional que faz expurgo e cria novas partições para uma determinada tabela, isso é muito útil como fonte de consulta:
CREATE OR REPLACE PROCEDURE pr_purge_gwbroadgacpartitions (dataref NUMBER) IS
TYPE tVetor IS VARRAY(100) of VARCHAR2(5); -- Cria um tipo como array.
mmddref VARCHAR2(4);
dataref_ini DATE;
dataref_end DATE;
part_name_one VARCHAR2(9);
sqlcmd VARCHAR2(500);
year_ini VARCHAR2(4);
year_now VARCHAR2(4);
instances_pa tVetor; -- Define uma variável como array
v_future_date VARCHAR2(9);
num_rows integer;
--Cursores que fazem interação no resultado do SELECT
CURSOR parts_cur_same_year(part_name IN VARCHAR2, inst IN VARCHAR2) RETURN user_tab_partitions%ROWTYPE IS
SELECT * FROM user_tab_partitions WHERE table_name = 'STATUS_REPO' and partition_name < part_name and partition_name LIKE inst ORDER BY partition_name ASC;
CURSOR parts_cur_another_year(part_name IN VARCHAR, inst IN VARCHAR2) RETURN user_tab_partitions%ROWTYPE IS
SELECT * FROM user_tab_partitions WHERE table_name = 'STATUS_REPO' and partition_name > part_name and partition_name LIKE inst ORDER BY partition_name ASC;
parts_record user_tab_partitions%ROWTYPE; -- Define a variável como tipo linha da tabela
BEGIN
dataref_ini := TO_DATE(dataref,'YYYYMMDD') - 91; -- Transforma o parâmetro informado como string em data e subtrai dias.
year_ini := TO_CHAR(dataref_ini, 'YYYY'); -- Recupera o ano da data como string.
year_now := TO_CHAR(SYSDATE, 'YYYY');
instances_pa := tVetor('PART1', 'PART2', 'PART3', 'PART4', 'PART5'); -- Inicializa o array
--Verifica o ano do parametro e o mesmo do ano atual da maquina, essa verificacao e importante porque a particao nao informa o ano.
IF year_ini = year_now THEN
mmddref := TO_CHAR(dataref_ini,'MMDD'); -- Mes e ano da data informada.
FOR i IN instances_pa.FIRST..instances_pa.LAST -- navega entre as instâncias do gateway.
LOOP
part_name_one := instances_pa(i) || mmddref; -- Nome da instancia mais dia e mes.
OPEN parts_cur_same_year(part_name_one, instances_pa(i) || '%'); -- Executa o cursor
LOOP -- Faz loop do cursor
BEGIN
FETCH parts_cur_same_year INTO parts_record; -- Armazena cada linha na variável
EXIT WHEN (parts_cur_same_year%NOTFOUND) OR (parts_cur_same_year%ROWCOUNT = 0);
sqlcmd := 'ALTER TABLE STATUS_REPO DROP PARTITION ' || parts_record.partition_name; -- Apaga a partição
EXECUTE IMMEDIATE sqlcmd;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
COMMIT;
END LOOP;
CLOSE parts_cur_same_year;
END LOOP;
ELSE
dataref_end := TO_DATE(dataref,'YYYYMMDD') + 91; -- Caso os anos sejam diferentes ele dropa as particoes a frente
mmddref := TO_CHAR(dataref_end,'MMDD');
FOR i IN instances_pa.FIRST..instances_pa.LAST
LOOP
part_name_one := instances_pa(i) || mmddref;
OPEN parts_cur_another_year(part_name_one, instances_pa(i) || '%');
LOOP
BEGIN
FETCH parts_cur_another_year INTO parts_record;
EXIT WHEN (parts_cur_another_year%NOTFOUND) OR (parts_cur_another_year%ROWCOUNT = 0);
sqlcmd := 'ALTER TABLE STATUS_REPO DROP PARTITION ' || parts_record.partition_name;
EXECUTE IMMEDIATE sqlcmd;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
END LOOP;
COMMIT;
CLOSE parts_cur_another_year;
END LOOP;
END IF;
-- Verifica se existe a particao DD+30, caso não, cria
FOR i IN instances_pa.FIRST..instances_pa.LAST
LOOP
mmddref := TO_CHAR(SYSDATE+30,'MMDD');
SELECT COUNT(*) INTO num_rows FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'STATUS_REPO' AND PARTITION_NAME = '' || instances_pa(i) || mmddref || '' ORDER BY PARTITION_NAME ASC; --Armazena o resultado na variável num_rows
IF num_rows <= 0 THEN
sqlcmd := 'ALTER TABLE ''STATUS_REPO'' add PARTITION ' || instances_pa(i) || mmddref || ' VALUES ('''|| instances_pa(i) || mmddref ||''') ' || 'TABLESPACE ABCD PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 2 STORAGE (INITIAL 1048576 NEXT 1064960 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 )'; --Cria uma nova partição
EXECUTE IMMEDIATE sqlcmd;
COMMIT;
END IF;
END LOOP;
END;
