Introduced in Oracle 9i, external tables are not really tables, but descriptions and locations of external text files that are stored in the database dictionary.  They allow the external tables to be read through SELECT statements.  One popular usage example is to create an external table to view the contents of the alert.log file via a simple SELECT statement .

create or replace directory bdump
as 'e:\oracle\admin\ora92\bdump';

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.log')
)
reject limit unlimited
/
Querying the ALERT_LOG table will display the contents of the database's alert.log:

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#

e:\oracle\ora92\oradata\redo01.log
Mon Feb  9 22:18:20 2004T
hread 1 advanced to log sequence 1562
  Current log# 2 seq# 1562 mem#

e:\oracle\ora92\oradata\redo02.log
Mon Feb  9 22:18:20 2004
ARC1: Media recovery disabled

This example would be particularly useful for users who don't have access or permissions to the directories the log (or trace files, for that matter) are stored in.  

Another use of external tables is importing flat files into the database (you can embed the SQL*Loader control file into the table DLL script).