今天在看Oracle的CONCEPT文档时发现,Oracle的外部表也可以用来卸载数据了。
从10.2中开始,Oracle增加了外部表的一个新的访问驱动:ORACLE_DATAPUMP。而再次之前,只有一个默认的驱动ORACLE_LOADER。
使用ORACLE_DATAPUMP驱动,带来的一个优点是,可以利用外部表将数据库中的数据卸载到磁盘文件中,而这是10.1及以前版本所无法做到的。
下面看一个最简单的例子:
SQL> CREATE TABLE T_EXTERNAL
2 (
3 ID,
4 NAME
5 )
6 ORGANIZATION EXTERNAL
7 (
8 TYPE ORACLE_DATAPUMP
9 DEFAULT DIRECTORY D_OUTPUT
10 LOCATION('TEST.DMP')
11 )
12 AS SELECT ROWNUM RN, TNAME
13 FROM TAB;
表已创建。
在这张外部表创建的同时,在D_OUTPUT所指向的操作系统目录下,生成了一个TEST.DMP二进制文件。
显示这个二进制文件中的可显示字符:
$ strings TEST.DMP
IBMPC/WIN_NT-8.1.0
ZHS16GBK
LBB EMB GHC JWD SD EBE WMF DDG JG SJH SRH JGK CL EGM BJM RAP RLP RP KR PAR MS MRS JLS CET HLT
10.02.00.01.00
T
1
0
3
0
ZHS16GBK
AL16UTF16
+00:00
YANGTK
T_EXTERNAL
1
0
ID
2
22
0
-127
0
0
0
2
0
NAME
1
30
0
0
852
1
30
T_LOG<
TEST_LOB<
T_COMPRESS<
T_TEMP2<
T_RECORD<
T_SESSION_STAT<
T_TEMP<
T_NO_EXISTS_BEFORE<
T_CHAR<
SYS_EXPORT_SCHEMA_01<
T_EXTERNAL<
T_LOAD_SPACE<
T_LEVEL<
T_LEVEL1<
CHAINED_ROWS<
T_TREE
这个二进制文件可以用于在其他数据库中创建外部表:
SQL> CREATE TABLE T_EXTERNAL
2 (
3 ID NUMBER,
4 NAME VARCHAR2(30)
5 )
6 ORGANIZATION EXTERNAL
7 (
8 TYPE ORACLE_DATAPUMP
9 DEFAULT DIRECTORY D_OUTPUT
10 LOCATION('TEST.DMP')
11 );
表已创建。
SQL> SELECT * FROM T_EXTERNAL;
ID NAME
---------- ------------------------------
1 T_LOG
2 TEST_LOB
3 T_COMPRESS
4 T_TEMP2
5 T_RECORD
6 T_SESSION_STAT
7 T_TEMP
8 T
9 T_NO_EXISTS_BEFORE
10 T_CHAR
11 SYS_EXPORT_SCHEMA_01
12 T_EXTERNAL
13 T_LOAD_SPACE
14 T_LEVEL
15 T_LEVEL1
16 CHAINED_ROWS
17 T_TREE
已选择17行。
唯一可惜的是Oracle只能将数据写为二进制格式,因此可以考虑使用这种方法进行数据迁移或发布。不过想利用这种方法来生成文本报表也是行不通的。
最后提一下,外部表是只读表,可以利用外部表卸载数据,不过必须在建表时完成,一旦表建立成功,外部表就变成不可修改了。
SQL> INSERT INTO T_EXTERNAL VALUES (100, 'A');
INSERT INTO T_EXTERNAL VALUES (100, 'A')
*第1行出现错误:
ORA-30657:操作在外部组织表上不受支持
SQL> DROP TABLE T_EXTERNAL;
表已删除。
SQL> CREATE TABLE T_EXTERNAL
2 (
3 ID NUMBER,
4 NAME VARCHAR2(30)
5 )
6 ORGANIZATION EXTERNAL
7 (
8 TYPE ORACLE_DATAPUMP
9 DEFAULT DIRECTORY D_OUTPUT
10 LOCATION('TEST1.DMP')
11 );
表已创建。
SQL> INSERT INTO T_EXTERNAL VALUES (1, 'A');
INSERT INTO T_EXTERNAL VALUES (1, 'A')
*第1行出现错误:
ORA-30657:操作在外部组织表上不受支持