--#####################################################
--#### Alert-Datei-Scan Skript fuer NT / 2000 / XP   
--#### Fuer Oracle 9.x !            
--#### geschrieben von Marco Patzwahl             
--#### Firma MuniQSoft (c) 2002   
--#### Dieses Skript liest die Alert-Datei alle 5 Minuten
--#### in eine Tabelle (alert_tab) ein, die dann mit SQL
--#### Befehlen ausgewertet werden kann
--####            
--#### Durchfuehrung auf Produktivdatenbanken auf   
--#### eigene Gefahr !!!!!                        
--#### Version V 1.0                              
--#### Aenderungen                     
--#### 
--####
--#### Achtung es wird der SYSTEM Account benoetigt
--#### Zusaetzlich wird das Recht CREATE TABLE Recht
--#### temporaer direkt an SYSTEM vergeben !
--#####################################################


set serveroutput on

grant create table to system;

CREATE SEQUENCE system.alert_seq START WITH 1;

CREATE TABLE system.alert_tab (
nr number,
prio number,
text varchar2(400),
bemerkung varchar2(100),
datum date);

CREATE PUBLIC SYNONYM alert_tab FOR system.alert_tab;



-- UNIX oder Windows Plattform ?
COL os_platform NEW_VALUE os_platform
select decode(instr(upper(product), 'WINDOWS',1), 0 , 'UNIX', 
'WINDOWS') os_platform
  from PRODUCT_COMPONENT_VERSION
 WHERE upper(PRODUCT) LIKE 'TNS%';


-- Verzeichnis fuer Alert-Datei
COL dirname NEW_VALUE dirname
select decode('&&os_platform', 'WINDOWS', value || '\', value || '/')  dirname
from sys.v_$parameter
where name = 'background_dump_dest';


-- Alertdateiname herausfinden
COL alert_datei NEW_VALUE alert_datei
select 
decode('&&os_platform','WINDOWS','alert_'||value||'.log','alert_'||value||'.log')  alert_datei 
from sys.v_$parameter 
where name = 'db_name';

CREATE OR REPLACE DIRECTORY alert_dir as '&&dirname';

REM Procedure die aus dbms_job heraus aufgerufen werden kann

CREATE OR REPLACE PROCEDURE system.get_alertfile
(modus IN VARCHAR2 DEFAULT NULL)
IS

num_rows	NUMBER;
tmp_string	VARCHAR2(8192);
os_platform	VARCHAR2(256);
dirname		VARCHAR2(512);
alert_datei	VARCHAR2(32);
alert_dir 	VARCHAR2(512);
nls_string	VARCHAR2(256);
BEGIN


-- Herausfinden wieviele Zeilen schon gelesen wurden
SELECT nvl(count(*),0) INTO num_rows FROM system.alert_tab;

begin
	EXECUTE IMMEDIATE 'drop table system.alert_tmp';
	exception
	when others then null;
end;


tmp_string:='
CREATE TABLE system.alert_tmp
(alert_text varchar2(1000))
ORGANIZATION EXTERNAL
(
  TYPE oracle_loader
  DEFAULT DIRECTORY alert_dir
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE 
    SKIP '||num_rows||'
     FIELDS TERMINATED BY '||chr(39)||'§'||chr(39)||'    
     MISSING FIELD VALUES ARE NULL
     (alert_text)
   )
LOCATION ('||chr(39)||'&&alert_datei'||chr(39)||')
)
REJECT LIMIT UNLIMITED';


EXECUTE IMMEDIATE tmp_string;

-- In eine permanente Tabelle umkopieren
nls_string:='to_date(substr(alert_text,4),'||chr(39)||'Mon DD HH24:MI:SS YYYY'||chr(39)||','||chr(39)||
	'nls_date_language=AMERICAN'||chr(39)||')';

tmp_string:='INSERT INTO system.alert_tab
SELECT system.alert_seq.nextval,
decode(sign(instr(alert_text,'||chr(39)||'ORA-'||chr(39)||')),1,1,0),
alert_text,
null,
case substr(alert_text,1,4) 
WHEN '||chr(39)||'Mon '||chr(39)||' THEN '||nls_string||'
WHEN '||chr(39)||'Tue '||chr(39)||' THEN '||nls_string||'
WHEN '||chr(39)||'Wed '||chr(39)||' THEN '||nls_string||'
WHEN '||chr(39)||'Thu '||chr(39)||' THEN '||nls_string||'
WHEN '||chr(39)||'Fri '||chr(39)||' THEN '||nls_string||'
WHEN '||chr(39)||'Sat '||chr(39)||' THEN '||nls_string||'
WHEN '||chr(39)||'Sun '||chr(39)||' THEN '||nls_string||'
END
FROM system.alert_tmp';

EXECUTE IMMEDIATE tmp_string;
commit;

END;
/
show errors

exec system.get_alertfile

declare
job_proz	number;
num_rows		number;
begin
-- Prufen ob Pfile oder SPfile benutzt wurde
SELECT count(isspecified) into num_rows from v$spparameter 
where isspecified='true';
if num_rows>0 then
   SELECT nvl(value,0) into job_proz 
   from v$spparameter where name='job_queue_processes';
   if job_proz=0 then
   	execute immediate 'ALTER SYSTEM set job_queue_processes=1';
   end if;
else
   SELECT nvl(value,0) into job_proz 
   from v$parameter where name='job_queue_processes';
   if job_proz=0 then
   	execute immediate 'ALTER SYSTEM set job_queue_processes=1';
   end if;
end if;
end;
/


variable job_nr number
exec sys.dbms_job.submit(:job_nr,'begin system.get_alertfile; end;',sysdate,'sysdate+1/(24*12)');
commit;

prompt Es wurde eine Tabelle angelegt (alert_tab), die alle 5 Minuten mit der Alert-datei
prompt synchronisiert wird, via DBMS_JOB mit Job-Nr 
print job_nr
revoke create table from system;

prompt Die Tabelle alert_tab hat folgendes aussehen:

col prio format 99
col text format a100
col bemerkung format a20

desc alert_tab

set linesize 900
col text format a80
prompt Die letzten 20 Zeilen der Alert-Datei lauten:
select * from alert_tab
where nr >(select count(*)-20 from alert_tab);