Explicit table locking to disable DELETES?

Question

Using Oracle 11gR2:

We already have a process that cleans up particular tables by deleting records from them that are past a specified retention date (based on the comparison between the timestamp from when the record finished processing and the retention date). I am currently writing code that will alert my team if this process fails. The only way I can see this process possibly failing is if DELETEs are disabled on the particular table it is trying to clean up.

I want to test the alerts to make sure they work and look correct by having the process fail. If I temporarily exclusively lock the table, will that disable DELETEs and cause the procedure that deletes records to fail? Or does it only disable DDL operations? Is there a better way to do this?

Answer 1

Assuming that "fail" means "throw an error" rather than, say, exceeding some performance bound, locking the table won't accomplish what you want. If you locked every row via a SELECT FOR UPDATE in one session, your delete job would block forever waiting for the first session to release its lock. That wouldn't throw an error and wouldn't cause the process to fail for most definitions. If your monitoring includes alerts for jobs that are running longer than expected, however, that would work well.

If your monitoring process only looks to see if the process ran and encountered an error, the easiest option would be to put a trigger on the table that throws an error when there is a delete. You could also create a child table with a foreign key constraint that would generate an error if the delete tried to delete the parent row while a child row exists. Depending on how the delete process is implemented, you probably could engineer a second process that would produce an ORA-00060 deadlock for the process you are monitoring but that is probably harder to implement than the trigger or the child table.