• Log File Naming in Parallel Loads
  

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

• The columns for which you specify the ENCRYPT clause will be encrypted before being written into the dump file.

• 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.

• 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.

• The dump file that is produced must be at release 10.2 or higher. Otherwise, an error is returned.

The dump file must be on a disk big enough to hold all the data being written. If there is insufficient space for all of the data, then an error is returned for the CREATE TABLE AS SELECT statement. One way to alleviate the problem is to create multiple files in multiple directory objects (assuming those directories are on different disks) when executing the CREATE TABLE AS SELECT statement. Multiple files can be created by specifying multiple locations in the form directory:file in the LOCATION clause and by specifying the PARALLEL clause. Each parallel I/O server process that is created to populate the external table writes to its own file. The number of files in the LOCATION clause should match the degree of parallelization because each I/O server process requires its own files. Any extra files that are specified will be ignored. If there are not enough files for the degree of parallelization specified, then the degree of parallelization is lowered to match the number of files in the LOCATION clause.

Here is an example of unloading the inventories table into three files.

SQL> CREATE TABLE inventories_XT_3
  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 oe.inventories;

Table created.

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.

Dump files populated by different external tables can all be specified in the LOCATION clause of another external table. For example, 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. This means that after you create the first external table you must drop it so that you can use the same table name for the second external table. This ensures that the metadata listed in the two dump files is the same and they can be used together to create the same external table.

SQL> CREATE TABLE inv_part_1_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 oe.inventories WHERE warehouse_id < 5;

Table created.

SQL> DROP TABLE inv_part_1_xt;

SQL> CREATE TABLE inv_part_1_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 oe.inventories WHERE warehouse_id >= 5;

Table created.

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.

SQL> SELECT * FROM inv_part_all_xt MINUS SELECT * FROM oe.inventories;

no rows selected

1. Create a function to extract the directory object for a BFILE column. Note that if the column is NULL, then NULL is returned.

   SQL> CREATE FUNCTION get_dir_name (bf BFILE) RETURN VARCHAR2 IS
     2  DIR_ALIAS VARCHAR2(255);
     3  FILE_NAME VARCHAR2(255);
     4  BEGIN
     5    IF bf is NULL
     6    THEN
     7      RETURN NULL;
     8    ELSE
     9      DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
    10      RETURN dir_alias;
    11    END IF;
    12  END;
    13  /
   
   Function created.
   

2. Create a function to extract the file name for a BFILE column.

   SQL> CREATE FUNCTION get_file_name (bf BFILE) RETURN VARCHAR2 is
     2  dir_alias VARCHAR2(255);
     3  file_name VARCHAR2(255);
     4  BEGIN
     5    IF bf is NULL
     6    THEN
     7      RETURN NULL;
     8    ELSE
     9      DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
    10      RETURN file_name;
    11    END IF;
    12  END;
    13  /
   
   Function created.
   

3. You can then add a row with a NULL value for the BFILE column, as follows:

   SQL> INSERT INTO PRINT_MEDIA (product_id, ad_id, ad_graphic)
     2  VALUES (3515, 12001, NULL);
   
   1 row created.
   

   You can use the newly created functions to populate an external table. Note that the functions should set columns ad_graphic_dir and ad_graphic_file to NULL if the BFILE column is NULL.

4. Create an external table to contain the data from the print_media table. Use the get_dir_name and get_file_name functions to get the components of the BFILE column.

   SQL> CREATE TABLE print_media_xt
     2  ORGANIZATION EXTERNAL
     3  (
     4    TYPE oracle_datapump
     5    DEFAULT DIRECTORY def_dir1
     6    LOCATION ('pm_xt.dmp')
     7  ) AS
     8  SELECT product_id, ad_id,
     9         get_dir_name (ad_graphic) ad_graphic_dir,
    10         get_file_name(ad_graphic) ad_graphic_file
    11  FROM print_media;
   
   Table created.
   

5. Create a function to load a BFILE column from the data that is in the external table. This function will return NULL if the ad_graphic_dir column in the external table is NULL.

   SQL> CREATE FUNCTION get_bfile (dir VARCHAR2, file VARCHAR2) RETURN
   BFILE is
     2  bf BFILE;
     3  BEGIN
     4    IF dir IS NULL
     5    THEN
     6      RETURN NULL;
     7    ELSE
     8      RETURN BFILENAME(dir,file);
     9    END IF;
    10  END;
    11  /
   
   Function created.
   

6. The get_bfile function can be used to populate a new table containing a BFILE column.

   SQL> CREATE TABLE print_media_int AS
     2  SELECT product_id, ad_id,
     3         get_bfile (ad_graphic_dir, ad_graphic_file) ad_graphic
     4  FROM print_media_xt;
   
   Table created.
   

7. The data in the columns of the newly loaded table should match the data in the columns of the print_media table.

   SQL> SELECT product_id, ad_id,
     2         get_dir_name(ad_graphic),
     3         get_file_name(ad_graphic)
     4  FROM print_media_int
     5  MINUS
     6  SELECT product_id, ad_id,
     7         get_dir_name(ad_graphic),
     8         get_file_name(ad_graphic)
     9  FROM print_media;
   
   no rows selected

