Usn's IT Blog

Executing OS commands from SQL or PL/SQL is easy as long as you are in SQL*PLUS – just do a host command. But I must confess, that this is a really useless hint. :) Much more interesting is to execute an OS command with a DBMS_SCHEDULER job, but this is not possible in every case, as it wasn’t in mine: I had to execute an ICMP ping and wait for the result to proceed, all that within a periodic DBMS_SCHEDULER job. No way to launch another job and wait until it’s started.

It took not much time to find out, that PL/SQL provides no native method to execute something on OS level. But the web was full of more or less useful hints what to do, the most useful one I found was from Ask Tom, a question about problems calling a in-database-java method for this case. The guy there used rt.exec() out of the database – nice, but Java and me …

Read more…

I simply love 10046 level 12 traces. I described session tracing based on a logon trigger quite earlier, but some situations in real DBA life need this trace event switched on for a session other than my own AND for a short peroid in time (with no need for the connected user to log out). The package/function dbms_system.set_ev is cool for that – the syntax and the handling is very simple, and I like it much more than ORADEBUG. So it’s syntax is like:

Read more…

Oracle has a disadvantage: It allows no trigger BEFORE LOGON ON DTATBASE! :) For obvious reasons, this would be nonsense, but there’s a need for it! Auditing failed logon attempts, for example. Of course, there’s Oracle Auditing. But IMO, setting up an audit trail for one Email in one case looked like the overkill to me and my simple DB decommissioning needs (finding autistic developers).

The concept with a trigger was too nice to drop it after examining the AFTER LOGON trigger, which is useless for my problem. But the oracle-l mailing list helped AGAIN: There’s another oracle trigger: AFTER SERVERERROR ON DATABASE – catch ORA-28000, and you are perfectly auditing logon attempts on locked user accounts.

This is a code snippet for 10g and above, that sends an email on each attempt. Might be a spam bomb if abused, but as I said: My simple needs …

Read more…