How to view all records since the last commit in Oracle 10?

Question

I have a fairly large Oracle PL / SQL script I'm testing, and I wanted to know if it is possible to see which records have been updated / deleted / inserted since the last commit to the database? I need a faster way to verify that all database operations are correct. I have access to the command line as well as the user tool Oracle SQL Developer.

Answer 1

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

Usually, Flashback Query is used when you need to see data some time ago, but in your case, the trick is that Flashback Query only sees captured data.

So here is 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: Better yet, you can use the Flashback or Flashback Transaction Query version query with some tweaks to filter changes made by all sessions except the current session.

Answer 2

If your environment allows this, you can add triggers for each table by creating some kind of audit log.

, , , . Google , : http://www.securityfocus.com/infocus/1689

Answer 3

, , , . , , , . , .

Answer 4

SCN, SYSTEM CHANGE NUMBER. , .

ASK_TOM

Answer 5

You need to better define your problem - do you want to write a PLSQL script that displays only the data that has been updated since the last launch? Does the recordset you are looking for have a unique identifier that is sequential? Can you allow yourself to select possible duplicates and check them with the final set to make sure that they are really duplicated and therefore discard them? Depending on these cases, the complexity of your decision will change.