Skip to Main Content
  • Questions
  • Get the list of all records changed in the same transaction

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Marciel.

Asked: January 16, 2023 - 9:06 pm UTC

Last updated: January 18, 2023 - 7:12 am UTC

Version: 19

Viewed 1000+ times

You Asked

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?

and Connor said...

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


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

UnReal application testing

A reader, January 17, 2023 - 12:40 pm UTC

create table t1(n number primary key, s varchar2(100 char));

-- you session...
exec dbms_output.put_line(dbms_transaction.local_transaction_id(true))

3.1.46841

select current_scn from v$database;

CURRENT_SCN
-----------
  102115974

insert into t1(n, s) values(123,'abc');
update t1 set s='xyz' where n=123;
delete t1 where n=123;

select current_scn from v$database;

CURRENT_SCN
-----------
  102116532


In the CDB
select any_value(member)
from v$log l
join v$logfile f using (group#)
where l.first_change# <= 102116532 and l.next_change# > 102115974
group by group#;

ANY_VALUE(MEMBER)              
-------------------------------
/oracle/DB213/data/DB213-3A.orl

begin
  dbms_logmnr.add_logfile('/oracle/DB213/data/DB213-3A.orl');
  dbms_logmnr.start_logmnr(
    options => dbms_logmnr.dict_from_online_catalog);
end;
/

select scn, operation, seg_name, row_id, sql_redo from gv$logmnr_contents
where xidusn=3 and xidslt=1 and xidsqn=46841;

       SCN OPERATION    SEG_NAME ROW_ID             SQL_REDO                                           
---------- ------------ -------- ------------------ ---------------------------------------------------
 102115906 START                 AAAAAAAAAAAAAAAAAA set transaction read write;                        
 102115906 INTERNAL              AAAAAAAAAAAAAAAAAA                                                    
 102116493 INSERT       T1       AAAX73AALAAAAa1AAA insert into "2"."T1"("N","S") values ('123','abc');
 102116495 UNSUPPORTED  T1       AAAX73AALAAAAa1AAA Unsupported                                        
 102116495 DELETE       T1       AAAX73AALAAAAa1AAA delete from "2"."T1" where "N" = '123' and "S" = 'x
                                                    yz' and ROWID = 'AAAX73AALAAAAa1AAA';              

 102116508 COMMIT                AAAAAAAAAAAAAAAAAA commit;                                            

begin
  dbms_logmnr.end_logmnr;
end;
/

Connor McDonald
January 18, 2023 - 7:12 am UTC

Nice but I think covered by my comment:

"but that's a major undertaking for just a diagnostic exercise"

:-)


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library