When Oracle 9i was introduced, one of many new features was the external tables feature. External tables were added as a database feature to build in more ETL capability for data warehousing.

An external table is not really a table at all, but a description of a text file and a record of its location, both stored in the Oracle data dictionary. It can be queried via SELECT statements, and is a boon for ETL operations in a data warehouse.

As with most new features, it didn’t take long to find other uses for external tables. One popular example is to use an external table to view the contents of the alert.log file via a simple SELECT statement.

create or replace directory bdump 
as '/u01/app/oracle/admin/ts01/bdump';

drop table alert_log;

create table alert_log ( text varchar2(400) )
organization external (
  type oracle_loader
  default directory BDUMP
  access parameters (
    records delimited by newline
    nobadfile
    nodiscardfile
    nologfile
   )
   location('alert_ts01.log')
)
reject limit unlimited
/

Now a simple query on the ALERT_LOG table will display the contents of the alert.log for the database ts01:

select * from system.alert_log;
Wed Oct 15 23:36:07 2003
Starting ORACLE instance (normal)
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
  processes                = 150
  timed_statistics         = TRUE
Thread 1 advanced to log sequence 1561
...
  Current log# 1 seq# 1561 mem# 0: /u01/app/oracle/oradata/ts01/redo01.log
Mon Feb  9 22:18:20 2004T
hread 1 advanced to log sequence 1562
  Current log# 2 seq# 1562 mem# 0: /u01/app/oracle/oradata/ts01/redo02.log
Mon Feb  9 22:18:20 2004
ARC1: Media recovery disabled

This is simple enough, but not too useful in most cases. Most sites already have processes for monitoring the alert.log, so displaying it via SQL seems little more than a novelty.

A more practical application would be to create external tables that allow you to view other trace files generated by the database. Besides the trace files that may appear in the BDUMP or UDUMP directories due to an error condition, there are also the trace files that appear when the DBA initiates a SQL trace, or a 10046 or 10053 trace.

These trace files are required for troubleshooting performance issues with problem SQL statements.

alter session set events '10046 trace name context forever, level 8';

select e.ename, e.job, d.dname
from scott.emp e, scott.dept d
where e.deptno = d.deptno
/

exit

In the test database used to write this article, the trace file /u01/app/oracle/admin/ts01/udump/ts01_ora_15358.trc was created.

If you are working directly on the server, it may not be any problem at all to access this file. If you are not on the server, or worse yet, simply don’t have access to the server, it becomes rather difficult to read the trace file via normal methods.

That gives us a good reason to find a new use for external tables. If an external table can be used to read the alert.log, why not exploit this feature to also read other trace files?

This is exactly what we will do now.

Using External Tables to Read Other Trace Files

Before creating external tables for trace files, you need a method to see what files are available in the BDUMP and UDUMP directories.

Java in the database is the perfect tool for this, as this will allow you to access the server’s Oracle directories. When testing this, be sure to substitute the location of the BDUMP and UDUMP directories for your own system in the CREATE DIRECTORY commands. These commands will need to be executed by an account with DBA privileges. I used the SYSTEM account to create these objects.

create or replace directory bdump 
as '/u01/app/oracle/admin/ts01/bdump';

create or replace directory udump 
             as '/u01/app/oracle/admin/ts01/udump';

drop table dirlist;

create global temporary table dirlist (
  directoryname varchar2(30)
  , filename varchar2(255)
  , filesize number
  , filedate date
)
on commit preserve rows
/

create or replace
  and compile java source named "DirList"
as 
  import java.io.*;
  import java.sql.*;

  public class DirList
  {
    public static void getList(String directory, String oracleDirName)
    throws SQLException
    {
      File path = new File( directory );
      String[] fileList = path.list();
      String fileName;
      long fileSize;
      long fileDate;
      for(int i = 0; i < fileList.length; i++)
      {
        fileName = fileList[i];
        File lfile = new File(directory + '/index.html' + fileName );
        fileSize = lfile.length();
        fileDate = lfile.lastModified();
        // just look at trace files
        if ( fileName.endsWith(".trc")
          || fileName.endsWith(".TRC")
        ) {
          #sql { insert into dirlist (directoryname, 
filename,filesize,filedate)
          values (:oracleDirName, :fileName, :fileSize
             ,to_date('01/01/1970','mm/dd/yyyy') + :fileDate / ( 
24*60*60*1000)
            )
          };
        }
      }
    }
  }
