Find out the history of SQL queries

oracle sql-update database-administration
sumit vedi picture sumit vedi · Feb 12, 2013 · Viewed 304.8k times · Source

An update SQL query was executed on the server, which caused many problems later.

How can I get the list of update queries executed in last 2 months, so that I can trace the exact problematic SQL query?

Answer

bonsvr picture bonsvr · Feb 12, 2013 
    select v.SQL_TEXT,
           v.PARSING_SCHEMA_NAME,
           v.FIRST_LOAD_TIME,
           v.DISK_READS,
           v.ROWS_PROCESSED,
           v.ELAPSED_TIME,
           v.service
      from v$sql v
where to_date(v.FIRST_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss')>ADD_MONTHS(trunc(sysdate,'MM'),-2)

where clause is optional. You can sort the results according to FIRST_LOAD_TIME and find the records up to 2 months ago.

Related questions

Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More
Number of rows affected by an UPDATE in PL/SQL

I have a PL/SQL function (running on Oracle 10g) in which I update some rows. Is there a way to find out how many rows were affected by the UPDATE? When executing the query manually it tells me how …

Read More
Update rows in one table with data from another table based on one column in each being equal

Update many rows into one table from another table based on one column in each being equal (user_id). both tables have a user_id column. Need to insert data from t2 into t1 when the user_id column are …

Read More