I need to be honest right up front here. I’ve aimed for the skies and only time will tell if I’ve fallen short 
.
Can it be done? Can you delve into the hex dumps, the byte headers, the acronyms all wrapped up in the magic that is an Oracle Database block with no slides, no code, no demos and no visuals? Who knows, but I gave it my best shot in this podcast episode.
Enjoy!
If you would really like a visual companion to the episode, you can download a screen shot of a symbolic block dump here
Hey,
> I often get a question on asktom.oracle.com: ‘How do I get the list of all the rows that are currently locked in an Oracle Database?’
> You cannot, because there is no structure that represents the list of all the locked rows in the Oracle Database.
Transactions are known from undo. Undo can also be used to find all the locked rows. It is exposed as FLASHBACK_TRANSACTION_QUERY https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/flashback.html#GUID-EBC8258B-03D9-4037-8B31-1A2EE4C73FCF
A simple test case shows how to find all the rows that are currently locked:
SQL> create table t as
2 select level n
3 from dual
4 connect by level
SQL> update t
2 set n = n
3 where mod(n, 2)=0;
2 rows updated.
SQL>
SQL> select dbms_transaction.local_transaction_id from dual;
LOCAL_TRANSACTION_ID
————————————————————————————————————————————————————————————————————
—————————————————————
2.4.465
SQL>
SQL> col xid old_v xid
SQL> select xid from v$transaction;
XID
—————-
02000400D1010000
SQL>
SQL>
SQL> col logon_user for a10
SQL> col table_name for a11
SQL> col table_owner for a11
SQL> col undo_sql for a80
SQL> select * from flashback_transaction_query where xid=hextoraw(‘&xid.’);
old 1: select * from flashback_transaction_query where xid=hextoraw(‘&xid.’)
new 1: select * from flashback_transaction_query where xid=hextoraw(‘02000400D1010000’)
XID START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL
—————- ———- ——— ———- ——— ———- ———— ———- ———– ———– ——————- ——————————————————————
02000400D1010000 11863202 11-MAR-21 1.8447E+19 TC 1 UPDATE T TC AAAFxYAABAAAJDpAAD update “TC”.”T” set “N” = ‘4’ where ROWID = ‘AAAFxYAABAAAJDpAAD’;
02000400D1010000 11863202 11-MAR-21 1.8447E+19 TC 2 UPDATE T TC AAAFxYAABAAAJDpAAB update “TC”.”T” set “N” = ‘2’ where ROWID = ‘AAAFxYAABAAAJDpAAB’;
02000400D1010000 11863202 11-MAR-21 1.8447E+19 TC 3 BEGIN
SQL>
SQL> select t.*
2 from t,
3 flashback_transaction_query
4 where xid = hextoraw(‘&xid.’)
5 and t.rowid = row_id;
old 4: where xid = hextoraw(‘&xid.’)
new 4: where xid = hextoraw(‘02000400D1010000’)
N
———-
2
4
True, but thats not the kind of query I want to be running on a system with thousands or millions of locked rows 🙂
nice podcast, thanks!
Learning is not a spectator sport 
Leave a reply to Mikhail Velikikh Cancel reply