I have a database trigger that sends off an email every time someone logs into the database, but unfortunately the email routine is not working due to the fact that there is no SMTP server set up on LOCALHOST, which is the SMTP Host value.
Since this email routine is not working, it will not finish the "BEFORE" trigger when logging in, therefore nobody can log in. THis means that I cannot log in to either fix or drop the trigger.
There is also a similar trigger that reports on any ALTER, DROP, CREATE, or REPLACE commands, including "ALTER DATABASE", "ALTER TRIGGER", etc., meaning that I can't connect as internal and just alter or drop the trigger.
Oh joy, oh bliss.
Does anybody have any thoughts on how to fix this solution?
I've been hoping that the util_smtp package would time out, but no such luck. I REALLY don't want to have to install a mail server on the box, but if that's my only option, then so be it.
(BTW, this is working great in production to inform me of any illegal access or actions being taken, but in Production we have a SMTP server on localhost.)
Hopefully I've explained this problem well enough, but if not, feel free to ask for more information.
Thanks in advance for any help anyone can provide.
As I undersood you are able to connect as internal.
So you can use DELETE command. I had the similar problem with corrupted object - I could not drop it and use it at all.
So are you ready to play with systems tables? If yes: shutdown your DB and make a full backup.
Everything very easy: you have to delete all informatiuon from DB about your Trigger based on object_id.
select object_id from dba_objects where object_name = 'YOUR_TRUGGER'
select column_name,table_name from dba_tab_columns where column_name = 'OBJECT_ID';
For example sys.ALL_PROBE_OBJECTS table contains the object_id ...
After that you have to check all tables which have that object_id and it could be another field name in different tables, probably you have to review views to extract table name ... and so on. At the and you will get around 20-30 records which have to be deleted from DB, after that, if everything was correct you will not see your TRIGGER any more in the database.