sql查看alert日志

1、查看 alter日志所在目录

SQL> show parameter dump name type value;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      f:\app\administrator\diag\rdbms\wms\wms\trace
core_dump_dest                       string      f:\app\administrator\diag\rdbms\wms\wms\cdump
max_dump_file_size                   string      unlimited
shadow_core_dump                     string      none
user_dump_dest                       string      f:\app\administrator\diag\rdbms\wms\wms\trace
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      
db_name                              string      wms
db_unique_name                       string      wms
global_names                         boolean     FALSE
instance_name                        string      wms
lock_name_space                      string      
log_file_name_convert                string      
service_names                        string      wms
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_type                        string      RDBMS
plsql_code_type                      string      INTERPRETED

background_dump_dest参数确定alert日志文件位置

2、创建目录

SQL> create or replace directory alert as 'f:\app\administrator\diag\rdbms\wms\wms\trace';
 
Directory created

3、创建外部表

SQL> create table alert(log varchar2(1000))
  2  organization external(type oracle_loader default directory alert access parameters (records delimited by newline) location('alert_wms.log')) reject limit unlimited;
 
Table created

4、查找日志

SQL> select * from alert t where t.log like '%ORA-%';