I have created a database trigger (oracle 184.108.40.206 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?