外部表的ROWID信息

SQL> CREATE directory d_alert AS 'D:\ORACLE\PRODUCT\ADMIN\YTK102\BDUMP';
目录已创建。
SQL> CREATE TABLE t_alert
  2  (text varchar2(1000)
  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'));
表已创建。
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
已选择9行。
SQL> SELECT ROWID FROM T_ALERT WHERE ROWNUM < 10;
ROWID
-----------------------
(AADRCQAAAAAAAAAAAAAAAA
(AADRCQAAAAAAAAAAAAAAQQ
(AADRCQAAAAAAAAAAAAAAXA
(AADRCQAAAAAAAAAAAAAAhg
(AADRCQAAAAAAAAAAAAAAmg
(AADRCQAAAAAAAAAAAAAAtA
(AADRCQAAAAAAAAAAAAAA6g
(AADRCQAAAAAAAAAAAAABDA
(AADRCQAAAAAAAAAAAAABVg
已选择9行。

SQL> create directory d_alert as 'D:\ORACLE\PRODUCT\ADMIN\YTK102\BDUMP'; 目录已创建。 SQL> create table t_alert 2 (text varchar2(1000) 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')); 表已创建。 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 已选择9行。 SQL> SELECT ROWID FROM T_ALERT WHERE ROWNUM < 10; ROWID ----------------------- (AADRCQAAAAAAAAAAAAAAAA (AADRCQAAAAAAAAAAAAAAQQ (AADRCQAAAAAAAAAAAAAAXA (AADRCQAAAAAAAAAAAAAAhg (AADRCQAAAAAAAAAAAAAAmg (AADRCQAAAAAAAAAAAAAAtA (AADRCQAAAAAAAAAAAAAA6g (AADRCQAAAAAAAAAAAAABDA (AADRCQAAAAAAAAAAAAABVg 已选择9行。

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 * 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行。