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 3

By James Koopmann

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



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


















Thanks for your registration, follow us on our social networks to keep up-to-date