v$sqlarea does not give latest SQL statements!

[qazml...@rediffmail.com]

How exactly we can find the last 'N' number of SQL statements that were
executed in the Oracle 9i(9.2.0.4.0)?

I tried to do the following, but it did not help:
$select SQL_TEXT,FIRST_LOAD_TIME from v$sqlarea order by
FIRST_LOAD_TIME;

I know that the application which is using the Oracle DB does some
updates in the Database. But, the above query did not show these SQL
statements at all. Wherelse I can check this?

[John K. Hinsdale]

Hmmm the SQL for recent updates really should be in the V$SQLAREA.
Did you really scan every single query?

Try filtering the text: for UPDATEs only, for the user of interest,
and also ordering by most RECENT first load:

SELECT SA.sql_text, SA.first_load_time
FROM v$sqlarea SA, all_users AU
WHERE UPPER(SA.sql_text ) LIKE '%UPDATE%'
AND SA.parsing_schema_id = AU.user_id
AND AU.username = 'MYUSER'
ORDER BY SA.first_load_time DESC;

This ought to weed out a lot of queries. If you get no rows,
remove the filter on UPPER(sql_text), then the filter on your
schema of interest.

I believe there are new columns LAST_LOAD_TIME and LAST_ACTIVE_TIME
that will give even better view of what has actually been executed (as
opposed to first parsed), but these are available only in versions
beyond your 9.2.0.4.0

--JH