When you create the ORACLE_DATAPUMP access driver external table, you can specify certain parameters in an access_parameters clause.

This clause is optional, as are its individual parameters. For example, you can specify LOGFILE, but not VERSION, or vice versa. The syntax for the access_parameters clause is as follows.

Note:

These access parameters are collectively referred to as the opaque_format_spec in the SQL CREATE TABLE...ORGANIZATION EXTERNAL statement.

Description of the illustration et_oracle_datapump.eps

• comments
  
• COMPRESSION
  
• ENCRYPTION
  
• LOGFILE | NOLOGFILE
  
• VERSION Clause
  The VERSION clause is used to specify the minimum release of Oracle Database that will be reading the dump file.
• Effects of Using the SQL ENCRYPT Clause
  Review the requirements and guidelines for external tables when you encrypt columns using the ORACLE_DATAPUMP access driver ENCRYPT clause.

As part of creating an external table with a SQL CREATE TABLE AS SELECT statement, the ORACLE_DATAPUMP access driver can write data to a dump file.

1. An external table will populate a file with data only as part of creating the external table with the AS SELECT clause. The following example creates an external table named inventories_xt and populates the dump file for the external table with the data from table inventories in the oe schema.

   SQL> CREATE TABLE inventories_xt
     2  ORGANIZATION EXTERNAL
     3  (
     4    TYPE ORACLE_DATAPUMP
     5    DEFAULT DIRECTORY def_dir1
     6    LOCATION ('inv_xt.dmp')
     7  )
     8  AS SELECT * FROM inventories;
   
   Table created.
   

2. Describe both inventories and the new external table, as follows. They should both match.

   SQL> DESCRIBE inventories
    Name                                      Null?    Type
    ---------------------------------------- --------- ----------------
    PRODUCT_ID                                NOT NULL NUMBER(6)
    WAREHOUSE_ID                              NOT NULL NUMBER(3)
    QUANTITY_ON_HAND                          NOT NULL NUMBER(8)
   
   SQL> DESCRIBE inventories_xt
    Name                                      Null?    Type
    ----------------------------------------- -------- -----------------
    PRODUCT_ID                                NOT NULL NUMBER(6)
    WAREHOUSE_ID                              NOT NULL NUMBER(3)
    QUANTITY_ON_HAND                          NOT NULL NUMBER(8)
   

3. Now that the external table is created, it can be queried just like any other table. For example, select the count of records in the external table, as follows:

   SQL> SELECT COUNT(*) FROM inventories_xt;
   
     COUNT(*)
   ----------
         1112
   

4. Compare the data in the external table against the data in inventories. There should be no differences.

   SQL> SELECT * FROM inventories MINUS SELECT * FROM inventories_xt;
   
   no rows selected
   

5. After an external table has been created and the dump file populated by the CREATE TABLE AS SELECT statement, no rows may be added, updated, or deleted from the external table. Any attempt to modify the data in the external table will fail with an error.

   The following example shows an attempt to use data manipulation language (DML) on an existing external table. This will return an error, as shown.

   SQL> DELETE FROM inventories_xt WHERE warehouse_id = 5;
   DELETE FROM inventories_xt WHERE warehouse_id = 5
               *
   ERROR at line 1:
   ORA-30657: operation not supported on external organized table
   

6. The dump file created for the external table can now be moved and used as the dump file for another external table in the same database or different database. Note that when you create an external table that uses an existing file, there is no AS SELECT clause for the CREATE TABLE statement.

   SQL> CREATE TABLE inventories_xt2
     2  (
     3    product_id          NUMBER(6),
     4    warehouse_id        NUMBER(3),
     5    quantity_on_hand    NUMBER(8)
     6  )
     7  ORGANIZATION EXTERNAL
     8  (
     9    TYPE ORACLE_DATAPUMP
    10    DEFAULT DIRECTORY def_dir1
    11    LOCATION ('inv_xt.dmp')
    12  );
   
   Table created.
   

7. Compare the data for the new external table against the data in the inventories table. The product_id field will be converted to a compatible data type before the comparison is done. There should be no differences.

   SQL> SELECT * FROM inventories MINUS SELECT * FROM inventories_xt2;
   
   no rows selected
   

8. Create an external table with three dump files and with a degree of parallelism of three.

   SQL> CREATE TABLE inventories_xt3
     2  ORGANIZATION EXTERNAL
     3  (
     4    TYPE ORACLE_DATAPUMP
     5    DEFAULT DIRECTORY def_dir1
     6    LOCATION ('inv_xt1.dmp', 'inv_xt2.dmp', 'inv_xt3.dmp')
     7  )
     8  PARALLEL 3
     9  AS SELECT * FROM inventories;
   
   Table created.
   

