How to view the Oracle sql history

[DnD_1]

I know this command,

select * from v$sqlarea;

but how about to see the history command run filtered by user ?

many thanks!!

[Steven E. Protter · Accepted solution]

Shalom,

Its been a while

desc v$sqlarea

If there is a user field.


select * from v$sqlarea where user = 'username';

Example:

SELECT *
FROM suppliers
WHERE city = 'Newark';

http://www.techonthenet.com/sql/select.php

SEP

[DnD_1]

thanks !!

does it something like show history for UNIX ?

some sort of sabotage happen lately, and i want to see what certain user has been doing with the DB...

[Steven E. Protter]

My understanding is Oracle keeps a record of all sql statements.

You need to merely construct the proper sql statement to call up the data and find our whom is doing what.

Oracle collects this data to conduct self tuning based on actual database use.

SEP

[Eric Antunes]

Hi Dnd,

In v$sql you just get the sql history since the last startup.

In the futures, to get more history (before the last startup) you must activate the audit functionality of your database (check your Database Administratorâ s Guide).

Best Regards,

Eric Antunes

[Volker Borowski]

Well

V$SQL contains what the database need to
keep track for current operation, not
for archiving yesterdays executed statements.

If your shared pool is small, statements might be flushed from that area, so if it
is not in there, it does NOT mean, it has
not been executed! (clear ? :-)

If you need relyable information, you'll need
to dig into auditing or use logminer.

Volker