How to find the latest SQL statements within the database

To count the rows deleted just after the delete statement use SQL%ROWCOUNT. Here is a demonstration.

--Setup.
drop table t1;
create table t1 as (select level x from dual connect by level <=5);

set serveroutput on size 1000000 format wrapped
DECLARE
   vCount Number(3);
BEGIN
   DELETE FROM T1 WHERE rownum<=2;
   vCount := SQL%ROWCOUNT;  --<<---<<---<<---<<---<<---<<---<<---<<---
   DBMS_Output.Put_Line('RowCount:' || vCount);
END;
/

For your broader question you can look into tracing, auditing, flashback database, flashback query, logminer, materialized views, streams, and change data capture (not necessarily in that order). We would need more details about what data you need and why before directing you any more specifically.

The reason your index isn't being used is that the two following SELECT are fundamentally different to the optimizer:

SELECT rows FROM table WHERE status = PROCESSED;

SELECT rows FROM table WHERE status = 0;

In the first case the database will interpret PROCESSED as a variable, and its value, even if constant won't be learned until execution time. An index on status will be used only if status has a strong selectivity for all values (ie there are many different values). Since you have only 3 values, Oracle makes a FULL SCAN since from its point of view any of the 3 values could be used.

In the second case, an index will be used if you have statistics on this column that show that the value 0 is very selective (ie there are few rows processed). Oracle knows at compile time that this value won't change thus an index scan will always be effective.

So, if you're sure that there are few rows processed, you will have to help the optimizer, for example with an hint like /*+ FIRST_ROWS*/.

Or you could use comments in your SQL: 0 -- PROCESSED.

Also remember that FULL SCAN are not evil.