Help - Search - Members - Calendar
Full Version: Trace sql history
Oracle DBA Forums > Oracle > Oracle Forum
lmih
Hi all,

I would like to ask if there is any way to trace in the database all the executed sql statements from the beginning where the
database has been installed up to the current point. I need information like the exact SQL statement, date and time, schema name,
user name etc... Which is the best way to keep a history of all the executed SQL scripts?

Thank you in advance!
dave
only if you have auditing turned on
lmih
I know this but when i exectute the statement below:

select * from dba_audit_trail;

The SQL_TEXT field doesn't contain the sql statement that the user has executed, it is null
any idea how to retrieve the statement?
dave
have you set up auditing?
lmih
i have execute these commands:

alter system set audit_trail=db scope=spfile;

audit select table,update table,insert table, delete table by "USER_NAME" by access;

audit execute procedure by "USER_NAME" by access;



Do i need to setup something extra?
dave
you need to look in aud$ then
burleson
>> I would like to ask if there is any way to trace in the database all the executed sql statements from the beginning where the
database has been installed

No, that would be too resource intensive, especially for select auditing.

Here is a good whitepaper that explains the options:

http://www.dba-oracle.com/t_audit_sql_select_statements.htm

If you only want to trace updates, that easier:

http://www.dba-oracle.com/art_builder_prop...or_auditing.htm
lmih
Thanks a lot for your help!! smile.gif
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.