The ORACLE_DATAPUMP Access Driver

1. access_parameters Clause

1.1 comments

--This is a comment.

1.2 COMPRESSION

默认为 DISABLED，表示是否在数据写入dump file set前是否进行压缩

COMPRESSION [ENABLED {BASIC | LOW| MEDIUM | HIGH} | DISABLED]

If ENABLED is specified, then all data is compressed for the entire unload operation. To use compression algorithms, the COMPATIBLE initialization parameter must be set to at least 12.0.0. This feature requires that the Oracle Advanced Compression option be enabled.

CREATE TABLE deptXTec3

 ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY def_dir1

ACCESS PARAMETERS (COMPRESSION ENABLED)

LOCATION ('dept.dmp'));

1.3 ENCRYPTION

默认 DISABLED，是否加密后再写入dump file set

This parameter is used only for export operations.

CREATE TABLE deptXTec3

 ORGANIZATION EXTERNAL

(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY def_dir1

 ACCESS PARAMETERS (ENCRYPTION ENABLED)

 LOCATION ('dept.dmp'));

If you specify the SQL ENCRYPT clause when you create an external table, then keep the following in mind:

1. The columns for which you specify the ENCRYPT clause will be encrypted before being written into the dump file.
2. If you move the dump file to another database, then the same encryption password must be used for both the encrypted columns in the dump file and for the external table used to read the dump file
3. If you do not specify a password for the correct encrypted columns in the external table on the second database, then an error is returned. If you do not specify the correct password, then garbage data is written to the dump file.
4. The dump file that is produced must be at release 10.2 or higher. Otherwise, an error is returned.

1.4 LOGFILE | NOLOGFILE

If LOGFILE is not specified, then a log file is created in the default directory and the name of the log file is generated from the table name and the process ID with an extension of .log. If a log file already exists by the same name, then the access driver reopens that log file and appends the new log information to the end.

LOGFILE [directory_object:]logfile_name

The access driver does some symbol substitution to help make file names unique in the case of parallel loads. The symbol substitutions supported are as follows:

1. %p is replaced by the process ID of the current process. For example, if the process ID of the access driver is 12345, then exttab_%p.log becomes exttab_12345.log.
2. %a is replaced by the agent number of the current process. The agent number is the unique number assigned to each parallel process accessing the external table. This number is padded to the left with zeros to fill three characters. For example, if the third parallel agent is creating a file and exttab_%a.log was specified as the file name, then the agent would create a file named exttab_003.log.
3. %% is replaced by %. If there is a need to have a percent sign in the file name, then this symbol substitution must be used.

If the % character is followed by anything other than one of the characters in the preceding list, then an error is returned.

If %p or %a is not used to create unique file names for output files and an external table is being accessed in parallel, then output files may be corrupted or agents may be unable to write to the files.

If no extension is supplied for the file, then a default extension of .log is used. If the name generated is not a valid file name, then an error is returned and no data is loaded or unloaded.

1.5 VERSION Clause

读取高版本的dumpfile要使用version，默认为COMPATIBLE值

1. Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver

ORACLE_DATAPUMP access driver can write data to a dump file. The data in the file is written in a binary format that can only be read by the ORACLE_DATAPUMP access driver. Once the dump file is created, it cannot be modified (that is, no data manipulation language (DML) operations can be performed on it).

下面会创建外部表inventories_xt，同时在目录中创建dumpfile:inv_xt.dmp，在创建时它通过as select 把inventories表导出到了这个文件里

CREATE TABLE inventories_xt

 ORGANIZATION EXTERNAL

 (

 TYPE ORACLE_DATAPUMP

 DEFAULT DIRECTORY def_dir1

 LOCATION ('inv_xt.dmp')

 )

 AS SELECT * FROM inventories; 

你可能通过已存在的dumpfile来创建其它外部表：

CREATE TABLE inventories_xt2

    (

      product_id          NUMBER(6),

      warehouse_id        NUMBER(3),

      quantity_on_hand    NUMBER(8)

    )

    ORGANIZATION EXTERNAL

    (

      TYPE ORACLE_DATAPUMP

     DEFAULT DIRECTORY def_dir1

     LOCATION ('inv_xt.dmp')

   );

2.1 Parallel Loading and Unloading

一般要求文件数要大于parallel数，但即使只指定单个文件，也可以并行:

When the ORACLE_DATAPUMP access driver is used to load data, parallel processes can read multiple dump files or even chunks of the same dump file concurrently.

Thus, data can be loaded in parallel even if there is only one dump file, as long as that file is large enough to contain multiple file offsets. The degree of parallelization is not tied to the number of files in the LOCATION clause when reading from ORACLE_DATAPUMP external tables.

CREATE TABLE inventories_xt3

    ORGANIZATION EXTERNAL

    (

      TYPE ORACLE_DATAPUMP

      DEFAULT DIRECTORY def_dir1

      LOCATION ('inv_xt1.dmp', 'inv_xt2.dmp', 'inv_xt3.dmp')

    )

    PARALLEL 3

    AS SELECT * FROM inventories;

2.2 Combining Dump Files

