I've got a big Oracle PL/SQL script I am testing and that i desired to determine if it had been easy to see what records were up-to-date/erased/placed because the last invest in the database? I want a quicker method to make sure that all of the database actions were done properly. I have the command line in addition to Oracle's custom tool
In Oracle 10g (and beginning with 9i, I believe) you can utilize Flashback Query with this.
Normally, Flashback Totally used when you really need to determine data so to speak a while ago, however in your situation the secret is the fact that Flashback Query sees only committed 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 still, you should use Flashback Version Query or Flashback Transaction Query with a few fine-tuning to filter changes produced by all periods except your present session.
In case your atmosphere enables it, you could include triggers to every single table, creating some type of audit log.
There's an audit feature in oracle, that you have the ability to use. Google thinks, this short article may be of outside assistance: http://www.securityfocus.com/infocus/1689
I would be wrong, however i don't believe that there are a good way to get this done. The only real factor that involves thoughts are checking the redo log but thres no interface for that user to determine the procedures. You are able to do it manually but it is not too simple.
You need the SCN, SYSTEM CHANGE NUMBERAnd is essentially a ticker that will get set following a commit.
You have to better define your problem - Are you currently searching to create a PLSQL script that captures just the data that's been up-to-date because the last run? Does the record set that you're searching at possess a unique id that's consecutive? Are you able to manage to choose possible replicates and validate all of them with the ultimate set to ascertain if they're indeed replicates and therefore discard them? Based on these cases, the complexness of the solution can change