Home » SQL & PL/SQL » SQL & PL/SQL » Capturing SQL statements to a log table. (10.2)
Capturing SQL statements to a log table. [message #331201] Wed, 02 July 2008 10:06 Go to next message
bpeasey
Messages: 46
Registered: March 2005
Member
Hi,

I'm looking for a method to capture the SQL (with Bind variables) for a specific series of actions taken by an application. The goal is use the SQL statements to create scripts for a weekly application build.

I tried the SQL Trace and DBMS_MONITOR.DATABASE_TRACE_ENABLE/DISABLE. It was helpful in indentifying the tables involved, but only showed one statement type per table. I'm certain there where dozens of actual inserts/updates.

I was hoping to use a trigger 'on schema' to capture the sql text but database level triggers don't allow on update, delete, insert attributes.

I could capture the before and after versions of the tables involved then compare the contents, but I'm hoping for a method to capture the actual SQL statements.

Any suggestions greatly appreciated.

Best Regards,
Brian
Re: Capturing SQL statements to a log table. [message #331204 is a reply to message #331201] Wed, 02 July 2008 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Pete Finnigan, How to set trace for others sessions, for your own session and at instance level

Regards
Michel

Re: Capturing SQL statements to a log table. [message #331254 is a reply to message #331204] Wed, 02 July 2008 15:16 Go to previous message
bpeasey
Messages: 46
Registered: March 2005
Member
Hi Michel,

Thanks for the link.

I eventually went with FGA..

--enable auditing
alter system set audit_trail = db SCOPE=SPFILE;

--bounce db

--login as sysdba

--create audit policy
BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'XYZ',
    object_name     => 'RATE',
    policy_name     => 'RATE_AUDIT',
    audit_condition => NULL, 
    audit_column    => NULL,
    statement_types => 'INSERT,UPDATE,DELETE');
END;
/

--run app to act on tables

--view dml statements in view
SELECT sql_text FROM dba_fga_audit_trail;

--drop the policy
BEGIN
  DBMS_FGA.drop_policy(
    object_schema   => 'XYZ',
    object_name     => 'RATE',
    policy_name     => 'RATE_AUDIT');
END;
/
Previous Topic: help with query
Next Topic: Date compare
Goto Forum:
  


Current Time: Sat Dec 10 16:47:12 CST 2016

Total time taken to generate the page: 0.12153 seconds