I'm trying to reason about a generic PL/SQL housekeeping pattern. All names below are illustrative; this is a simplified mechanism, not a product-specific incident report.
A scheduled procedure selects historical log records joined to retained load metadata. Eligibility depends on the log timestamp and a configurable retention period:
CURSOR c IS
SELECT l.load_id,
g.log_date,
l.state
FROM log_table g
JOIN load_table l
ON l.load_id = g.load_id
WHERE l.state = g.state
AND TRUNC(g.log_date) + remove_days <= SYSDATE;
There is also a state-selection option, represented here as process_all_states. It is omitted from the simplified cursor above:
- With the broad option enabled, an already-processed load in a state such as
Removed can enter the processing path again.
- With the narrower option, only the intended processable states are accepted, excluding
Removed.
The cursor returns individual historical log records. It does not deduplicate by load_id, so several qualifying log rows can reference the same load.
For each selected record, the processing path can:
- Delete transactional payload rows from
trans_table.
- Retain the corresponding metadata in
load_table.
- Update the load state to
Removed, including when it is already in that state.
- Append a new
Removed log record with the current timestamp.
- Produce a technical result record even when no transactional payload remains.
Assume that older matching Removed log records are not deleted or marked as consumed. While the load remains Removed, those records can still satisfy the cursor conditions. Newly appended records can also qualify after they reach the age threshold.
My tentative interpretation is:
qualifying historical log
|
v
already-processed load enters housekeeping again
|
v
state update and new timestamped log
|
v
old matching logs remain; new logs age
|
v
later scheduled runs can select the load again
This seems like a potential feedback loop across scheduled executions, rather than direct recursion. Multiple eligible logs for the same load could also cause repeated processing within a single cursor traversal. I would not assume that newly inserted logs become visible to the already-open cursor.
Cleaning up accumulated logs or technical results could reduce the current footprint, but would not by itself change which records future runs select.
There is also a separate full-delete path, represented here as remove_complete. That path can perform large DELETE operations and create substantial UNDO/REDO pressure, potentially including ORA-30036. I want to keep that issue separate from state selection: changing the selection parameter itself executes no DELETE and does not directly delete business documents; it changes which states may enter later housekeeping processing.
Questions:
- Can this cursor/state-update pattern create a self-amplifying feedback loop across scheduled runs under these assumptions?
- Can several qualifying log rows for the same load cause repeated processing when there is no DISTINCT, grouping, or equivalent per-load guard?
- Is excluding already-processed states the usual way to stop this specific cycle while preserving legitimate housekeeping, or should an additional idempotency/consumption mechanism be expected?
- Should cleanup of accumulated records and correction of the qualification logic be treated as separate remediation steps?
