外部表作为sqlldr的替换产品，在使用范围上，还是有一定的局限性质：外部表也能将文本装载进数据库，但是外部表不支持多用户的并发修改，也不支持LOB类型，而且访问路径中的文件必须在服务器端。
以下是具体的语法：
DROP DIRECTORY bdump_dir;
create or replace directory bdump_dir AS ‘D:oracleadminoralocalbdump’
DROP TABLE alert_log;
CREATE TABLE alert_log
( text_line varchar2(255))
ORGANIZATION EXTERNAL –表示这是一个外部表
(
TYPE ORACLE_LOADER –外部表的具体信息：外部表的2种类型之一：oracle_loader
DEFAULT DIRECTORY bdump_dir –之前建立的访问路径
ACCESS PARAMETERS
(
records delimited by newline –装载记录以一行为一个记录
fields –和sqlldr类型，字段名称
REJECT ROWS WITH ALL NULL FIELDS — 不装载空行记录
)
LOCATION
(‘alert_oralocal.log’) –访问路径下的文件名
)
–默认为REJECT LIMIT 0。表示一旦有记录被拒绝，马上停止导入。
–REJECT LIMIT UNLIMITED表示无论存在多少条报错记录，都将其忽略继续导入
REJECT LIMIT UNLIMITED;
–应用举例：
–查曾经报错记录：
select * from alert_log a WHERE lower(a.text_line) LIKE 'ora-%'; --查曾经报错记录： SELECT to_char(last_time, 'yyyy-mm-dd hh24:mi') 停机时间, to_char(start_time, 'yyyy-mm-dd hh24:mi') 启动时间, round((start_time - last_time) * 24 * 60, 2) 停机时间间隔_分钟, round((last_time - lag(start_time) over(ORDER BY r)), 2) 持续运行时间_天, CASE WHEN (lead(r) over(ORDER BY r) IS NULL) THEN round((SYSDATE - start_time), 2) END 至今连续运行时间_天 FROM (SELECT r, to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY','nls_date_language=american') last_time, to_date(start_time, 'Dy Mon DD HH24:MI:SS YYYY','nls_date_language=american') start_time FROM (SELECT r, text_line, lag(text_line, 1) over(ORDER BY r) start_time, lag(text_line, 2) over(ORDER BY r) last_time FROM (SELECT rownum r, text_line FROM alert_log WHERE text_line LIKE '___ ___ __ __:__:__ 20__' OR text_line LIKE 'Starting ORACLE instance %')) WHERE text_line LIKE 'Starting ORACLE instance %' AND last_time NOT LIKE 'Starting ORACLE instance %' AND start_time not LIKE 'Starting ORACLE instance %');
SQL> --查曾经报错记录： SQL> SELECT to_char(last_time, 'yyyy-mm-dd hh24:mi') 停机时间, 2 to_char(start_time, 'yyyy-mm-dd hh24:mi') 启动时间, 3 round((start_time - last_time) * 24 * 60, 2) 停机时间间隔_分钟, 4 round((last_time - lag(start_time) over(ORDER BY r)), 2) 持续运行时间_天, 5 CASE 6 WHEN (lead(r) over(ORDER BY r) IS NULL) THEN 7 round((SYSDATE - start_time), 2) 8 END 至今连续运行时间_天 9 FROM (SELECT r, 10 to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY','nls_date_language=american') last_time, 11 to_date(start_time, 'Dy Mon DD HH24:MI:SS YYYY','nls_date_language=american') start_time 12 FROM (SELECT r, 13 text_line, 14 lag(text_line, 1) over(ORDER BY r) start_time, 15 lag(text_line, 2) over(ORDER BY r) last_time 16 FROM (SELECT rownum r, text_line 17 FROM alert_log 18 WHERE text_line LIKE '___ ___ __ __:__:__ 20__' 19 OR text_line LIKE 'Starting ORACLE instance %')) 20 WHERE text_line LIKE 'Starting ORACLE instance %' AND last_time NOT LIKE 'Starting ORACLE instance %' 21 AND start_time not LIKE 'Starting ORACLE instance %'); 停机时间 启动时间 停机时间间隔_分钟 持续运行时间_天 至今连续运行时间_天 ---------------- ---------------- ----------------- --------------- ------------------- 2006-09-08 21:16 2006-09-08 21:16 0.07 2006-09-09 02:43 2006-09-09 02:43 0 0.23 2006-09-09 03:04 2006-09-09 03:04 0 0.01 2006-09-09 03:05 2006-09-09 03:06 0.55 0 2006-09-09 03:06 2006-09-09 03:06 0.27 0 2006-09-09 03:09 2006-09-09 03:10 0.23 0 2006-09-09 03:10 2006-09-09 03:10 0.37 0 2006-09-09 03:11 2006-09-09 03:11 0.1 0 2006-09-09 03:11 2006-09-09 03:11 0 0 2006-09-09 03:13 2006-09-09 03:34 21.38 0 2006-09-09 03:34 2006-09-09 03:35 0.15 0 2006-09-09 03:35 2006-09-09 03:58 23.55 0 2006-09-09 04:10 2006-09-09 04:10 0.5 0.01 2006-09-09 04:16 2006-09-09 04:17 0.47 0 2006-09-09 04:24 2006-09-09 04:25 0.95 0.01 2006-09-09 04:30 2006-09-09 04:33 2.95 0 2006-09-09 04:34 2006-09-09 04:34 0.5 0 2006-09-09 04:37 2006-09-09 04:39 2.52 0 2006-09-09 04:43 2006-09-09 04:43 0.2 0 2006-09-09 04:47 2006-09-09 04:49 1.98 0 2006-09-09 05:00 2006-09-09 05:00 0.75 0.01 2006-09-09 05:01 2006-09-09 05:02 0.95 0 2006-09-09 05:02 2006-09-09 05:02 0.25 0 2006-09-09 05:03 2006-09-09 05:24 20.6 0 2006-09-09 13:15 2006-09-09 13:15 0 0.33 2006-09-09 16:33 2006-09-09 16:33 0 0.14 2006-09-09 22:34 2006-09-09 22:34 0 0.25 2006-09-10 01:29 2006-09-10 01:29 0 0.12 2006-09-10 02:45 2006-09-10 02:45 0.02 0.05 2006-09-10 15:13 2006-09-10 15:13 0 0.52 2006-09-10 15:22 2006-09-10 15:23 0.45 0.01 2006-09-10 19:31 2006-09-10 19:32 1.07 0.17 2006-09-10 22:02 2006-09-10 22:04 1.33 0.1 2006-09-10 22:09 2006-09-10 22:09 0.13 0 2006-09-10 22:13 2006-09-10 22:16 2.98 0 2006-09-10 22:21 2006-09-10 22:21 0.12 0 2006-09-10 22:28 2006-09-10 22:30 1.88 0.01 2006-09-10 22:49 2006-09-10 22:49 0.22 0.01 2006-09-11 10:59 2006-09-11 10:59 0 0.51 2006-09-11 12:19 2006-09-11 12:19 0 0.06 2006-09-11 23:07 2006-09-11 23:07 0 0.45 2006-09-12 00:28 2006-09-12 00:28 0.68 0.06 2006-09-12 15:19 2006-09-12 15:19 0 0.62 2006-09-12 19:27 2006-09-12 19:27 0 0.17 2006-09-12 20:14 2006-09-12 20:14 0 0.03 2006-09-12 23:40 2006-09-12 23:40 0.42 0.14 2006-09-13 09:06 2006-09-13 09:06 0 0.39 2006-09-13 16:52 2006-09-13 16:52 0 0.32 2006-09-13 22:19 2006-09-13 22:19 0 0.23 2006-09-14 21:29 2006-09-14 21:29 0 0.97 2006-09-15 16:58 2006-09-15 16:58 0 0.81 2006-09-15 17:51 2006-09-15 17:51 0 0.04 2006-09-15 18:33 2006-09-15 18:33 0.13 0.03 2006-09-15 18:47 2006-09-15 18:47 0.1 0.01 2006-09-15 18:47 2006-09-15 18:48 0.37 0 2006-09-15 18:49 2006-09-15 18:49 0.15 0 2006-09-15 18:49 2006-09-15 18:49 0.12 0 2006-09-16 14:19 2006-09-16 14:19 0 0.81 2006-09-16 14:19 2006-09-17 15:56 1536.27 0 2006-09-19 09:15 2006-09-19 09:15 0 1.72 2006-09-19 21:02 2006-09-19 21:02 0 0.49 2006-09-20 14:14 2006-09-20 14:14 0 0.72 2006-09-20 18:20 2006-09-20 18:20 0 0.17 2006-09-20 20:01 2006-09-20 20:01 0 0.07 2006-09-20 20:25 2006-09-20 20:27 1.93 0.02 2006-09-20 20:27 2006-09-20 20:27 0.4 0 2006-09-20 20:28 2006-09-20 20:28 0.12 0 2006-09-20 20:29 2006-09-20 20:41 11.32 0 2006-09-20 20:42 2006-09-20 20:46 4.08 0 2006-09-20 20:48 2006-09-20 20:53 4.82 0 2006-09-20 20:54 2006-09-20 20:54 0.17 0 2006-09-20 20:54 2006-09-20 20:55 0.85 0 2006-09-20 20:57 2006-09-20 20:57 0.68 0 2006-09-20 21:06 2006-09-20 21:06 0.02 0.01 2006-09-21 09:54 2006-09-21 09:54 0 0.53 2006-09-21 18:45 2006-09-21 18:45 0 0.37 2006-09-22 00:48 2006-09-22 00:48 0 0.25 2006-09-22 09:18 2006-09-22 09:18 0 0.35 2006-09-22 16:55 2006-09-22 16:55 0 0.32 2006-09-23 18:35 2006-09-23 18:35 0 1.07 2006-09-23 19:44 2006-09-23 19:44 0 0.05 2006-09-24 00:15 2006-09-24 00:15 0 0.19 2006-09-24 22:22 2006-09-24 22:22 0 0.92 2006-09-26 11:09 2006-09-26 11:09 0 1.53 2006-09-26 14:41 2006-09-26 14:41 0 0.15 2006-09-26 16:00 2006-09-26 16:02 1.75 0.05 2006-09-26 16:24 2006-09-26 16:25 1.03 0.02 2006-09-26 16:28 2006-09-26 16:28 0.12 0 2006-09-26 16:29 2006-09-26 16:29 0.67 0 2006-09-26 16:31 2006-09-26 16:31 0.17 0 2006-09-26 16:45 2006-09-26 16:46 0.73 0.01 2006-09-26 17:16 2006-09-26 17:18 2.13 0.02 2006-09-26 17:58 2006-09-26 17:59 1.3 0.03 2006-09-26 18:06 2006-09-26 18:07 1.53 0 2006-09-27 01:34 2006-09-27 01:34 0 0.31 2006-09-27 18:46 2006-09-27 18:46 0 0.72 2006-09-27 21:22 2006-09-27 21:26 4.28 0.11 2006-09-27 22:32 2006-09-27 22:32 0 0.05 2006-09-27 23:33 2006-09-27 23:33 0 0.04 2006-09-28 11:21 2006-09-28 11:21 0 0.49 2006-09-29 10:04 2006-09-29 10:04 0 0.95 2006-09-30 02:17 2006-09-30 02:17 0 0.68 2006-09-30 14:25 2006-09-30 14:26 0.18 0.51 2006-09-30 16:10 2006-09-30 16:10 0 0.07 2006-09-30 16:12 2006-09-30 16:12 0.2 0 2006-09-30 16:12 2006-09-30 16:13 0.62 0 2006-09-30 16:21 2006-09-30 16:21 0 0.01 2006-09-30 16:22 2006-09-30 16:22 0.65 0 2006-09-30 16:22 2006-09-30 16:23 0.13 0 2006-09-30 16:36 2006-09-30 16:40 3.6 0.01 2006-09-30 16:51 2006-09-30 17:11 20.65 0.01 2006-09-30 17:55 2006-09-30 17:55 0.17 0.03 2006-09-30 18:46 2006-09-30 18:46 0.02 0.04 2006-09-30 18:52 2006-09-30 18:52 0.38 0 2006-09-30 18:52 2006-09-30 18:52 0.15 0 2006-09-30 18:54 2006-09-30 18:54 0.13 0 2006-10-01 18:22 2006-10-01 18:22 0 0.98 2006-10-02 00:13 2006-10-02 00:14 1.02 0.24 2006-10-02 00:17 2006-10-02 00:19 2.18 0 2006-10-02 00:51 2006-10-02 00:53 2.07 0.02 2006-10-02 00:57 2006-10-02 00:57 0.18 0 2006-10-02 00:59 2006-10-02 01:00 0.85 0 2006-10-02 13:17 2006-10-02 13:17 0 0.51 2006-10-02 13:44 2006-10-02 13:47 2.88 0.02 2006-10-02 14:34 2006-10-02 14:34 0.23 0.03 2006-10-04 13:17 2006-10-04 13:17 0 1.95 2006-10-05 23:54 2006-10-05 23:54 0 1.44 2006-10-08 00:04 2006-10-08 00:04 0 2.01 2006-10-08 22:13 2006-10-08 22:13 0 0.92 2006-10-10 21:36 2006-10-10 21:36 0 1.97 2006-10-11 19:39 2006-10-11 19:39 0 0.92 2006-10-13 21:22 2006-10-13 21:22 0 2.07 2006-10-14 20:04 2006-10-14 20:04 0 0.95 2006-10-14 20:17 2006-10-14 20:18 0.57 0.01 2006-10-15 21:46 2006-10-15 21:46 0 1.06 2006-10-16 00:46 2006-10-16 00:52 5.62 0.13 2006-10-16 00:59 2006-10-16 00:59 0.1 0 2006-10-16 01:01 2006-10-16 01:01 0.03 0 2006-10-16 01:03 2006-10-16 01:03 0.2 0 2006-10-16 10:25 2006-10-16 10:25 0 0.39 2006-10-16 19:54 2006-10-16 19:54 0 0.39 2006-10-16 23:02 2006-10-16 23:02 0 0.13 2006-10-17 01:21 2006-10-17 01:21 0 0.1 2006-10-17 11:21 2006-10-17 11:21 0 0.42 2006-10-17 22:28 2006-10-17 22:28 0 0.46 2006-10-18 16:11 2006-10-18 16:11 0 0.74 2006-10-18 22:03 2006-10-18 22:03 0 0.24 2006-10-19 10:19 2006-10-19 10:19 0 0.51 2006-10-20 16:22 2006-10-20 16:22 0 1.25 2006-10-21 22:42 2006-10-21 22:42 0 1.26 2006-10-22 23:09 2006-10-22 23:09 0 1.02 2006-10-23 10:02 2006-10-23 10:02 0 0.45 2006-10-24 18:23 2006-10-24 18:23 0 1.35 2006-10-24 22:33 2006-10-24 22:33 0 0.17 2006-10-25 09:58 2006-10-25 09:58 0 0.48 2006-10-26 01:47 2006-10-26 01:47 0 0.66 2006-10-26 10:45 2006-10-26 10:45 0 0.37 2006-10-27 09:28 2006-10-27 09:28 0 0.95 2006-10-27 16:50 2006-10-27 16:50 0 0.31 2006-10-29 01:18 2006-10-29 01:18 0 1.35 2006-10-29 19:07 2006-10-29 19:07 0 0.74 2006-10-30 01:40 2006-10-30 01:40 0 0.27 2006-10-30 10:35 2006-10-30 10:35 0 0.37 2006-10-31 16:10 2006-10-31 16:10 0 1.23 2006-10-31 23:11 2006-10-31 23:11 0 0.29 2006-11-01 22:12 2006-11-01 22:12 0 0.96 2006-11-02 09:18 2006-11-02 09:18 0 0.46 2006-11-02 14:52 2006-11-02 14:52 0 0.23 2006-11-02 17:47 2006-11-02 17:47 0 0.12 2006-11-03 09:38 2006-11-03 09:38 0 0.66 2006-11-03 15:07 2006-11-03 15:07 0 0.23 2006-11-03 16:38 2006-11-03 16:38 0 0.06 2006-11-03 17:07 2006-11-03 17:07 0 0.02 2006-11-03 17:30 2006-11-03 17:31 1.3 0.02 2006-11-03 23:56 2006-11-03 23:56 0 0.27 0.14 175 rows selected