Link to home
Start Free TrialLog in
Avatar of dojjol
dojjolFlag for Afghanistan

asked on

last updated or inserted query

Hello All,

Is there a way I can find out the last update or inserted statement fired on database.

I tried select * from v$sql, it only gave me select statements.

Thanks.
 
Avatar of Sean Stuber
Sean Stuber

if you have auditing turned on for updates/inserts,  yes   (dba_audit_trail,  dba_fga_audit_trail)

otherwise you'll have to approximate with flashback queries,  or logminer
if you have triggers (or app code) that populates a "last_modified" (or similar) column in your tables that might work too.

Avatar of dojjol

ASKER

Thanks sdstuber.

How can I turn on auditing.

I am sorry It just that I am new to oracle world.

If possible can you also give pointers about logminer or flashback queries.
to audit inserts/updates on any table...

audit insert table, update table;

Open in new window



for flashback  you can select from table's "as of" a particular point in time  - here I'm querying one minute ago
and then compare those results to current table to see what's different, if anything.


select * from my_table as of timestamp (systimestamp - interval '1' minute)

Open in new window


note, this won't tell you want the insert/update was, only that that something changed

log miner is a bit much to go into for a Q&A forum

but if you're interested...

http://download.oracle.com/docs/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL019
Avatar of dojjol

ASKER

thanks sdstuber,
is there a way that we can check the sequence of triggers executed.
not that I know of.  triggers are non-transactional.  so unless you can capture a sql statement that invokes them I don't know of a way to track them directly.
Avatar of dojjol

ASKER

Hey sdstuber,
I did print statement, to work it this time, also found out oracle 11g supports the trigger sequence, so by that time will wait to move to oracle 11.

By any chance could you let me know get the parameter values for sql statements we see on running select * from v$sql;
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dojjol

ASKER

thanks I used v$sql_bind_capture.

Thanks sdstuber