1. If a table to be unloaded contains a LONG or LONG RAW column, then define the corresponding columns in the external table as CLOB for LONG columns or BLOB for LONG RAW columns.

   SQL> CREATE TABLE long_tab
     2  (
     3    key                   SMALLINT,
     4    description           LONG
     5  );
   
   Table created.
   
   SQL> INSERT INTO long_tab VALUES (1, 'Description Text');
   
   1 row created.
   

2. Now, an external table can be created that contains a CLOB column to contain the data from the LONG column. Note that when loading the external table, the TO_LOB operator is used to convert the LONG column into a CLOB.

   SQL> CREATE TABLE long_tab_xt
     2  ORGANIZATION EXTERNAL
     3  (
     4    TYPE ORACLE_DATAPUMP
     5    DEFAULT DIRECTORY def_dir1
     6    LOCATION ('long_tab_xt.dmp')
     7  )
     8  AS SELECT key, TO_LOB(description) description FROM long_tab;
   
   Table created.
   

3. The data in the external table can be used to create another table exactly like the one that was unloaded except the new table will contain a LOB column instead of a LONG column.

   SQL> CREATE TABLE lob_tab
     2  AS SELECT * from long_tab_xt;
   
   Table created.
   

4. Verify that the table was created correctly.

   SQL> SELECT * FROM lob_tab;
   
          KEY  DESCRIPTION
   ----------------------------------------------------------------------------
            1  Description Text

1. In the following example, the warehouse column in the external table is used to track whether the warehouse column in the source table is atomically NULL.

   SQL> CREATE TABLE inventories_obj_xt
     2  ORGANIZATION EXTERNAL
     3  (
     4    TYPE ORACLE_DATAPUMP
     5    DEFAULT DIRECTORY def_dir1
     6    LOCATION ('inv_obj_xt.dmp')
     7  )
     8  AS
     9  SELECT oi.product_id,
    10         DECODE (oi.warehouse, NULL, 0, 1) warehouse,
    11         oi.warehouse.location_id location_id,
    12         oi.warehouse.warehouse_id warehouse_id,
    13         oi.warehouse.warehouse_name warehouse_name,
    14         oi.quantity_on_hand
    15  FROM oc_inventories oi;
   
   Table created.
   

   The columns in the external table containing the attributes of the object type can now be used as arguments to the type constructor function when loading a column of that type. Note that the warehouse column in the external table is used to determine whether to call the constructor function for the object or set the column to NULL.

2. Load a new internal table that looks exactly like the oc_inventories view. (The use of the WHERE 1=0 clause creates a new table that looks exactly like the old table but does not copy any data from the old table into the new table.)

   SQL> CREATE TABLE oc_inventories_2 AS SELECT * FROM oc_inventories
   WHERE 1 = 0;
   
   Table created.
   
   SQL> INSERT INTO oc_inventories_2
     2  SELECT product_id,
     3         DECODE (warehouse, 0, NULL,
     4                 warehouse_typ(warehouse_id, warehouse_name,
     5                 location_id)), quantity_on_hand
     6  FROM inventories_obj_xt;
   
   1112 rows created.

Object tables have an object identifier that uniquely identifies every row in the table. The following situations can occur:

• If there is no need to unload and reload the object identifier, then the external table only needs to contain fields for the attributes of the type for the object table.

• If the object identifier (OID) needs to be unloaded and reloaded and the OID for the table is one or more fields in the table, (also known as primary-key-based OIDs), then the external table has one column for every attribute of the type for the table.

• If the OID needs to be unloaded and the OID for the table is system-generated, then the procedure is more complicated. In addition to the attributes of the type, another column needs to be created to hold the system-generated OID.

The steps in the following example demonstrate this last situation.

1. Create a table of a type with system-generated OIDs:

   SQL> CREATE TYPE person AS OBJECT (name varchar2(20)) NOT FINAL
     2  /
   
   Type created.
   
   SQL> CREATE TABLE people OF person;
   
   Table created.
   
   SQL> INSERT INTO people VALUES ('Euclid');
   
   1 row created.
   

2. Create an external table in which the column OID is used to hold the column containing the system-generated OID.

   SQL> CREATE TABLE people_xt
     2  ORGANIZATION EXTERNAL
     3  (
     4    TYPE ORACLE_DATAPUMP
     5    DEFAULT DIRECTORY def_dir1
     6    LOCATION ('people.dmp')
     7  )
     8  AS SELECT SYS_NC_OID$ oid, name FROM people;
   
   Table created.
   

3. Create another table of the same type with system-generated OIDs. Then, execute an INSERT statement to load the new table with data unloaded from the old table.

   SQL> CREATE TABLE people2 OF person;
   
   Table created.
   
   SQL> 
   SQL> INSERT INTO people2 (SYS_NC_OID$, SYS_NC_ROWINFO$)
     2  SELECT oid, person(name) FROM people_xt;
   
   1 row created.
   
   SQL> 
   SQL> SELECT SYS_NC_OID$, name FROM people
     2  MINUS
     3  SELECT SYS_NC_OID$, name FROM people2;
   
   no rows selected