[20150707]外部表与rowid.txt

SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

CREATE OR REPLACE DIRECTORY
TRACE AS
'/u01/app/oracle11g/diag/rdbms/test/test/trace/';

GRANT EXECUTE, READ, WRITE ON DIRECTORY TRACE TO SCOTT WITH GRANT OPTION;

CREATE TABLE ALERT_LOG (text_line VARCHAR2 (512))
ORGANIZATION EXTERNAL
   (TYPE ORACLE_LOADER
         DEFAULT DIRECTORY TRACE
            ACCESS PARAMETERS
               (
            RECORDS DELIMITED BY NEWLINE
            NOBADFILE
            NODISCARDFILE
            NOLOGFILE
    )
         LOCATION ('alert_test.log'));

SCOTT@test> select rowid,alert_log.text_line c100 from ALERT_LOG where rownumROWID                   C100
----------------------- ----------------------------------------------------------------------------------------------------
(AASw6QAAAAAAAAAAAAAAAA Mon May 04 15:22:19 2015
(AASw6QAAAAAAAAAAAAAAGQ Error 12170 received logging on to the standby
(AASw6QAAAAAAAAAAAAAASA PING[ARC2]: Heartbeat failed to connect to standby 'testdg'. Error is 12170.
(AASw6QAAAAAAAAAAAAAAlQ Mon May 04 15:22:35 2015
(AASw6QAAAAAAAAAAAAAArg ALTER SYSTEM ARCHIVE LOG
(AASw6QAAAAAAAAAAAAAAxw Mon May 04 15:22:35 2015
(AASw6QAAAAAAAAAAAAAA4A Beginning log switch checkpoint up to RBA [0xa8f.2.10]
(AASw6QAAAAAAAAAAAAABKQ Thread 1 advanced to log sequence 2703 (LGWR switch)
(AASw6QAAAAAAAAAAAAABXg   Current log# 1 seq# 2703 mem# 0: /u01/app/oracle11g/oradata/test/redo01.log
(AASw6QAAAAAAAAAAAAABrA Mon May 04 15:22:35 2015

10 rows selected.)

--可以发现外部表也有rowid，但是明显与实际的rowid不同。
SCOTT@test> select rowid,dept.* from dept where rownum=1;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AABJVUAAEAAAAdzAAA         10 ACCOUNTING     NEW YORK

SCOTT@test> @ lookup_rowid (AASw6QAAAAAAAAAAAAAAAA
SELECT DBMS_ROWID.ROWID_OBJECT ('(AASw6QAAAAAAAAAAAAAAAA') "OBJECT",
       *
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at "SYS.DBMS_ROWID", line 82

SCOTT@test> @ lookup_rowid AASw6QAAAAAAAAAAAAAAAA
SELECT DBMS_ROWID.ROWID_OBJECT ('AASw6QAAAAAAAAAAAAAAAA') "OBJECT",
       *
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at "SYS.DBMS_ROWID", line 82

SCOTT@test> select text_line c100  from alert_log where rowid='(AASw6QAAAAAAAAAAAAAAAA';
C100
----------------------------------------------------------------------------------------------------
Mon May 04 15:22:19 2015

--我插入1行空行。

SCOTT@test> select rowid,alert_log.text_line c100 from ALERT_LOG where rownumROWID                   C100
----------------------- ----------------------------------------------------------------------------------------------------
(AASw6QAAAAAAAAAAAAAAAA
(AASw6QAAAAAAAAAAAAAAAQ Mon May 04 15:22:19 2015
(AASw6QAAAAAAAAAAAAAAGg Error 12170 received logging on to the standby
(AASw6QAAAAAAAAAAAAAASQ PING[ARC2]: Heartbeat failed to connect to standby 'testdg'. Error is 12170.
(AASw6QAAAAAAAAAAAAAAlg Mon May 04 15:22:35 2015
(AASw6QAAAAAAAAAAAAAArw ALTER SYSTEM ARCHIVE LOG
(AASw6QAAAAAAAAAAAAAAyA Mon May 04 15:22:35 2015
(AASw6QAAAAAAAAAAAAAA4Q Beginning log switch checkpoint up to RBA [0xa8f.2.10]
(AASw6QAAAAAAAAAAAAABKg Thread 1 advanced to log sequence 2703 (LGWR switch)
(AASw6QAAAAAAAAAAAAABXw   Current log# 1 seq# 2703 mem# 0: /u01/app/oracle11g/oradata/test/redo01.log
(AASw6QAAAAAAAAAAAAABrQ Mon May 04 15:22:35 2015

11 rows selected.

--我感觉rowid应该表示某种偏移量，具体细节很难猜透，至少我的能力不行.。

--建立一个aaa.txt文件在相应目录。
$ nl aaa.txt
     1  12334567890
     2  22334567890
     3  32334567890
     4  42334567890
     5  52334567890
     6  62334567890
     7  72334567890
     8  82334567890
     9  92334567890

SCOTT@test>  alter table ALERT_LOG location ('aaa.txt');
Table altered.

SCOTT@test> select rowid,text_line c100  from alert_log ;
ROWID                   C100
----------------------- ------------
(AASw6QAAAAAAAAAAAAAAAA 12334567890
(AASw6QAAAAAAAAAAAAAADA 22334567890
(AASw6QAAAAAAAAAAAAAAGA 32334567890
(AASw6QAAAAAAAAAAAAAAJA 42334567890
(AASw6QAAAAAAAAAAAAAAMA 52334567890
(AASw6QAAAAAAAAAAAAAAPA 62334567890
(AASw6QAAAAAAAAAAAAAASA 72334567890
(AASw6QAAAAAAAAAAAAAAVA 82334567890
(AASw6QAAAAAAAAAAAAAAYA 92334567890
9 rows selected.

--ABC DEF GHI JKL MNO PQR STU VWX YZ,视乎存在某种规律。

$ nl aaa.txt
     1  12334567890a
     2  22334567890
     3  32334567890
     4  42334567890
     5  52334567890
     6  62334567890
     7  72334567890
     8  82334567890
     9  92334567890

SCOTT@test> select rowid,text_line c100  from alert_log ;
ROWID                   C100
----------------------- ----------------------------------
(AASw6QAAAAAAAAAAAAAAAA 12334567890a
(AASw6QAAAAAAAAAAAAAADQ 22334567890
(AASw6QAAAAAAAAAAAAAAGQ 32334567890
(AASw6QAAAAAAAAAAAAAAJQ 42334567890
(AASw6QAAAAAAAAAAAAAAMQ 52334567890
(AASw6QAAAAAAAAAAAAAAPQ 62334567890
(AASw6QAAAAAAAAAAAAAASQ 72334567890
(AASw6QAAAAAAAAAAAAAAVQ 82334567890
(AASw6QAAAAAAAAAAAAAAYQ 92334567890
9 rows selected.

--放弃,应该表示某种偏移量，具体细节实在太难猜测。