Bu Blogda Ara

10 Ocak 2012 Salı

Create external table of alert_log from alertlog file

/*
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.