Man, have I dug myself into it this time!
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.
You can choose what is easyer for you?
There should be refernces and you have to find another value for your object_id, I do not remeber already it could be 10-15 char long, if I right.
I did it 1 year ago on 8.1.5
and you will find there lots of system tables.
tried it, but it didn't work...
Thanks for the thought, and it seems to be working.
I'll plough through it and see how it goes and let you know the results.
Meanwhile, I've tried doing a simple port forwarding of port 25 (SMTP) from the local machine to a mail server that is working, and that doesn't seem to fix it.
I'm glad this is on a test box and not in production, but it has definitely piqued my curiousity!
wupps... forgot to change last subject line...
Initially when I tried it, it wasn't working, but then I restarted the database and tried it again, and I was able to select/insert into tables as Internal.
As a result, I originally starting to do the "not working" reply, but then changed everything but the subject line.
So, it is working.
Thanks to Dmitri, I thought of an even easier way to fix this.
Instead of having to delete all sys references to the triggers in question, I just found the Object ID from ALL_OBJECTS, and then updated the TRIGGERS$ table to disable the trigger (ENABLED=0).
That allowed me to log in without running the trigger and then drop the offending trigger.
Everything is working fine now.
Thanks for the help.
Since this was not production and the problem is now solved, can we laugh? You really do have a unique talent, that was pretty funny no?
Click Here to Expand Forum to Full Width