Manipulating Oracle Files with UTL_FILE - Page 3

January 9, 2004

The Code

CREATE OR REPLACE PACKAGE 
          that_darn_alert_log 
	  AS

 PROCEDURE bdump_dir_setup;
 PROCEDURE build_possible_alert_log_names;
 PROCEDURE build_alertlog_name;
 PROCEDURE check_alertlog_name;
 PROCEDURE read   (in_date_offset IN NUMBER);
 PROCEDURE archive;
 PROCEDURE remove (in_date_offset IN NUMBER, 
   number_of_days IN NUMBER);
 PROCEDURE list   (in_date_offset IN NUMBER);

END that_darn_alert_log;
/
CREATE OR REPLACE PACKAGE BODY 
          that_darn_alert_log 
	  AS

bdump_dir   VARCHAR2(500);
alertlist   VARCHAR2(4000);
sid         VARCHAR2(100);
comma       VARCHAR2(1);       
alertfile   VARCHAR2(255);
itexists    NUMBER;
fileat      BFILE;
pointa      INTEGER;
pointb      INTEGER;
v_length    INTEGER;
r_char10    RAW(4);
char10      VARCHAR2(1) := CHR(10);
startdate   VARCHAR2(100);
r_startdate RAW(100);
buffer      VARCHAR2(800);
buffer2     VARCHAR2(800);

vexists      BOOLEAN;
vfile_length NUMBER;
vblocksize   NUMBER;
date_offset NUMBER;
date_file   VARCHAR2(255);

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;

PROCEDURE build_alertlog_name IS
BEGIN
 alertlist := NULL;
 comma     := NULL;
 EXECUTE IMMEDIATE 
 'SELECT value '||
 '  FROM v$parameter '||
 ' WHERE name = ''db_name'''                      
    INTO sid;
 build_possible_alert_log_names;
 EXECUTE IMMEDIATE 
 'SELECT value '||
 '  FROM v$parameter '||
 ' WHERE name = ''instance_name'''
    INTO sid;
 build_possible_alert_log_names;
 EXECUTE IMMEDIATE 
 'SELECT substr(global_name,1,'||
 '        instr(global_name,''.'',-1)-1) '||
 '  FROM global_name' 
    INTO sid;
 build_possible_alert_log_names;

 r_char10    := UTL_RAW.CAST_TO_RAW(char10);
END build_alertlog_name;

PROCEDURE check_alertlog_name IS
BEGIN
 pointa    := 1;
 LOOP
  pointb   := INSTR(alertlist,
              ',',
              pointa,1);
  v_length := pointb - pointa;
  alertfile:= SUBSTR(alertlist,
              pointa,
              v_length);
  pointa   := pointb + 1;
  itexists := DBMS_LOB.FILEEXISTS(
              BFILENAME('BDUMP_DIR',alertfile));
  IF itexists = 1 THEN
   pointb  := 0;
   EXIT;
  END IF;
 END LOOP;
END check_alertlog_name;

PROCEDURE build_possible_alert_log_names IS
BEGIN
 alertlist := alertlist||
              comma||
              'alert_'||
              lower(sid)||
              '.log';
 comma     := ',';
 alertlist := alertlist||
              comma||
              upper(sid)||
              'ALRT.LOG';
END build_possible_alert_log_names;

PROCEDURE read (in_date_offset IN NUMBER) IS
BEGIN
 DBMS_OUTPUT.ENABLE(1000000);
 build_alertlog_name;
 check_alertlog_name;
 date_offset := in_date_offset;
 IF date_offset = 0 THEN
   date_file := alertfile;
 ELSE
   date_file := TO_CHAR(SYSDATE-date_offset,'YYYYMMDD')||'_'||alertfile;
 END IF;
 UTL_FILE.FGETATTR('BDUMP_DIR',date_file,vexists,vfile_length,vblocksize);
 IF vexists THEN
 fileat       := BFILENAME('BDUMP_DIR',date_file);
 DBMS_LOB.FILEOPEN(fileat,DBMS_LOB.FILE_READONLY);

 startdate  := TO_CHAR(TRUNC(SYSDATE),'Dy Mon DD');
 r_startdate  := UTL_RAW.CAST_TO_RAW(startdate);
 pointa := DBMS_LOB.INSTR(fileat,r_startdate,1,1);

 IF pointa = 0 THEN
   EXECUTE IMMEDIATE
   'SELECT TO_CHAR(TRUNC(SYSDATE),''Dy Mon '')||'||
   ' DECODE(SUBSTR(TO_CHAR(TRUNC(SYSDATE),''DD''),1,1),0,'' '')'||
   ' ||SUBSTR(TO_CHAR(TRUNC(SYSDATE),''DD''),2,1)'||
   '  FROM dual'
      INTO startdate;
   r_startdate  := UTL_RAW.CAST_TO_RAW(startdate);
   pointa := DBMS_LOB.INSTR(fileat,r_startdate,1,1);
 END IF;

 IF pointa != 0 THEN
  LOOP
   pointb  := DBMS_LOB.INSTR(fileat,r_char10,pointa,1);
   EXIT WHEN pointb = 0;
   v_length  := pointb - pointa;
   buffer  := DBMS_LOB.SUBSTR(fileat,v_length,pointa);
   buffer2 := utl_raw.cast_to_varchar2(buffer);
   DBMS_OUTPUT.PUT_LINE(buffer2);
   pointa  := pointb + 1;
  END LOOP;
 END IF;
 DBMS_LOB.FILECLOSE(fileat);
 END IF;

END read;

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;

PROCEDURE remove (in_date_offset IN NUMBER, number_of_days IN NUMBER) IS
BEGIN
  build_alertlog_name;
  check_alertlog_name;
  FOR date_offset IN (in_date_offset - number_of_days)..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;

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;

BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
END that_darn_alert_log;
/

The Oracle alert log contains loads of information. It is not enough just to look at the alert log every day; you should also be in the routine of archiving the alert log so that you can go back in time to determine the history of errors and database happenings. Your ability to keep the current archive log manageable, and maintain a clean mechanism to archive the alert log are the first steps in maintaining a clean database environment. Using some new features in the UTL_FILE package, you now have the tools. Feel free to modify these procedures for the alert log but also be aware there are other files such as sqlnet.log that could be encapsulated in these procedures. Happy file hunting.

» See All Articles by Columnist James Koopmann








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers