Oracle: How to find out if there is a transaction pending?

by

you can check if your session has a row in V$TRANSACTION (obviously that requires read privilege on this view):

SQL> SELECT COUNT(*)
       FROM v$transaction t, v$session s, v$mystat m
      WHERE t.ses_addr = s.saddr
        AND s.sid = m.sid
        AND ROWNUM = 1;

  COUNT(*)
----------
         0

SQL> insert into a values (1);

1 row inserted

SQL> SELECT COUNT(*)
       FROM v$transaction t, v$session s, v$mystat m
      WHERE t.ses_addr = s.saddr
        AND s.sid = m.sid
        AND ROWNUM = 1;

  COUNT(*)
----------
         1

SQL> commit;

Commit complete

SQL> SELECT COUNT(*)
       FROM v$transaction t, v$session s, v$mystat m
      WHERE t.ses_addr = s.saddr
        AND s.sid = m.sid
        AND ROWNUM = 1;

  COUNT(*)
----------
         0

More Related Contents:

  1. Difference between LockModeType Jpa
  2. Calculate difference between 2 date / times in Oracle SQL
  3. Can you SELECT everything, but 1 or 2 fields, without writer’s cramp?
  4. How to generate entire DDL of an Oracle schema (scriptable)?
  5. ORA-28040: No matching authentication protocol exception
  6. How to handle Day Light Saving in Oracle database
  7. How to get records randomly from the oracle database?
  8. Oracle PL/SQL – Raise User-Defined Exception With Custom SQLERRM
  9. Auto Increment for Oracle
  10. Get resultset from oracle stored procedure
  11. Deploying and Configuring ODP.NET to work without installation with Entity Framework
  12. creating parameterized views in oracle11g
  13. Number of rows affected by an UPDATE in PL/SQL
  14. How to use a table type in a SELECT FROM statement?
  15. Spool Command: Do not output SQL statement to file
  16. URL string format for connecting to Oracle database with JDBC
  17. Why are Oracle table/column/index names limited to 30 characters?
  18. What is the difference between precision and scale?
  19. Is there any way to have the JBoss connection pool reconnect to Oracle when connections go bad?
  20. Get ID of last inserted record in oracle db
  21. oracle SQL plus how to end command in SQL file?
  22. Why is the GETDATE() an invalid identifier
  23. Get the number of days between two dates in Oracle, inclusive of the dates
  24. ORA-04091: table [blah] is mutating, trigger/function may not see it
  25. Cursor For Loop with dynamic SQL-Statement
  26. How to use parameters in a ‘where value in…’ clause?
  27. Why cannot I create triggers on objects owned by SYS?
  28. automating rollback script oracle
  29. How do I get column datatype in Oracle with PL-SQL with low privileges?
  30. How to View Oracle Stored Procedure using SQLPlus?

Leave a Comment