Manipulating Oracle Files with UTL_FILE - Page 2

UTL_FILE.FGETATTR

One of the key links to doing file manipulation is the use of the UTL_FILE.FGETATTR procedure. This procedure gets a couple of file attributes and checks for the file's existence on disk. The following procedure makes use of this procedure by accepting a date offset and checking on disk for archived alert logs that follow a pattern of date stamp and default alert log name. This procedure will list the files using the DBMS_OUTPUT.PUT_LINE procedure for files that it has found and print out the associated date offset to the individual files. Since the reading of the alert log and archived alert logs requires you to supply a date offset, you should take special note of this value to display the proper alert log. Before issuing this command, you must use the SET SERVEROUTPUT ON command so that DBMS_OUTPUT.PUT_LINE can send the information to the screen.

PROCEDURE list (in_date_offset IN NUMBER) IS
BEGIN
  build_alertlog_name;
  check_alertlog_name;
  FOR date_offset IN 0..in_date_offset LOOP
    IF date_offset = 0 THEN
      date_file := alertfile;
      UTL_FILE.FGETATTR('BDUMP_DIR',
date_file,
vexists,
vfile_length,
vblocksize);
      IF vexists THEN
        dbms_output.put_line('OffSet : '||
date_offset||
', '||
date_file||
'    '||
vfile_length);
      END IF;
    END IF;
    date_file := TO_CHAR(SYSDATE-date_offset,'YYYYMMDD')||
'_'||alertfile;
    UTL_FILE.FGETATTR('BDUMP_DIR',
date_file,
vexists,
vfile_length,
vblocksize);
    IF vexists THEN
        dbms_output.put_line('OffSet : '||
date_offset||
', '||
date_file||
'    '||
vfile_length);
    END IF;
  END LOOP;
END list;

UTL_FILE.FREMOVE

This new procedure does exactly what it says; it will remove a file from the operating system. You must have privileges on the operating system to do this but since we are manipulating files that are owned by the user oracle and the UTL_FILE procedure will run as user oracle, there are no problems here with operating system privileges. The following procedure makes use of the input variable IN_DATE_OFFSET that you should supply to tell the procedure how far back in time to go and remove all alert log archives up to but not including the current archived log. Before actually using the UTL_FILE.FREMOVE procedure the code will build viable alert log names and then check to see if the derived archive log name is a good one. If the default alert log name is good it will then cycle through the date offset, check to see if that archived alert log exists through the UTL_FILE.FGTATTR procedure and then remove the archived alert log if it exists. Also, be aware that this code will work if there are holes in the span for the date offset and one of the files does not exist.

PROCEDURE remove (in_date_offset IN NUMBER) IS
BEGIN
  build_alertlog_name;
  check_alertlog_name;
  FOR date_offset IN 1..in_date_offset LOOP
    date_file := 	TO_CHAR(SYSDATE-date_offset,'YYYYMMDD')||
'_'||alertfile;
    UTL_FILE.FGETATTR('BDUMP_DIR',
date_file,
vexists,
vfile_length,
vblocksize);
    IF vexists THEN
      UTL_FILE.FREMOVE ('BDUMP_DIR',date_file);
      dbms_output.put_line(date_file||' - REMOVED');
    END IF;
  END LOOP;
END remove;

How to Use the code.

To get you going, let's first go through the 'How to Use' procedures to put the simple shell on a database box and see the results. After you are comfortable with the shell, I would suggest you modify it by removing the DBMS_OUTPUT.PUT_LINE with a call to code that will search the strings returned, insert them into a table and have email or pager notification wrapped around those important error messages. After you get that done, just put the procedures on every database you want to monitor, schedule a task or dbms_job around it and then sleep peacefully.

How to Use

1.      log into your database of choice as the SYS user

2.      compile the package

SQL> @that_darn_alert_log

3.      set serveroutput on

SQL> set serveroutput on

4.      initial setup of directory object

SQL> exec that_darn_alert_log.bdump_dir_setup

5.      to read and display the current alert log

SQL> exec that_darn_alert_log.read(0)

6.      to archive the current alert log

SQL> exec that darn_alert_log.archive

7.      to list all archived logs starting 30 days from current date

SQL> exec that_darn_alert_log.list(30)

8.      to remove a single archived log with date offset of 45

SQL> exec that_darn_alert_log.remove(45,0)

9.      to remove all archived logs from the last week

SQL> exec that_darn_alert_log.remove(14,6)