/

show errors java source DirList

create or replace
procedure get_dir_list( directory_in in varchar2, oracle_directory in 
varchar2 )
  as language java
name 'DirList.getList( java.lang.String, java.lang.String )';
/

show errors procedure get_dir_list

To see all available trace files, simply SELECT from the DIRLIST table:

select * from dirlist;
23:10:19 poirot.jks.com - system@ts01 SQL> /

DIRECTORY  FILENAME                         FILESIZE FILEDATE
---------- ------------------------------ ---------- -------------------
BDUMP      ts01_ora_16740.trc                    657 01/06/2004 20:49:23
BDUMP      ts01_pmon_16828.trc                  2208 01/06/2004 20:50:25

…
UDUMP ts01_ora_15358.trc 3560 02/10/2004 06:39:49
UDUMP ts01_ora_15520.trc 1421 02/10/2004 06:58:03
UDUMP ts01_ora_15609.trc 1425 02/10/2004 07:00:22
UDUMP ts01_ora_15633.trc 1425 02/10/2004 07:00:47

35 rows selected.

Viewing the trace file is now a simple matter of creating an external table that references the trace file:

create table ts01_ora_15358 ( text varchar2(400) )
organization external (
  type oracle_loader
  default directory UDUMP
  access parameters (
    records delimited by newline
    nobadfile
    nodiscardfile
    nologfile
  )
  location('ts01_ora_15358.trc')
)  reject limit unlimited
/

Please note the use of the NOBADFILE, NODISCARDFILE, and NOLOGFILE access parameters in the ORGANIZATION INTERNAL clause. It is rather important to either preclude the creation of these SQL Loader files, or redirect them. If you don’t, these files will be created in the same directory that the trace files are in, (probably not something you’d want).

The new external table can now be used to view the trace file via a SELECT statement:

select * from ts01_ora_15358;

/u01/app/oracle/admin/ts01/udump/ts01_ora_15358.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning
…
=====================
PARSING IN CURSOR #1 len=87 dep=0 uid=60 oct=3 lid=60 tim=1051167177571159              
hv=3286900709 ad='5caecb2
c'

select e.ename
from scott.emp e
where e.deptno = d.deptno
END OF STMT
PARSE #1:c=0
EXEC #1:c=0
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1650815232 p2=1 p3=0
…

STAT #1 id=3 cnt=4 pid=1 pos=2 obj=32050 op='TABLE ACCESS FULL OBJ#(32050) 
(cr=10 r=0 w=0 time=155 u
s)'

60 rows selected.

With this example as a catalyst, it would be a fairly simple matter to create a PL/SQL package that could parse the alert.log for trace files, use the UTL_FILE package to determine which files still exist, and create external files for each.

You could also automatically create external tables for each file in the BDUMP and UDUMP directories.

Creating the alert_log_util package for Oracle 9i

I have included the following set of scripts to help you create the alert_log_util package for Oracle 9i. This package can be used to create external tables for the trace files in the BDUMP and UDUMP oracle directories. They can then be viewed directly from the database, making it possible to examine trace files without the need to log on on to the server, or map a shared drive.

They also provide a function for the alert.log that allow you to view only the error lines, and to build external tables for all trace files found in the alert.log.

You should run scripts in the order as follows:

as SYS:

alert_log_grants.sql

as SYSTEM:

alert_log_tmp_table.sql

alert_log_external_table.sql

alert_log_dirlist.sql

alert_log_util_pkg.sql

Whether or not you extend this concept, perusing the contents of Oracle trace files on remote or inaccessible servers just became much easier.

--

Jared Still is a DBA at RadiSys, an embedded solution provider. Jared has been an IT professional for 22 years, the last 10 of which have been as an Oracle DBA. He is the author of several Oracle articles and co-author of Perl for Oracle DBAs as well as being an editor for the IOUG Journal SELECT.