History of SQL statements for a current user

In OEM 2.1 diagnostics pack, I recall in TOPSESSIONS you could see a list of a users previous SQL statements and do explain plans on them. In the 9i version all I seem to be able to see is the current statement.

Is it possible in either TOPSESSIONS or basic SQL to see such a history.

I only care about connected sessions, and I would like to see an order history of their statements not even going too far back, a few minutes at most.

regards, John.

Since you are using 9i and OEM you can use LogMiner Viewer to get this information.

Configure LogMiner with either redo logs or online data dictionary support, and use the GUI to get this info.

Hope that helps,

clio_usa
OCP DBA 8/8i/9i
visit our web site: Here

v$sql

logminer is to see DML/DDL not queries

V$sql keep only sql statments without information about session ID

Probably u can see only current and previous sql statment only one) for session id
if u have to use v$sql + v$session something like:

select vs.sid,
s1.sql_text current_stmt,
s2.sql_text previous_stmt
from v$session vs,
v$sql s1,
v$sql s2
where vs.SQL_ADDRESS = s1.ADDRESS and
vs.PREV_SQL_ADDR = s2.ADDRESS
and
s1.ADDRESS <> s2.ADDRESS union
select vs.sid,
s1.sql_text current_stmt,
'*' previous_stmt
from v$session vs,
v$sql s1
where vs.SQL_ADDRESS = s1.ADDRESS and
vs.PREV_SQL_ADDR = vs.SQL_ADDRESS
/