1

I have created a database trigger (oracle 11.2.0.4 standard edition on windows 2008 R2) to intercept any drop or truncate statements, eg

drop table abc123;

and that will raise an exception to prevent any drop statements

As part of that trigger I would like to log the statement that is being called so I can a) see who is trying to do what that they shouldn't and b) allow certain drop and truncate commands to execute

I've tried looking in v$sql but I don't think DDL statements are run this way, eg

    select /*ignore this*/ nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' username,
        ses.sid,
        ses.machine,
        replace(sqlt.sql_text,chr(10),'') as stmt,
        ltrim(to_char(floor(ses.last_call_et/3600), '09')) || ':'|| ltrim(to_char(floor(mod(ses.last_call_et, 3600)/60), '09')) || ':'|| ltrim(to_char(mod(ses.last_call_et, 60), '09')) as runtime
    from v$session ses
        join v$sql sql on (sql.address = ses.sql_address and sql.hash_value = ses.sql_hash_value and sql.sql_text not like 'select /*ignore this*/%')
        join v$sqltext_with_newlines sqlt on (sqlt.address = ses.sql_address and sqlt.hash_value = ses.sql_hash_value)
    where ses.username = user
    order by sql.hash_value, sqlt.piece

Is there another view I can get currently executing DDL statements from?

Thanks

1

You really need the statements? The drop / truncate commands don't have that many options. You can get this info from these functions in the trigger (that I think is enough for what you need):

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner

Ps: if you want to capture the statements, I would recommend you choose AUDIT feature available in the database, instead of using triggers.

  • Thanks, I want to intercept them in the trigger so in certain circumstances I want to allow the commands but in most cases I just want an exception. eg. there are a couple of tables that the application uses as temporary tables and I'd rather truncate after use than delete from Also, I don't really understand, but compiling certain packages in our application actually fires the drop trigger! I'm not sure why but I assume Oracle is doing something with types declared in the package spec. I want to be able to allow that. I'll look at those functions and see what they return – Robert K May 24 '17 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.