Author: Jared Still

Page: 1 2

Oracle external tables were added in Oracle 9i database as a feature to build in more ETL capability for data warehousing. An external table is not really a table. Rather it is 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.

Oracle external tables to view alert.log file contents:

Most popularly Oracle external tables are used to view the contents of the alert.log file.

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
/

Below simple query on the ALERT_LOG table will display the contents of the alert.log for the database

select * from system.alert_log;
Wed Dec 15 23:36:07 2007
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 2004
T 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 but unluckily not too useful in most cases as most sites already have processes for monitoring the alert.log.

Oracle external tables to view other trace files:

Some trace files appear in the BDUMP or UDUMP directories due to an error condition. Whereas others appears 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. You can create external tables that allow you to view other trace files generated by the database. Below code will create a trace file /u01/app/oracle/admin/ts01/udump/ts01_ora_15358.trc

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

It is not problem to access this file if you are working directly on the server. However if you are not on the server or don't have access to the server, it becomes difficult to read the trace file via normal methods. As an external table can be used to read the alert.log, so we can exploit this feature to also read other trace files as well.

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Inside Oracle DETERMINISTIC Clause!!
   Best Setting for Oracle Multiblock Read Count!!
   Performance Optimization by avoiding Table Locks!!
   Great Tips for Using Oracle Automatic Database Diagnostic Monitor!!