Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

How to view the Oracle sql history

SOLVED
Go to solution
DnD_1
Regular Advisor

How to view the Oracle sql history

I know this command,

select * from v$sqlarea;

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

many thanks!!
5 REPLIES
Steven E. Protter
Exalted Contributor
Solution

Re: How to view the Oracle sql history

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

Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
DnD_1
Regular Advisor

Re: How to view the Oracle sql history

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
Exalted Contributor

Re: How to view the Oracle sql history

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Eric Antunes
Honored Contributor

Re: How to view the Oracle sql history

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
Each and every day is a good day to learn.
Volker Borowski
Honored Contributor

Re: How to view the Oracle sql history

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