Skip to Main Content
  • Questions
  • How to trap DDL Activities and get the Sql text of such statements

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Neeraj.

Asked: February 02, 2005 - 8:34 am UTC

Last updated: May 03, 2018 - 3:27 am UTC

Version: 9.2.0.4

Viewed 10K+ times! This question is

You Asked


Hello Sir,
I am quite surprised to see that Today urs ite is not blocked for me.

Sir ,I've an immediate requirement to trap all the activities fired in the Databse(DML as well As DDls). As Auditing is not supportive for this purpose. I need the log about Username,terminal,SQL Text,timestamp,OS Program.

I need immediate log for every activity.(When a command is fired ,its log is generated at once). I think PL/SQL script is needed for this,.I have tried that ,but fails to get the SQL text.

Thanks in advance,
Neeraj Bhatia (DBA)

and Tom said...

"ur" site -- I'm not familar with that site. I'm familar with this one however and since the question ended up here....



Ok this is going to be "big". As in the amount of code you will be developing. I'd ask myself "do I *really* want to do this..."

for SQL selects, you'll need to use dbms_fga:
</code> http://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_fga.htm#998101
and put a policy per table.

for SQL modifications, you'll need to code a trigger on each table and use the ora_sql_txt function to record the SQL yourself.
http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm#1005274

(search this site for some examples)


and likewise for the DDL, you'll write a system event trigger "after alter", "after create" and so on:
http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm#999369 <code>





Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

CTAS?

Jon, February 02, 2005 - 12:48 pm UTC

One of the problems I've seen is the need to see the sql running from a CTAS. Could a trigger be written to capture this as it is running? My attempts at this have only been able to show what has run after the CTAS completes, not while it is running.

CREATE OR REPLACE TRIGGER t_on_ctas before create on database

declare
n number;
sql_text ora_name_list_t;
begin
n := ora_sql_txt(sql_text);
for i in 1..n loop
insert
into ctas_audit
(sql_text,
sort_order)
values (sql_text(i),
i);
end loop;
end;
/


Tom Kyte
February 03, 2005 - 1:00 am UTC

in 10g, this won't be needed (ddl will be in the v$ views)

in 9i, you could do this:

create or replace trigger t_on_ctas
before create on database
declare
l_string long;
l_sql_text ora_name_list_t;
l_n number;
begin
l_n := ora_sql_txt(l_sql_text);
for i in 1 .. l_n
loop
l_string := l_string || l_sql_text(i);
end loop;
dbms_application_info.set_client_info( substr( l_string, 1, 64 ) );
dbms_application_info.set_module( substr( l_string, 65, 48 ),
substr( l_string, 113, 32 ) );
end;
/


to see the first 145 characters in v$session if that is enough (just select client_info||module||action from v$session)

else, you'd need to use an autonomous transaction to be able to commit those rows so others can see them.

David Aldridge, February 02, 2005 - 1:37 pm UTC

This seems like a great way of generating a vast amount of data. It's just the sort of requirement much beloved of pointy-haired bosses, who are then surprised to find you requesting enough disks to increase the db size by an order of magnitude. Or two.

seeing CTAS in v$sql!

Jon, February 03, 2005 - 9:37 am UTC

Wow, I had no idea that in 10g you can see CTAS in v$sql. This must be number 11 in top reasons to upgrade to 10g.

thanks!


Trapping Statement of Unsuccessful DDL events

Nidhi Bawa, February 08, 2005 - 4:28 am UTC

Hello Sir,
The information you provided was very useful...
I was able to get the statement of DDLs which executed successfully...But Sir, what if I want to trap the text of DDL statement which wasn't executed successfully....regardless of the reason....
2. Can I trap the reason also why the statement failed.??
Sir, I want to trap these statments immediately...I mean an immediate log is to be maintained...
Please Reply as soon as possible
Thanks in advance
Nidhi

Tom Kyte
February 08, 2005 - 6:48 am UTC

this'll get you started, you'll want to filter out things that "begin with" -- like create, alter and so on.

oh, and make errsql a clob of course.....



