Manipulating Oracle Files with UTL_FILE

Take hold of your alert log with the use of a few new subprograms in the UTL_FILE package.

Maintenance of the Oracle alert log file has and continually will be a pain to work with until the day that Oracle realizes they need to hook in a few more operating system utilities to manage their own trashcans. About this time last year I wrote an article called ‘That Darn Alert Log‘ where I gave you a simple utility that would allow you to read the alert log from within the Oracle database. This is a great little utility if you have found it a headache to logon to every Oracle database just to view the alert log. While I have changed the code slightly for these new procedures, go back and read this article on how to setup and use the utility. Through this article, I will present the new procedures I have created to take advantage of the new UTL_FILE procedures and present the full code at the end of the article. If you used that utility, and hopefully modified it to warn you of errors, you soon found out that you still needed to log into those database boxes to clean-up the alert log of old information. This article will expand upon the utility and give you a few more options to handle your alert log and actually clean-up the alert log without ever logging into those boxes.

What is the UTL_FILE Package

The UTL_FILE package is simply a set of PL/SQL procedures that allow you to read, write and manipulate operating system files. In past releases of this package, you were limited to only reading and writing to system files and obtaining access to those files was much more difficult since the database needed to be bounced every time a new set of files were to be accessed from a different directory structure on the system. With the new changes to the UTL_FILE package, the access to different directories has been simplified by the use of the CREATE DIRECTORY command.

CREATE DIRECTORY

As noted above, it use to be that granting access to a set of files on the operating system, a DBA would have to make an entry in the init.ora file for the parameter UTL_FILE_DIR to let the UTL_FILE package recognize a directory. This has been greatly simplified by the use of the CREATE DIRECTORY command. By issuing a CREATE DIRECTORY and granting access to the directory, users will have immediate access to system files within that directory. As you can see, the CREATE DIRECTORY command can give you much more control through its dynamic nature than the old UTL_FILE_DIR parameter. Here is what the two commands look like.

SQL> CREATE DIRECTORY BDUMP_DIR 
AS '/u01/app/oracle/admin/saigon/bdump';
SQL> GRANT READ ON DIRECTORY BDUMP_DIR TO PUBLIC;

Here is the new procedure that takes advantage of creating the directory where the alert log resides. It first selects from V$PARAMETER where the background dump destination is defined and then creates the directory to it.

PROCEDURE bdump_dir_setup IS
 BEGIN
  EXECUTE IMMEDIATE 
  'SELECT value '||
  '  FROM v$parameter '||
  ' WHERE name = ''background_dump_dest''' 
     INTO bdump_dir;
  EXECUTE IMMEDIATE 
  'CREATE DIRECTORY BDUMP_DIR '||
  '    AS '''||bdump_dir||'''';
END bdump_dir_setup;

UTL_FILE.FRENAME

This is a little gem of a procedure that will rename one file to another. In addition to renaming a file, this procedure also has the ability to move the file to a different area on disk. In the following procedure, I only rename the file to a date stamp with the alert log name in the same BDUMP_DIR. If you are currently using operating system utilities to move alert logs to different directories, this procedure can quickly be changed to move the alert log to a different directory. Moving to a different directory would require the use of the CREATE DIRECTORY command to set up the alternate directory you would be moving the alert logs to. Please also note that after this command executes, there will no longer be an alert log on disk. Not to fear, the next time Oracle needs to write to the alert log a new one will be created. If you are serious about archiving your alert logs, this procedure should be run every day, week or month depending on how much information you want in each of your alert logs. Be aware that the first time you run this command, the first archived alert log might be very large since it will contain all of the lines in your current alert log since the last time you actually cleaned it out. Also, be aware that the name of the file is a date stamp of the current day plus the normal default alert log name. Access to this archived alert log in future code will be from a date offset that is determined from the current day.

PROCEDURE archive IS
BEGIN
  build_alertlog_name;
  check_alertlog_name;
  UTL_FILE.FRENAME ('BDUMP_DIR',
                    alertfile,
                    'BDUMP_DIR',
                    TO_CHAR(SYSDATE,'YYYYMMDD')||
		    '_'||alertfile);
END archive;