Re: Alert Log File (UTL_FILE)

Hi

aren't you having fun with alert logs! I have a script that I have been prompted to work on some more by this whole discussion. (Of course Connor's book arrives this week and will show me all the stuff I haven't thought about no doubt.) The script creates the necessary directory object and table for you, but does require 9.2 and running as a user with at least the CREATE ANY DIRECTORY privilege and also SELECT on v$parameter to work without errors.

You may have missed that I stated that the procedure required 9.2 or higher. this is because the ability to rename files only came into the UTL_FILE package at this release. In other words if you run the code that I originally posted then the compiler will indeed barf on UTL_FILE.FRENAME because that procedure doesn't exist. The same applies to the code below. Other changes are

1. I fixed the stupid omission of a COMMIT; statement.
2. I throw a different error instead of UTL_FILE.INVALID_OPERATION - this just seems a useless error as all it tells you is that something you did to the file didn't work for some reason.
3. You can now switch alert logs as frequently as once a second! If you find yourself needing to do this it may be time to look for a life.
4. I actually have some error handling..

As far as appending to the table goes, this procedure works by dumping the data into an table and then renaming the alert log file. This means that on subsequent runs we can load the whole file (since history has gone to another file).

On prior versions (where FRENAME isn't available) then one would probably need to do quite a bit of work to ensure that you only load new data, one might do this by counting the number of lines in the table and then reading the alert log from that position+1 for example, but I suspect that this would be prone to error.

It is worth noting that all this work reproduces something that OEM can do better out of the box. :(

script at
http://www.niall.litchfield.dial.pipex.com/scripts/admin/read_alertlog.sql

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
"MATHEW KING" <bzbzzb_at_hotmail.com> wrote in message
news:528c8daf.0402170433.e2e1ded_at_posting.google.com...


> Dear Niall,

>

> Thanks for your  procedue which uses the feature of Utl_file.

> I think it is another good way to read the Alert file.

> But I like to say there is some thing missing in the code, that's why

> the procedure can't compile.

>

> With regards to the second last line of the code that is the

> following:

> UTL_FILE.FRENAME(p_location,p_filename,p_location,v_newname);

>

> Could you please send the complete code?

>

> Could you also plz discuss more about how to append the text of alert

> log file into the table alert_log when we want to refresh the contents

> of alert log?

>

> Regards

>

> Mathew.