oracle 10g - Monitor large row deletions

Question

how can i monitor rows being deleted(say 20 rows)? consider, i am a dba and monitoring an oracle database.. i have to get an alert if someone deletes more than 20 rows.. i should avoid a trigger since it is costly.. is there any other way around?

how can i parse the redo log and trap the sql that might have caused a bulk row delete? my scenario is, as soon as a bulk row delete happens, the DBA should be intimated.. any other way other than trigger? i ve been using a trigger and I am looking for a way to avoid triggers..

Answer 1

So, to review: you're asking for a way to monitor, but without using a trigger because it's "costly" to execute that code on every DELETE statement.

Basically, you're trying to find a way to monitor without the overhead of monitoring. That's not going to be possible. A trigger will do the job nicely, I highly doubt you'll notice any performance difference for a simple check on the number of rows being deleted.

Answer 2

What were you hoping to do if such a deletion occurs? Unless you've saved the deleted rows somewhere (which would require a trigger) you can't get them back, nor can you even know what they were.

If deleting rows is a problem and recovery is likely to be needed, don't allow rows to be deleted: add a "deleted" column and set its value in an UPDATE rather than a DELETE (or add a trigger.)

Alternatively, maybe you could find a way to parse the redo logs? I've never tried, but I imagine it's possible given enough effort.

Personally, I think I'd:

1. re-examine the "need" to know about deletions and if I can't change that,
2. overcome my fear of triggers.

Answer 3

Has anyone on your team actually measured the cost of using a trigger to do this? What was the trigger code, and how was it measured?

Answer 4

Use a procedure for your deletion process and add a SQL%ROWCOUNT to monitor how much rows have been deleted. Here is an example from Oracle Documentation on how to use SQL%ROWCOUNT:

CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE
  mgr_no NUMBER(6) := 122;
BEGIN
  DELETE FROM employees_temp WHERE manager_id = mgr_no;
  DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/

If you want to use the FORALL statement you can use SQL%BULK_ROWCOUNT the same way as SQL%ROWCOUNT here is another example from the Oracle Documentation:

CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(30, 50, 60);
BEGIN

   FORALL j IN depts.FIRST..depts.LAST
      DELETE FROM emp_temp WHERE department_id = depts(j);

   -- How many rows were affected by each DELETE statement?
   FOR i IN depts.FIRST..depts.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted ' || SQL%BULK_ROWCOUNT(i) || ' rows.');
   END LOOP;

END;
/

If you really have or want to avoid triggers, i think you should go that way.

Answer 5

If you don't want to use a trigger your option as a dba is to install and setup database auditing. With the auditing feature you can track who did what and when and who should be notified. You can also get the deleted rows back by using flash back feature if the delete period have not exceeded the undo retention period. After which point then you can use logminer to get the deleted rows back from the archive/redo logs by mining the logs for that time period.

Answer 6

"how can i parse the redo log and trap the sql that might have caused a bulk row delete? my scenario is, as soon as a bulk row delete happens" What sort of delete are you talking about ?

A DELETE FROM table WHERE pk IN (1,....50) can delete fifty rows in one statement execution

FORALL i IN 1..50
 DELETE FROM table WHERE pk = i;

will have fifty executions, each deleting a single row in one transaction.

FOR i IN 1..50 LOOP
 DELETE FROM table WHERE pk = i;
 COMMIT;
END LOOP;

will have fifty executions, each deleting a single row in fifty transactions in one session.

So are you looking for deletes of more than 20 rows in a single statement, transaction or session ? Taking the simplest option. Logically, after each DELETE statement, you'd need to look at the number of rows processed and, if greater than 20, create an audit record. Technically, the way to execute such a test is to create an AFTER DELETE row level trigger on the table. Any alternative would have exactly the same performance impact as it would need to do exactly the same work. Transaction or session level limits would be similar, but you'd need the trigger to maintain a count of the number of records deleted (eg in a package level variable).