Jan 5 2009, 03:22 AM
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!
Jan 5 2009, 05:36 AM
only if you have auditing turned on
Jan 5 2009, 06:44 AM
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?
Jan 5 2009, 09:02 AM
have you set up auditing?
Jan 5 2009, 09:31 AM
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?
Jan 5 2009, 09:53 AM
you need to look in aud$ then
Jan 5 2009, 10:08 AM
>> 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
Jan 8 2009, 10:14 AM
Thanks a lot for your help!!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here