9. Compare the data unload against inventories. There should be no differences.

   SQL> SELECT * FROM inventories MINUS SELECT * FROM inventories_xt3;
   
   no rows selected
   

10. Create an external table containing some rows from table inventories.

    SQL> CREATE TABLE inv_part_xt
      2  ORGANIZATION EXTERNAL
      3  (
      4  TYPE ORACLE_DATAPUMP
      5  DEFAULT DIRECTORY def_dir1
      6  LOCATION ('inv_p1_xt.dmp')
      7  )
      8  AS SELECT * FROM inventories WHERE warehouse_id < 5;
     
    Table created.
    

11. Create another external table containing the rest of the rows from inventories.

    SQL> drop table inv_part_xt;
     
    Table dropped.
     
    SQL> 
    SQL> CREATE TABLE inv_part_xt
      2  ORGANIZATION EXTERNAL
      3  (
      4  TYPE ORACLE_DATAPUMP
      5  DEFAULT DIRECTORY def_dir1
      6  LOCATION ('inv_p2_xt.dmp')
      7  )
      8  AS SELECT * FROM inventories WHERE warehouse_id >= 5;
     
    Table created.
    

12. Create an external table that uses the two dump files created in Steps 10 and 11.

    SQL> CREATE TABLE inv_part_all_xt
      2  (
      3  product_id NUMBER(6),
      4  warehouse_id NUMBER(3),
      5  quantity_on_hand NUMBER(8)
      6  )
      7  ORGANIZATION EXTERNAL
      8  (
      9  TYPE ORACLE_DATAPUMP
     10  DEFAULT DIRECTORY def_dir1
     11  LOCATION ('inv_p1_xt.dmp','inv_p2_xt.dmp')
     12  );
     
    Table created.
    

13. Compare the new external table to the inventories table. There should be no differences. This is because the two dump files used to create the external table have the same metadata (for example, the same table name inv_part_xt and the same column information).

    SQL> SELECT * FROM inventories MINUS SELECT * FROM inv_part_all_xt;
    
    no rows selected

• Parallel Loading and Unloading
  This topic describes parallel loading and unloading.
• Combining Dump Files
  Dump files populated by different external tables can all be specified in the LOCATION clause of another external table.

The ORACLE_DATAPUMP access driver resolves many data types automatically during loads and unloads.

When you use external tables to move data between databases, you may encounter the following situations:

• The database character set and the database national character set may be different between the two platforms.
• The endianness of the platforms for the two databases may be different.

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. This is demonstrated in the following example, in which the unsupported data type, LONG, is used:

SQL> CREATE TABLE bad_datatype_xt
  2  (
  3    product_id             NUMBER(6),
  4    language_id            VARCHAR2(3),
  5    translated_name        NVARCHAR2(50),
  6    translated_description LONG
  7  )
  8  ORGANIZATION EXTERNAL
  9  (
 10    TYPE ORACLE_DATAPUMP
 11    DEFAULT DIRECTORY def_dir1
 12    LOCATION ('proddesc.dmp')
 13  );
  translated_description LONG
  *
ERROR at line 6:
ORA-30656: column type not supported on external organized table

You can use the ORACLE_DATAPUMP access driver to unload and reload data for some of the unsupported data types

An external table supports a subset of all possible data types for columns. 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 unsupported data types for which you can use the ORACLE_DATAPUMP access driver to unload and reload data include the following:

• BFILE
• LONG and LONG RAW
• Final object types
• Tables of final object types

• Unloading and Loading BFILE Data Types
  The BFILE data type has two pieces of information stored in it: the directory object for the file and the name of the file within that directory object.
• Unloading LONG and LONG RAW Data Types
  The ORACLE_DATAPUMP access driver can be used to unload LONG and LONG RAW columns, but that data can only be loaded back into LOB fields.
• Unloading and Loading Columns Containing Final Object Types
  Final column objects are populated into an external table by moving each attribute in the object type into a column in the external table.
• Tables of Final Object Types
  Object tables have an object identifier that uniquely identifies every row in the table.

Learn how to improve ORACLE_DATAPUMP access driver performance.

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.

Be aware of restrictions that apply to accessing external tables with the ORACLE_DATAPUMP access driver.

The restrictions that apply to using the ORACLE_DATAPUMP access driver with external tables includes the following:

• Encrypted columns: Exporting and importing of external tables with encrypted columns is not supported.
• 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.
• LONG columns: An external table cannot load data into a LONG column.
• 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.
• Backslash escape characters: The external tables feature does not support the use of the backslash (\) escape character within strings.
• Reserved words: 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.

If you use words in identifiers that are reserved by the ORACLE_DATAPUMP access driver, then they must be enclosed in double quotation marks.

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