Hello, I'm working on a legacy system where programmers abused the use of triggers. I have a hard time identifying the impact that each single commit has on the system. Is there any way to get the rowid of all changed records in all database tables, in the transaction, before committing?
Not really.
We don't have a "list of rows locked" in the database, because that would then be a finite resource that needs to be managed. The lock information for a row is stored on the row itself. For example
SQL> create table t as select * from scott.emp;
Table created.
SQL> select * from v$lock
2 where type in ('TM','TX');
no rows selected
SQL> delete from t where rownum = 1;
1 row deleted.
SQL> select * from v$lock
2 where type in ('TM','TX');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000001F3B56778A8 000001F3B56778D0 132 TM 95042 0 3 0 2 0 3
00007FFC48B007D0 00007FFC48B00808 132 TX 655381 41021 6 0 2 0 3
I have two entries
TM- the lock on the table so you can't drop it while a transaction is open, and
TX - a row is locked
But if I delete more rows
SQL> delete from t;
13 rows deleted.
SQL> select * from v$lock
2 where type in ('TM','TX');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000001F3AC633F18 000001F3AC633F40 132 TM 95042 0 3 0 24 0 3
00007FFC48B007D0 00007FFC48B00808 132 TX 655381 41021 6 0 24 0 3
Notice we dont get LOTS of TX locks.
In terms of working out what triggers are doing, probably your best way would be sql trace. A tkprof formatted file shows the recursive depth for each statement, so when the depth goes up, you're probably in a trigger.
Other options would be creating a materialized view log on every table to track rowid's...but that will incur an overhead, or utilise something like Streams/Goldengate to capture changes but that's a major undertaking for just a diagnostic exercise