Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 9, 2004

Manipulating Oracle Files with UTL_FILE - Page 2

By James Koopmann

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)


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM