[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.
--放弃,应该表示某种偏移量，具体细节实在太难猜测。