在线查看alter.log文件内容

在Oracle9i中，使用External Table和动态SQL，在线地查看alter.log文件内容，从而进行某些查询、工作调度来监控错误。[@more@]

在Oracle9i中，使用External Table和动态SQL，在线地查看alter.log文件内容，从而进行某些查询、工作调度来监控错误。

使用下面的脚本可以实现上述的要求。

SQL> drop table e_alert_log;

Table dropped.

SQL> drop table t_perm_alert_log;

Table dropped.

SQL> drop table t_temp_alert_log;

Table dropped.

SQL> create or replace DIRECTORY alert_dir as 'D:oracleadminoracle9bdump';

Directory created.

SQL> create table e_alert_log
2 (detail_line VARCHAR2(2000)
3 ) ORGANIZATION EXTERNAL
4 (TYPE oracle_loader
5 DEFAULT DIRECTORY alert_dir
6 ACCESS PARAMETERS
7 ( RECORDS DELIMITED BY NEWLINE
8 nobadfile
9 nologfile
10 nodiscardfile
11 FIELDS TERMINATED by ' '
12 MISSING FIELD VALUES ARE NULL
13 (detail_line )
14 )
15 LOCATION('alert_oracle9.log') )
16 parallel 5
17 REJECT LIMIT UNLIMITED;

Table created.

SQL> create table t_perm_alert_log
2 (row_id number,
3 detail_line varchar2(2000) )
4 tablespace users
5 storage (initial 1m next 1m pctincrease 0);

Table created.

SQL>
SQL> create table t_temp_alert_log
2 (row_id number,
3 detail_line varchar2(2000) )
4 tablespace users
5 storage (initial 1m next 1m pctincrease 0);

Table created.

SQL>
SQL>
SQL> create or replace procedure update_alert as
2 BEGIN
3
4 -- This truncates the temp table from the last run...
5 execute immediate 'truncate table t_temp_alert_log';
6
7 -- Now, populate the temp table with all the alert log contents....
8 execute immediate 'insert into t_temp_alert_log select rownum,
9 detail_line from e_alert_log';
10
11 -- Now, populate the perm table with just the changes.....
12 execute immediate 'insert into t_perm_alert_log select row_id,
13 detail_line from t_temp_alert_log b
14 where b.row_id > (select nvl(max(a.row_id),0) from t_perm_alert_log a)';
15 END;
16 /

Procedure created.

SQL> exec update_alert

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.
SQL> select DETAIL_LINE from t_temp_alert_log sample(0.1) ;

DETAIL_LINE
--------------------------------------------------------------------------------
ORA-12012: error on auto execute of job 1
Created Undo Segment _SYSSMU18$
Undo Segment 8 Offlined

3 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/556989/viewspace-903448/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/556989/viewspace-903448/