data from different production databases can be unloaded into separate files, and then those files can all be included in an external table defined in a data warehouse. This provides an easy way of aggregating data from multiple sources.

The only restriction is that the metadata for all of the external tables be exactly the same. This means that the character set, time zone, schema name, table name, and column names must all match. Also, the columns must be defined in the same order, and their data types must be exactly alike.

注：如果原外部表对应一个dumpfile，想添加其它dumpfile，只能重建外部表

SQL> CREATE TABLE inv_part_1_xt

    ORGANIZATION EXTERNAL

    (

      TYPE ORACLE_DATAPUMP

      DEFAULT DIRECTORY def_dir1

      LOCATION ('inv_p1_xt.dmp')

    )

    AS SELECT * FROM oe.inventories WHERE warehouse_id < 5;

SQL> DROP TABLE inv_part_1_xt;

SQL> CREATE TABLE inv_part_1_xt

    ORGANIZATION EXTERNAL

    (

      TYPE ORACLE_DATAPUMP

      DEFAULT directory def_dir1

      LOCATION ('inv_p2_xt.dmp')

    )

    AS SELECT * FROM oe.inventories WHERE warehouse_id >= 5;

SQL> CREATE TABLE inv_part_all_xt

    (

      PRODUCT_ID          NUMBER(6),

      WAREHOUSE_ID        NUMBER(3),

      QUANTITY_ON_HAND    NUMBER(8)

    )

    ORGANIZATION EXTERNAL

    (

      TYPE ORACLE_DATAPUMP

     DEFAULT DIRECTORY def_dir1

     LOCATION ('inv_p1_xt.dmp','inv_p2_xt.dmp')

   );

1. Supported Data Types

In particular, it supports character data types (except LONG), the RAW data type, all numeric data types, and all date, timestamp, and interval data types.

The ORACLE_DATAPUMP access driver automatically resolves some of these situations.

The following data types are automatically converted during loads and unloads:

Character (CHAR, NCHAR, VARCHAR2, NVARCHAR2), RAW, NUMBER, Date/Time, BLOB, CLOB and NCLOB, ROWID and UROWID

If you attempt to use a data type that is not supported for external tables, then you receive an error.

1. Unsupported Data Types   --用时查

This section describes how you can use the ORACLE_DATAPUMP access driver to unload and reload data for some of the unsupported data types, specifically: BFILE, LONG and LONG RAW, Final, object types, Tables of final object types

1. Performance Hints When Using the ORACLE_DATAPUMP Access Driver

When you monitor performance, the most important measurement is the elapsed time for a load. Other important measurements are CPU usage, memory usage, and I/O rates.

You can alter performance by increasing or decreasing the degree of parallelism. The degree of parallelism indicates the number of access drivers that can be started to process the data files. The degree of parallelism enables you to choose on a scale between slower load with little resource usage and faster load with all resources utilized. The access driver cannot automatically tune itself, because it cannot determine how many resources you want to dedicate to the access driver.

An additional consideration is that the access drivers use large I/O buffers for better performance. On databases with shared servers, all memory used by the access drivers comes out of the system global area (SGA). For this reason, you should be careful when using external tables on shared servers.

1. Restrictions When Using the ORACLE_DATAPUMP Access Driver

The ORACLE_DATAPUMP access driver has the following restrictions:

1. Exporting and importing of external tables with encrypted columns is not supported.
2. Column processing: By default, the external tables feature fetches all columns defined for an external table. This guarantees a consistent result set for all queries. However, for performance reasons you can decide to process only the referenced columns of an external table, thus minimizing the amount of data conversion and data handling required to execute a query. In this case, a row that is rejected because a column in the row causes a data type conversion error will not get rejected in a different query if the query does not reference that column. You can change this column-processing behavior with the ALTER TABLE command.
3. An external table cannot load data into a LONG column.
4. Handling of byte-order marks during a load: In an external table load for which the data file character set is UTF8 or UTF16, it is not possible to suppress checking for byte-order marks. Suppression of byte-order mark checking is necessary only if the beginning of the data file contains binary data that matches the byte-order mark encoding. (It is possible to suppress byte-order mark checking with SQL*Loader loads.) Note that checking for a byte-order mark does not mean that a byte-order mark must be present in the data file. If no byte-order mark is present, then the byte order of the server platform is used.
5. The external tables feature does not support the use of the backslash (\) escape character within strings.
6. When identifiers (for example, column or table names) are specified in the external table access parameters, certain values are considered to be reserved words by the access parameter parser. If a reserved word is used as an identifier, then it must be enclosed in double quotation marks.

16.7 Reserved Words for the ORACLE_DATAPUMP Access Driver

When identifiers (for example, column or table names) are specified in the external table access parameters, certain values are considered to be reserved words by the access parameter parser. If a reserved word is used as an identifier, then it must be enclosed in double quotation marks. The following are the reserved words for the ORACLE_DATAPUMP access driver:

BADFILE, COMPATIBLE, COMPRESSION, DATAPUMP, DEBUG, ENCRYPTION, INTERNAL, JOB, LATEST, LOGFILE, NOBADFILE, NOLOGFILE, PARALLEL, TABLE, VERSION, WORKERID,