How to see all records changed since last commit in Oracle 10?

Question

I have a rather large Oracle PL/SQL script I'm testing and I wanted to know if it was possible to see what records were updated/deleted/inserted since the last commit to the database? I need a faster way to check that all the database actions were done correctly. I have access to the command line as well as Oracle's custom tool SQL Developer.

Answer 1

I might be wrong, but I don't think that there's an easy way to do this. The only thing that comes to mind is checking the redo log but thres no interface for the user to check the operations. You can do it manually but it's not that simple.

Answer 2

You need to get the SCN, SYSTEM CHANGE NUMBER. It is basically a ticker that gets set after a commit.

always ASK_TOM

Answer 3

If your environment allows it, you could add triggers to each and every table, creating some kind of audit log.

There is an audit feature in oracle, which you might be able to use. Google thinks, this article might be of some help: http://www.securityfocus.com/infocus/1689

Answer 4

You need to better define your issue - Are you looking to write a PLSQL script that captures only the data that has been updated since the last run? Does the record set that you are looking at have a unique id that is sequential? Can you afford to select possible duplicates and validate them with the final set to see if they are indeed duplicates and thus discard them? Depending on these cases, the complexity of your solution will change

Answer 5

In Oracle 10g (and starting with 9i, I think) you may use Flashback Query for this.

Normally, Flashback Query is used when you need to see data as it were some time ago, but in your case the trick is that Flashback Query sees only committed data.

So, here's a quick example:

SQL> create table t1 as select level lev from dual connect by level < 100;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
        99

SQL> select count(*) from t1 as of timestamp systimestamp;

  COUNT(*)
----------
        99

SQL> update t1 set lev = -lev;

99 rows updated.

SQL> select max(lev) from t1 as of timestamp systimestamp;

  MAX(LEV)
----------
        99

SQL> select max(lev) from t1;

  MAX(LEV)
----------
        -1

SQL> commit;

Commit complete.

SQL> select max(lev) from t1 as of timestamp systimestamp;

  MAX(LEV)
----------
        -1

SQL>

UPD: even better, you can use Flashback Version Query or Flashback Transaction Query with some tweaking to filter changes made by all sessions except your current session.