2

Let's say there have a web framework with using ORM approach to handle db transactions (like hibernate)

And my question is there any SQL which can find out all transactions that have already executed/committed in oracle db?

Is it possible? Thanks.

loalexzzzz
  • 463
  • 1
  • 7
  • 16
  • Query the table. That's the best way to check if the transaction is comepleted or not. – XING Aug 25 '17 at 09:54
  • The short answer is"no" but it rather depends on what you're trying to achieve. Normally transactions are managed at the top of the call stack – APC Aug 25 '17 at 10:49

2 Answers2

1

I don't believe that the Oracle database automatically exposes data about completed transactions. There are various tables that can be queried for in-progress transactions but, once the transaction is complete, the database really doesn't care about it any more.

What you can do is create a trigger that takes effect on a database update. The procedure invoked by the trigger can log to a new database table some outline of the operation that just completed. You can then query that table using ordinary SQL operations. Of course, you will need to purge or trim the log table from time to time.

There is an example of creating an update trigger here:

log insertions/updates/deletions in oracle database

Kevin Boone
  • 4,092
  • 1
  • 11
  • 15
1

You can use Redo Logminer to obtain a list of commit and info what was made in transactions.

List of red_log files 

 SELECT distinct member LOGFILENAME FROM V$LOGFILE where type = 'ONLINE';

Add these files to log_miner. And start log_miner.

declare 
begin  
 for rec in (select distinct member from V$LOGFILE where type = 'ONLINE' ) loop
  DBMS_LOGMNR.ADD_LOGFILE(rec.member); 
 end loop; 
  DBMS_LOGMNR.START_LOGMNR (options => dbms_logmnr.dict_from_online_catalog);
end;

Now you can start using view v$logmnr_contents

 select operation,sql_redo, sql_undo,table_name, x.* from  v$logmnr_contents x where username = 'your jdbc user';

At the end. Turn off log miner; execute DBMS_LOGMNR.END_LOGMNR();

Arkadiusz Łukasiewicz
  • 6,241
  • 1
  • 11
  • 17