Flash back query - How to find when a column was updated?

Oracle 10g offers powerful mechanism of retrieving historic data via flash back queries. Simply put, we can find the state a table was in at a particular point in time. For example if you want to find the state of the table on September 11 2009, we can write a query like this.

SELECT *
FROM FND_USER
AS OF TIMESTAMP (TO_TIMESTAMP ('11-SEP-2009 08:46:40','DD-MON-YYYY HH24:MI:SS'));

This will give the output as though the query was run on '11-SEP-2009 08:46:40' provided flash back query is enabled in the system and data is available for the date. Usually in the system this data is available for a few days depending on DBA setups.

Uses

1. This feature is much useful to analyse synchronization issues. Frequently we encounter situations like a report showing different output for the same input when executed at different points in time. Some issue occurred at some point in time but not replicable now. Using flash back query we can see and analyze the data in the table when the report was run and arrive at the conclusion.
   



2. In a system with out proper auditing functionalities, it will be useful to find the time at which data was changed so as to find which program or user manipulated data incorrectly.

Here is a PL/SQL script which can be used to find when particular data was modified.

DECLARE
v_sql VARCHAR2 (2000);
v_table_name VARCHAR2 (30) := '&table_name';
v_where_clause VARCHAR2 (2000) := '&where';
v_count NUMBER;
v_start BOOLEAN := TRUE;
last_scn SYS.smon_scn_time.time_dp%TYPE;
BEGIN
FOR rec_scn IN (SELECT SCN,
time_dp
FROM SYS.smon_scn_time
ORDER BY SCN DESC)
LOOP
v_sql :=
'select count(rowid) from '
|| v_table_name
|| ' as of scn('
|| rec_scn.SCN
|| ') where '
|| v_where_clause;

EXECUTE IMMEDIATE v_sql
INTO v_count;
IF (v_count = 0)
THEN
IF (v_start = TRUE)
THEN
DBMS_OUTPUT.put_line ('The record does not exists in the system');
ELSE
DBMS_OUTPUT.put_line ( 'The record was created between '
|| TO_CHAR (rec_scn.time_dp,
'DD-MON-YYYY HH24:MI:SS')
|| ' and '
|| TO_CHAR (last_scn,
'DD-MON-YYYY HH24:MI:SS'));
END IF;

EXIT;
END IF;

last_scn := rec_scn.time_dp;
v_start := FALSE;
END LOOP;
IF (v_count <> 0)
THEN
DBMS_OUTPUT.put_line ( 'The record was created before '
|| TO_CHAR (last_scn, 'DD-MON-YYYY HH24:MI:SS'));
END IF;
END;

For example if you want to find when user with a particular id was created, you can use this script. First write a query to fetch the particular record only


SELECT *
FROM fnd_user
WHERE user_id = 10964;


Run the script filling in the two parameters.

Table_Name :- 'FND_USER'
Where_clause :- 'user_id = 10964'


The script will output the approximate time in which the record was created as given below.

"The record was created between 11-SEP-2009 12:46:06 and 11-SEP-2009 12:51:37"

You can compare the time in CREATION_DATE column with the script output and see if it matches. Not all the tables will have audit columns and the script will be more useful in such conditions.

If the record was created long back and flash back data is not available, then message will be displayed like

"The record was created before 07-SEP-2009 01:15:39"


If we want to find when the EMAIL_ADDRESS of a user was modified, we can run the script as given below. First write the query to fetch exact record.


SELECT *
FROM fnd_user
WHERE user_id = 7 and email_adress='wizard@oz.org'


This query should return one and only one row.


Now run the script with the following parameters

Table_Name :- 'FND_USER'
Where_clause :- 'user_id = 7 and email_adress=''wizard@oz.org''


(Notice that for quotes an additional quote should be used as escape charecter.)

The output will be

'The record was created between 08-SEP-2009 08:15:22 and 08-SEP-2009 08:15:22'

Limitations

1. Flash back data is available only for a limited time period based on DBA settings.



2. Accurate time of modification can't be found. Usually we will get a time period of 5 minutes as output.

The script will be useful to analyse non-reproducible data issues provided flash back query is enabled and historical data is available for the time.