?
Solved

last updated or inserted query

Posted on 2011-10-20
10
Medium Priority
?
284 Views
Last Modified: 2012-05-12
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.
 
0
Comment
Question by:dojjol
  • 6
  • 4
10 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37001231
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37001248
if you have triggers (or app code) that populates a "last_modified" (or similar) column in your tables that might work too.

0
 

Author Comment

by:dojjol
ID: 37001252
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.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 74

Expert Comment

by:sdstuber
ID: 37001322
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

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37001328
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
0
 

Author Comment

by:dojjol
ID: 37001380
thanks sdstuber,
is there a way that we can check the sequence of triggers executed.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37001419
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.
0
 

Author Comment

by:dojjol
ID: 37002270
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;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37002376
select * from v$sql_bind_data
0
 

Author Closing Comment

by:dojjol
ID: 37002478
thanks I used v$sql_bind_capture.

Thanks sdstuber
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question