You will have to change:
- Create directory for directory of alertlog file as bdump_dir : CREATE OR REPLACE DIRECTORY BDUMP_DIR AS '/oracle/diag/rdbms/test/test/trace';
- Update <SID> to your sid in table script .
*/
create table alert_log (
line_number number,
text varchar2(4000)
)
organization external (
type oracle_loader
default directory bdump_dir
access parameters (
records delimited by newline
nobadfile
nodiscardfile
nologfile
fields missing field values are null
(
line_number recnum,
text position(1:4000)
)
)
location ('alert_<SID>.log')
)
reject limit unlimited
noparallel;
SQL> select * from alert_log where text like '%ORA-27037%' ;
LINE_NUMBER TEXT
----------- -------------------------------------------------------
254 ORA-27037: unable to obtain file status
262 ORA-27037: unable to obtain file status
268 ORA-27037: unable to obtain file status
277 ORA-27037: unable to obtain file status
285 ORA-27037: unable to obtain file status
291 ORA-27037: unable to obtain file status
298 ORA-27037: unable to obtain file status
306 ORA-27037: unable to obtain file status
312 ORA-27037: unable to obtain file status
9 rows selected.
