Skip to Main Content
  • Questions
  • Delay in row commit or Uncommited rows by Application

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Balu.

Asked: February 16, 2018 - 1:42 pm UTC

Last updated: February 20, 2018 - 1:10 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Tom,

Application(.Net) component is calling SP and creates an entry into few tables and sometimes there is delay in committing these transactions by .Net component or there is transaction left uncommitted in Oracle DB. we are aware of this issue.

As DB developer, how to find how much delay between insert and commit for that session. And what are uncommitted rows for that session.

Thank you
Balu

and Connor said...

You could get an *approximation* using ora_rowscn

SQL> create table t ( x date );

Table created.

SQL>
SQL> insert into t values ( sysdate );

1 row created.

SQL> host sleep 30 
SQL> commit;

Commit complete.

SQL> select x, ora_rowscn from t;

X                   ORA_ROWSCN
------------------- ----------
19/02/2018 17:03:04 1.4816E+13

SQL> select x, scn_to_timestamp(ora_rowscn) from t;

X                   SCN_TO_TIMESTAMP(ORA_ROWSCN)
------------------- ---------------------------------------
19/02/2018 17:03:04 19-FEB-18 05.03.45.000000000 PM


but I stress, that is only at best an approximation.

You can also do some data mining with flashback query for a particular transaction ID, eg

SQL> select x
  2             versions_starttime
  3            ,versions_xid
  4            ,versions_operation
  5  from t versions between scn minvalue and maxvalue;

VERSIONS_STARTTIME  VERSIONS_XID     V
------------------- ---------------- -
19/02/2018 17:03:04 010018009CAE0000 I

SQL>
SQL> select commit_timestamp
  2  from   flashback_transaction_query
  3  where  xid = hextoraw('010018009CAE0000');

COMMIT_TIMESTAMP
-------------------
19/02/2018 17:03:45
19/02/2018 17:03:45


Rating

  (1 rating)

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

Comments

Great!!

Balu Kuppusamy, February 19, 2018 - 10:20 am UTC

Thank you very much and it helped me a lot to get commit history.

But I'm getting null versions_starttime, versions_xid and versions_operation for most of rows in my DB. So is that because DBA clears version history that are older than 2 hrs?

Connor McDonald
February 20, 2018 - 1:10 am UTC

It is based on the 'undo_retention' parameter. We can't look back past that without using something like flashback data archive.

https://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS1008

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.