New podcast episode! Block internals

Posted by

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 Smile.

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

3 comments

  1. 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

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.