ops$tkyte@ORA9IR2> create table errors ( errcd number, errsql varchar2(4000) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger errors
  2  after servererror on database
  3  declare
  4      l_string long;
  5      l_sql_text ora_name_list_t;
  6      l_n number;
  7          l_errcd number := ora_server_error(1);
  8  begin
  9      l_n := ora_sql_txt(l_sql_text);
 10      for i in 1 .. l_n
 11      loop
 12          l_string := l_string || l_sql_text(i);
 13      end loop;
 14          insert into errors ( errcd, errsql ) values ( l_errcd, l_string );
 15  end;
 16  /
 
Trigger created.
 
ops$tkyte@ORA9IR2> create table ttt ( x Boo );
create table ttt ( x Boo )
                     *
ERROR at line 1:
ORA-00902: invalid datatype
 
 
ops$tkyte@ORA9IR2> select * from errors;
 
     ERRCD
----------
ERRSQL
-------------------------------------------------------------------------------
       902
create table ttt ( x Boo )
 
 

10g method

Larry Johnson, February 12, 2005 - 11:29 am UTC

I recently started 10g development and the ora_sql_txt always returns NULL in my triggers.

You mentioned "in 10g, this won't be needed (ddl will be in the v$ views)"

How would I do this? I've searched the docs and the Internet and can't find an example.

Thanks,
Larry

Tom Kyte
February 12, 2005 - 12:55 pm UTC

are you sure about that? that would be a bug:

ops$tkyte@ORA10G> create table errors ( errcd number, errsql varchar2(4000) );
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create or replace trigger errors
  2  after servererror on database
  3  declare
  4      l_string long;
  5      l_sql_text ora_name_list_t;
  6      l_n number;
  7          l_errcd number := ora_server_error(1);
  8  begin
  9      l_n := ora_sql_txt(l_sql_text);
 10      for i in 1 .. l_n
 11      loop
 12          l_string := l_string || l_sql_text(i);
 13      end loop;
 14          insert into errors ( errcd, errsql ) values ( l_errcd, l_string );
 15  end;
 16  /
 
Trigger created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create table ttt ( x Boo );
create table ttt ( x Boo )
                     *
ERROR at line 1:
ORA-00902: invalid datatype
 
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from errors;
 
     ERRCD
----------
ERRSQL
-------------------------------------------------------------------------------
       902
create table ttt ( x Boo )
 


it seems to work for me, can you give me an example where it isn't working?  I'd like to see


as for seeing the sql, i just fired up:


 
ops$tkyte@ORA10G> create table big_table
  2  as select a.* from all_objects a, all_objects b, all_objects c;


that'll take a while -- in another session, I ran my showsql script (search for it on this site) 

ops$tkyte@ORA10G> @showsql
                                                                                              
USERNAME        SID_SERIAL      STATUS     MODULE          ACTION
--------------- --------------- ---------- --------------- ---------------
CLIENT_INFO     LAST_CALL_ET
--------------- ------------
OPS$TKYTE       '152,1'         ACTIVE     SQL*Plus
                           0
                                                                                              
OPS$TKYTE       '155,3'         ACTIVE     SQL*Plus
                           6
                                                                                              
                                                                                              
2 rows selected.
                                                                                              
--------------------
OPS$TKYTE(155,3) ospid = 27344 command = 1 program =
sqlplus@localhost.localdomain (TNS V1-V3) dedicated server=27346
Saturday  12:50  Saturday  12:52 last et = 6
create table big_table
as select a.* from all_objects a, all_obj
ects b, all_objects c
ops$tkyte@ORA10G>


just joins v$session and v$sqltext_with_newlines 

9i vs. 10g ORA_SQL_TXT

Larry Johnson, February 14, 2005 - 4:23 pm UTC

This worked in 9i but always returns NULL in 10g (10.1.0.2.0).

create type ExampleType as object (
Seq NUMBER(11),
Name VARCHAR2(35),
DOB DATE
);
/

create table Example of ExampleType ;
grant select, insert, update, delete on Example to MedicsUser ;

create or replace trigger ExampleTrigger
AFTER INSERT or UPDATE or DELETE
ON Example
FOR EACH ROW

DECLARE
SQL_Text_List ora_name_list_t;
SQL_Text VARCHAR2(8000);
l_Cnt NUMBER;
l_Loop NUMBER;

BEGIN
l_Cnt := ora_sql_txt(SQL_Text_List);
for l_Loop in 1..nvl(l_Cnt, 0) loop
SQL_Text := SQL_Text || SQL_Text_List(l_Loop);
end loop;

DBMS_OUTPUT.PUT_LINE('SQL: ' || nvl(SQL_Text,'*NULL*'));
END;
/

insert into Example (Seq, Name, DOB) values (1, 'Larry', SysDate);
commit;
/

update Example set Name = 'Tom' where Seq=1;
commit;
/


Tom Kyte
February 14, 2005 - 6:09 pm UTC

ah, i was assuming system event triggers. there is an in progress new bug (just filed 3 days ago as a matter of fact -- 4171597) on this.


can you use fine grained auditing as a workaround (while you work this issue with support)? it can be used to capture select, insert, update delete -- whatever in 10g -- if you were auditing

Problem using Audit Train

Milind Dalvi, May 25, 2005 - 8:26 am UTC

Hi Tom,

We r using 10G & as per your suggesion, I tried using FGA in trigger.

but when I use it in "Instead of" trigger its not giving me the result

I have created following trigger :
create or replace trigger bi_milind_1
instead of insert or update or delete on milind1
for each row
declare
sel_str varchar(4000);
Begin
select lsqltext into sel_str
from fga_log$
where ntimestamp# =(select max(ntimestamp#)
from fga_log$
where sessionid = userenv('SESSIONID'));
sel_str := replace(upper(sel_str),'MILIND1','MILIND');
-- dbms_output.put_line(sel_str);
exe_cmd(sel_str);
End;

