Access the alert log file in table format with SQL Query in Oracle
You can access the alert log file of Oracle database in form of table. Its makes you easy for access the alert log file and check the ORA- or TNS- error in alert log with help of SQL Queries.
Following example show you to access the alert log file. you can use any other text file also in similar format or change format.
Following Steps configured alert log file in form of table
Step 1: Create a directory object that points to the diagnostic trace directory.
SQL> create directory t_loc as '/ora01/app/oracle/diag/rdbms/o12c/o12c/trace';
Step 2: Create table
create table alert_log_file(
( type oracle_loader
default directory t_loc
access parameters (
records delimited by newline
fields terminated by '#$~=ui$X'
missing field values are null
reject limit unlimited;
Step3: Access the Alert log file with help of SQL queries.
select * from alert_log_file where alert_text like 'ORA-%';