Access the alert log file in table format with SQL Query in Oracle

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(
alert_text varchar2(4000))
organization external
( type oracle_loader
default directory t_loc
access parameters (
records delimited by newline
nobadfile
nologfile
nodiscardfile
fields terminated by '#$~=ui$X'
missing field values are null
(alert_text)
)
location ('alert_o12c.log')
)
reject limit unlimited;

Step3: Access the Alert log file with help of SQL queries.

select * from alert_log_file where alert_text like 'ORA-%';

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s