my SQL is not showing me that Update Statement causing this trigger but after this trigger when I select from fga_log$ its showing me the Update statement.

Please give me some solution on mutating.

I have also tried ORA_SQL_TXT but as we know its a bug in Oracle 10g its not working

Regards
Milind



Tom Kyte
May 25, 2005 - 11:41 am UTC

what does this have to do with a mutating table?

Dave, May 25, 2005 - 12:13 pm UTC

But this is the auditing thread :-)

Tom Kyte
May 25, 2005 - 3:12 pm UTC

my question was on the above -- they are talking about a mutating table constraint, but I don't see how that all can be tied together.. where does the mutating table come into play?

dave, May 25, 2005 - 6:17 pm UTC

doh, that will teach me not to read properly

apologies

FGA

milind, May 26, 2005 - 8:08 am UTC

Hi tom,

sorry for the mistake in writing please tell me solution,
about how i can get the value of above Query in Instead of trigger

cuase I am not getting that in Instead of trigger

Thanks
Milind

Tom Kyte
May 26, 2005 - 9:45 am UTC

well, the sql is captured there for you -- what are you trying to do? (recapture it all over again?)

What is the ultimate goal here (tell me what you are trying to accomplish, not so much what you have tried to do)

Details

milind, June 01, 2005 - 4:30 am UTC

Hi,

I want to have triggering SQL in the trigger for which as you said there is FGA which gives me triggering SQL.

I have problem in getting that SQL using FGA. I am trying to get that SQL in "INSTEAD OF" trigger. when I query FGA_LOG table in "INSTEAD OF" its not giving me the SQL. but when that trasaction gets over I can see that SQL in FGA table.

Give me some solution on this

Regards
Milind




Tom Kyte
June 01, 2005 - 10:02 am UTC

sorry, but i don't see it happening, you have captured the SQL, what do you need the SQL for in the instead of trigger? I mean, you have the audit trail -- what would you be doing with the text of the SQL in "real time" (besides auditing it)

SQL Explain Plan

Ernie, March 02, 2007 - 8:30 am UTC

We currently have a trigger that is capturing the currently running SQL statement when something bad (system errors) occurred. What we would like to do is enhance it by finding the execution plan and also displaying that as well. We currently send an email of the statement and also who / what was running the statement.

We could always "after the fact" run the SQL in the email and find the execution plan, however we want to see it at time of execution in case it had changed after the fact (new indexes addeded etc...)

Is there an easy way to accomplish this?

Thanks for the input on this. I do appreciate it.
Tom Kyte
March 04, 2007 - 6:03 pm UTC

v$sql_plan would have it.

Thank you for the response.

Ernie, March 06, 2007 - 2:31 pm UTC

I added v$sql_plan to get the explain plan. I am finding however that the HASH_VALUE is needed based on the failing SQL.
In order to get the HASH_VALUE, I query v$sqltext based on the first several characters of the SQL executing at time of failure.

I noticed in testing that if the SQL was exact, except for the where condition that the query to get the hash value would return more than one record. So, I only pull one back, but this may not "actually" be the one that I may have run to get the server error.

Any chance the executed SQL "hash value" is kept around somewhere (i.e. like ora_sql_txt, or ora_database_name or ora_server_error)?

