查询语句如下：

DECLARE

　PATH_BDUMP VARCHAR2(4000);

　NAME_ALERT VARCHAR2(4000);

　INS_NAME VARCHAR2(200);

BEGIN

　SELECT VALUE

　　INTO PATH_BDUMP

　　FROM SYS.V_$PARAMETER

　 WHERE NAME = 'BACKGROUND_DUMP_DEST';

　SELECT 'ALERT_' || VALUE || '.LOG'

　　INTO NAME_ALERT

　　FROM SYS.V_$PARAMETER

　 WHERE NAME = 'INSTANCE_NAME';

　SELECT VALUE

　　INTO INS_NAME

　　FROM SYS.V_$PARAMETER

　 WHERE NAME = 'INSTANCE_NUMBER';

　　IF INS_NAME = '0' THEN

　　　INS_NAME := '';

　　END IF;

　　EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY BDUMP' || INS_NAME ||' AS ''' || PATH_BDUMP || '''';

　　EXECUTE IMMEDIATE 'CREATE TABLE ALERT_LOG_VIEW' || INS_NAME ||' (MSG_LINE VARCHAR2(4000) ) ' ||' ORGANIZATION EXTERNAL ' || ' (TYPE ORACLE_LOADER ' ||' DEFAULT DIRECTORY BDUMP' || INS_NAME ||' ACCESS PARAMETERS ( ' ||' RECORDS DELIMITED BY NEWLINE ' || ' NOBADFILE ' ||' NOLOGFILE ' || ' NODISCARDFILE ' || ' SKIP 0 ' ||' READSIZE 10485760 ' || ' FIELDS LDRTRIM ' ||' REJECT ROWS WITH ALL NULL FIELDS ' ||' (MSG_LINE (1:1000) CHAR(1000)) ' || ' ) ' ||' LOCATION (''' || NAME_ALERT || ''') )' ||' REJECT LIMIT UNLIMITED ' ||' NOPARALLEL NOMONITORING ';

END;

/

[@more@]

ITPub博客

通过外部表查看alert.log的内容

最新文章