Очистка логов скорой помощи

В среднем получается порядка 80 Мб за месяц в табличных пространствах LOG_DATA, LOG_IDX.

Внимание! Выполнять осторожно (возможны проблемы с блокировками)!

-- Очищает логи, оставляет данные за последние 6 месяца
BEGIN
  DELETE FROM mis_log.user_info_log
   WHERE id_user_msg_log IN (SELECT id
                               FROM mis_log.tuser_msg_log
                              WHERE dt < ADD_MONTHS (SYSDATE, -6));

  COMMIT;

  DELETE FROM mis_log.tuser_msg_log
   WHERE dt < ADD_MONTHS (SYSDATE, -6);

  COMMIT;
END;

ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$i ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$i SHRINK SPACE;
ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$r ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.dr$idx_emergency_call_log_ft$r SHRINK SPACE;
ALTER TABLE mis_log.tuser_msg_log ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.tuser_msg_log SHRINK SPACE;
ALTER TABLE mis_log.emergency_call_log ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.tmsg_source ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.tmsg_source SHRINK SPACE;
ALTER TABLE mis_log.user_info_log ENABLE ROW MOVEMENT;
ALTER TABLE mis_log.user_info_log SHRINK SPACE;

ALTER INDEX mis_log.pk_tuser_action_log REBUILD  NOLOGGING;
ALTER INDEX mis_log.dr$idx_emergency_call_log_ft$x REBUILD  NOLOGGING;
ALTER INDEX mis_log.idx_emergency_call_id REBUILD  NOLOGGING;
ALTER INDEX mis_log.pk_user_info_log REBUILD  NOLOGGING;
ALTER INDEX mis_log.pk_tmsg_source REBUILD  NOLOGGING;
ALTER INDEX mis_log.pk_tuser_msg_log REBUILD  NOLOGGING;
ALTER INDEX mis_log.ix_user_info_log$key_tbl_name REBUILD  NOLOGGING;
ALTER INDEX mis_log.idx_emergency_call_log_side REBUILD  NOLOGGING;

ALTER TABLESPACE log_data COALESCE;
ALTER TABLESPACE log_idx COALESCE;

declare
  v_i Integer;
BEGIN
  for v_i in 1 .. 8 loop
  
    DELETE FROM mis_log.user_info_log
     WHERE id_user_msg_log IN (SELECT id
                                 FROM mis_log.tuser_msg_log
                                WHERE dt < ADD_MONTHS(SYSDATE, -16)
                                  and rownum < 250);
  
    COMMIT;
  
    DELETE FROM mis_log.tuser_msg_log
     WHERE dt < ADD_MONTHS(SYSDATE, -16)
       and rownum < 250;
  
    COMMIT;
  end loop;
END;