Thanks for the assistance.

Tom Kyte
March 06, 2007 - 4:10 pm UTC

v$open_cursor for that session might be helpful

Explain Plan from System Trigger

Ernie, March 07, 2007 - 10:55 am UTC

Tom,

You are a genius....

Thanks,

Ernie

ora_sql_txt returns null

Hussain, April 14, 2018 - 7:08 am UTC

Hi Tom,

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


create table errors ( errcd number, errsql varchar2(4000) );

CREATE OR REPLACE TRIGGER errors1
AFTER servererror ON DATABASE
DECLARE
l_string LONG;
l_sql_text ora_name_list_t;
l_n NUMBER;
l_errcd NUMBER := ora_server_error(1);
BEGIN
l_n := ora_sql_txt(l_sql_text);
FOR i IN 1 .. l_n LOOP
l_string := l_string || l_sql_text(i);
END LOOP;

INSERT INTO errors (errcd, errsql) VALUES (l_errcd, l_string);
END;


executing
create table ttt ( x Boo );
gives following errors

ORA-00604: error occurred at recursive SQL levl 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8
ORA-00902: invalid datatype

When I do debug the cause it turns out that ORA_SQL_TXT is null

not sure if anything to ORA_SQL_TXT changed in 11g

or if there is any other alternative to capture failed sql statement

Thanks in advance


Connor McDonald
April 16, 2018 - 2:11 am UTC

Looks to me like that could be a bug in 11.2. It captures DML errors but not DDL from the look of it.

SQL> set serverout on
SQL> CREATE OR REPLACE TRIGGER errors1
  2  AFTER servererror ON DATABASE
  3  DECLARE
  4  l_string varchar2(1000);
  5  l_sql_text ora_name_list_t;
  6  l_n NUMBER;
  7  l_errcd varchar2(1000) := ora_server_error(1);
  8  BEGIN
  9  l_n := ora_sql_txt(l_sql_text);
 10  dbms_output.put_line('l_n='||l_n);
 11  --FOR i IN 1 .. l_n LOOP
 12  --l_string := l_string || l_sql_text(i);
 13  --END LOOP;
 14
 15  --INSERT INTO errors (errcd, errsql) VALUES (l_errcd, l_string);
 16  END;
 17  /

Trigger created.

SQL>
SQL> set serverout on
SQL> create table ttt ( x Boo );
l_n=
create table ttt ( x Boo )
                     *
ERROR at line 1:
ORA-00902: invalid datatype


SQL> select 1/0 from dual;
select 1/0 from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero


l_n=1
SQL> drop table qwe;
l_n=
drop table qwe
           *
ERROR at line 1:
ORA-00942: table or view does not exist



whereas in 12.2, operation is back to normal

SQL> CREATE OR REPLACE TRIGGER errors1
  2  AFTER servererror ON DATABASE
  3  DECLARE
  4  l_string varchar2(1000);
  5  l_sql_text ora_name_list_t;
  6  l_n NUMBER;
  7  l_errcd varchar2(1000) := ora_server_error(1);
  8  BEGIN
  9  l_n := ora_sql_txt(l_sql_text);
 10  dbms_output.put_line('l_n='||l_n);
 11  --FOR i IN 1 .. l_n LOOP
 12  --l_string := l_string || l_sql_text(i);
 13  --END LOOP;
 14
 15  --INSERT INTO errors (errcd, errsql) VALUES (l_errcd, l_string);
 16  END;
 17  /

Trigger created.

SQL>
SQL> set serverout on
SQL> create table ttt ( x Boo );
l_n=1
create table ttt ( x Boo )
                     *
ERROR at line 1:
ORA-00902: invalid datatype


SQL> select 1/0 from dual;
select 1/0 from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero


l_n=1
SQL> drop table qwe;
l_n=1
drop table qwe
           *
ERROR at line 1:
ORA-00942: table or view does not exist


I'd contact Support about a backport of a patch (or look at upgrading)


Thank you!

Hussain, May 02, 2018 - 3:58 am UTC

There are plans to upgrade to 12c,it just that timelines are bit fluid at the moment.

In the meantime it would be great if I get some information on availability of patch for 11.2

Thanks in advance


Connor McDonald
May 03, 2018 - 3:27 am UTC

You'll need to contact Support.

Thank you!

A reader, May 03, 2018 - 4:17 am UTC


More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.