Find current users DDL statement running, Oracle 11g

auditddloracleoracle-11g-r2

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

Best Answer

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.

Related Question