这个例子是在本机上运行，因此只是告警日志的名称发生了变化，其他的语法和上一篇文章中的一致，当然D_ALERT目录需要指向background_dump_dest参数指定的路径：

SQL> CREATE TABLE T_ALERT
  2  (TEXT VARCHAR2(4000)
  3  )
  4  ORGANIZATION EXTERNAL
  5  (TYPE ORACLE_LOADER
  6  DEFAULT DIRECTORY D_ALERT
  7  ACCESS PARAMETERS
  8  (RECORDS DELIMITED BY NEWLINE
  9  FIELDS (TEXT (1:255) CHAR))
 10  LOCATION (' alert_ytk102.log'));

Table created.

SQL> SELECT * FROM T_ALERT WHERE ROWNUM <= 10;

TEXT
--------------------------------------------------------------------------
Dump file d:\oracle\product\admin\ytk102\bdump\alert_ytk102.log
Sat Dec 25 15:55:43 2010
ORACLE V10.2.0.5.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows NT Version V6.1
CPU : 4 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1594M/2995M, Ph+PgF:4318M/5989M, VA:1911M/2047M
Sat Dec 25 15:55:43 2010
Starting ORACLE instance (normal)

已选择10行。

如果我们用ROW_NUMBER() OVER()分析函数来代替ROWNUM伪列，则上一篇提到的错误不再会出现，而且两种写法都可以得到正确的结果。

需要注意的是，ROW_NUMBER() OVER()分析函数需要一个ORDER BY列，而在这个例子中，没有明确的排序列，是根据读取顺序的，因此这里选择了ROWID进行排序：

SQL> WITH A AS (SELECT ROW_NUMBER() OVER(ORDER BY ROWID) RN, TEXT FROM T_ALERT)
  2  SELECT * FROM A B
  3  WHERE B.RN >= (SELECT MIN(C.RN) FROM A C WHERE TEXT = 'Mon Sep 19 07:18:21  2011');

        RN TEXT
---------- --------------------------------------------------------------------------------
      5739 Mon Sep 19 07:18:21  2011
      5740 Successfully onlined Undo Tablespace 1.
      5741 Mon Sep 19 07:18:21  2011
      5742 SMON: enabling tx recovery
      5743 Mon Sep 19 07:18:21  2011
      5744 Database Characterset is ZHS16GBK
      5745 Opening with internal Resource Manager plan
      5746 replication_dependency_tracking turned off (no async multimaster replication found)
      5747 Starting background process QMNC
      5748 QMNC started with pid=17, OS id=5412
      5749 Mon Sep 19 07:18:29  2011
      5750 Completed: alter database open

已选择12行。

SQL> WITH A AS (SELECT ROW_NUMBER() OVER(ORDER BY ROWID) RN, TEXT FROM T_ALERT)
  2  SELECT B.*
  3  FROM A B,
  4   (SELECT MIN(RN) RN FROM A WHERE TEXT = 'Mon Sep 19 07:18:21  2011') C
  5  WHERE B.RN >= C.RN;

        RN TEXT
---------- --------------------------------------------------------------------------------
      5739 Mon Sep 19 07:18:21  2011
      5740 Successfully onlined Undo Tablespace 1.
      5741 Mon Sep 19 07:18:21  2011
      5742 SMON: enabling tx recovery
      5743 Mon Sep 19 07:18:21  2011
      5744 Database Characterset is ZHS16GBK
      5745 Opening with internal Resource Manager plan
      5746 replication_dependency_tracking turned off (no async multimaster replication found)
      5747 Starting background process QMNC
      5748 QMNC started with pid=17, OS id=5412
      5749 Mon Sep 19 07:18:29  2011
      5750 Completed: alter database open

已选择12行。

SQL> WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)
  2  SELECT B.*
  3  FROM A B,
  4   (SELECT MIN(RN) RN FROM A WHERE TEXT = 'Mon Sep 19 07:18:21  2011') C
  5  WHERE B.RN >= C.RN;

        RN TEXT
---------- --------------------------------------------------------------------------------
      5739 Mon Sep 19 07:18:21  2011
      5740 Successfully onlined Undo Tablespace 1.
      5741 Mon Sep 19 07:18:21  2011
      5742 SMON: enabling tx recovery
      5743 Mon Sep 19 07:18:21  2011
      5744 Database Characterset is ZHS16GBK
      5745 Opening with internal Resource Manager plan
      5746 replication_dependency_tracking turned off (no async multimaster replication found)
      5747 Starting background process QMNC
      5748 QMNC started with pid=17, OS id=5412
      5749 Mon Sep 19 07:18:29  2011
      5750 Completed: alter database open

已选择12行。

同时还修改了上一篇文章SQL的一个小bug，解决了ALERT读取中指定时间出现重复的问题。