external table 外部表学习

外部表作为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

 

相关文章

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据