Oracle's external tables can also be used to unload data __oracle

Starting with 10.2, ORACLE added a new access driver for the external table: Oracle_datapump. And again before, there is only one default driver Oracle_loader.

One advantage of using the Oracle_datapump driver is that external tables can be used to unload data from a database into a disk file that is not available in 10.1 and previous versions.

Here's a simple example:

sql> CREATE TABLE t_external
2 (
3 ID,
4 NAME
5)
6 Organization EXTERNAL
7 (
8 TYPE Oracle_datapump
9 DEFAULT DIRECTORY D_output
LOCATION (' TEST. DMP ')
11)
As SELECT rownum RN, Tname
From TAB;
　　


Table has been created.
At the same time as this external table was created, a test.dmp binary file was generated under the operating system directory that d_output pointed to.
Display the displayed characters in this binary file:


$ 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 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


This binary file can be used to create external tables in other databases:

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
LOCATION (' TEST. DMP ')
11);


Table has been created.

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
Ten T_char
One sys_export_schema_01
T_external
T_load_space
T_level
T_level1
Chained_rows
T_tree


17 rows have been selected.

The only pity is that Oracle can only write data in binary format, so consider using this method for data migration or publishing. However, it is not feasible to use this method to generate text reports.

Finally, the external table is a read-only table that can unload data using an external table, but it must be done when the table is built, and the outer table becomes immutable once the table is successfully established.

Sql> INSERT into t_external VALUES (+, ' A ');
INSERT into T_external VALUES (' A ')


* Line 1th Error:

ORA-30657: Operations are not supported on external organization tables

sql> DROP TABLE t_external;

The table has been deleted.

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
LOCATION (' TEST1. DMP ')
11);


Table has been created.

Sql> INSERT into T_external VALUES (1, ' A ');
INSERT into T_external VALUES (1, ' A ')


* Line 1th Error:

ORA-30657: Operations are not supported on external organization tables