External Tables Based on Data Pump - Oracle 11g

The concept of an external table was introduced in Oracle 9i Database. Oracle 9i Database introduced external tables using the ORACLE_LOADER driver. External tables were introduced strictly as read-only data; they were used only to load data into the database, and not to write it back out again.

In Oracle Database 10g,Oracle introduced the ORACLE_DATAPUMP driver, enabling you to create external tables that used Data Pump to move data into and out of the database.In Oracle Database 11g, Oracle enhances the Data Pump API and the ORACLE_DATAPUMP access driver.

One such improvement ensures that a row error will not cause a table load to abort.In Oracle Database 10g, without the reject limit clause, a single row error in an external table caused the entire table load to fail.DBAs had to investigate the error, resolve the error causing the failure,and reload the table.In Oracle Database 10g, you can create a Data Pump export dump file using the create table syntax similar to what is shown here:

1 create table docs_10g
2 organization external
3 (
4 type oracle_datapump
5 default directory dbadir
6 access parameters
7 (logfile dbadir:docs)
8 location ('docs.dmp')
9 )
10* as select * from docs
SQL> /
Table created.

In Oracle Database 11g,you can modify this script to take advantage of the new COMPRESSION and ENCRYPTION options.You do this by supplying additional options to the ACCESS PARAMETERS clause. See the following example of creating a compressed external table. Please pay particular attentionto the ACCESS PARAMETERS clause:

1 create table docs
2 organization external
3 (
4 type oracle_datapump
5 default directory dbadir
6 access parameters
7 (logfile logdir:docs compression enabled)
8 location ('docs.dmp')
9 )
10* as select * from documents
SQL> /
Table created.

In addition to specifying the COMPRESSION option,you can also specify both COMPRESSION and ENCRYPTION options,as shown in the following example:

SQL> create table docs
2 organization external
3 (
4 type oracle_datapump
5 default directory dbadir
6 access parameters
7 (logfile logdir:docs compression enabled encryption enabled)
8 location ('docs.dmp')
9 )
10 as select * from documents
11 /
Table created.

To use the encryption option, TDE must be set up first; otherwise, you will receive an ORA-28365 alert indicating that the wallet is not open.For infor mation about how to enable TDE.Now, let’s read the external table based on the encrypted and compressed dump file.The following query confirms that the data content is accurate and accounted for:

SQL> select doc_id, name
2 from docs;
DOC_ID NAME
------- --------------------------------------
1002 Redhat Linux System Administration.doc
1003 RMAN for DBAs.doc
1004 Java for Oracle Developers.doc
...
...

One great benefit of using the external table feature to write out Data Pump export dumps is that you can perform complex joins and data transfor mations while extracting data into export dump files.External tables can be created with the PARALLEL option.You can also select from views or even join views to derive the underlying Data Pump extract.

This mechanism of writing dump files significantly simplifies porting data from one database to another.On top of this, you can perform comparisons of the data before committing to the production database.

Stored Procedure to Generate External Table Using a Data Pump

Here’s a simple stored procedure to generate DDL for external tables using Data Pump dump files. This stored procedure generates the syntax to create an external table using Data Pump.The stored procedure is called gen_dp and accepts multiple parameters. All but one parameter is required.

The only required parameter is the table name.All other parameters have default values and can be overridden by assigning the appropriate parameter with values.

The intent of this script is to provide an automated mechanism to generate an external table using a Data Pump dump file for a specific table.This script can increase Data Pump usability, acceptance,and productivity in your organization.Here are the basic assumptions that the gen_dp stored procedure makes:

  • The default directory name is called oratmp and is mapped to a file system directory.
  • The name of the target table (the table that will be created) will be the source table name appended with a _dp.
  • The logfile will be named the same as the source table.
  • The dump file will be named as the source table name appended by .dmp.
  • The external table dump file will not be encrypted.
  • The external table dump file will not be compressed.

You can customize these default settings to fit your database environ ment.The following script uses the ORACLE_DATAPUMP type and the appropriate entries to ACCESS PARAMETERS to generate the syntax to create an external table:

set serveroutput on
CREATE OR REPLACE PROCEDURE gen_dp
(
p_table_name IN VARCHAR2,
p_table_name_extension IN VARCHAR2 := '_dp',
p_directory IN VARCHAR2 := 'oratmp',
p_dumpfile IN VARCHAR2 := 'sat', -- sat = same as table
p_logfile IN VARCHAR2 := 'sat', -- sat = same as table
p_compression IN VARCHAR2 := 'disabled',
p_encryption IN VARCHAR2 := 'disabled'
)
AS
v_dumpfile VARCHAR2(100) := p_dumpfile;
v_logfile VARCHAR2(100) := p_logfile;
v_compression VARCHAR2(30) := p_compression;
v_encryption VARCHAR2(30) := p_encryption;
BEGIN
IF v_dumpfile = 'sat' THEN
v_dumpfile := p_table_name||'.dmp';
END IF;
IF v_logfile = 'sat' THEN
v_logfile := p_table_name;
END IF;
IF lower(v_compression) = 'enabled' THEN
v_compression := 'compression enabled';
elsif lower(v_compression) = 'disabled' THEN
v_compression := '';
END IF;
F lower(v_encryption) = 'enabled' THEN
v_encryption := 'encryption enabled';
ELSIF lower(v_encryption) = 'disabled' THEN
v_encryption := '';
END IF;
DBMS_OUTPUT.PUT_LINE ('create table '||p_table_name||p_table_name_extension);
DBMS_OUTPUT.PUT_LINE (' organization external');
DBMS_OUTPUT.PUT_LINE ('(');
DBMS_OUTPUT.PUT_LINE (' type oracle_datapump ');
DBMS_OUTPUT.PUT_LINE (' default directory '||p_directory);
DBMS_OUTPUT.PUT_LINE (' access parameters ');
DBMS_OUTPUT.PUT_LINE (' ( logfile '||v_logfile||' '||v_compression||'
'||v_encryption||')' );
DBMS_OUTPUT.PUT_LINE (' location ('||chr(39)||v_dumpfile||chr(39)||')' );
DBMS_OUTPUT.PUT_LINE (')');
DBMS_OUTPUT.PUT_LINE (' as');
DBMS_OUTPUT.PUT_LINE (' select * from '||p_table_name);
END;
/Stored Procedure to Generate External Table Using a Data Pump

Let’s execute the gen_dp script. Accepting all the default values, let’s generate the syntax to create an external table called DOCS_DP deriving the data from the DOCS table:

SQL> exec gen_dp('docs');
create table docs_dp
organization external
(
type oracle_datapump
default directory oratmp
access parameters
( logfile docs )
location ('docs.dmp')
)
as
select * from docs PL/SQL procedure successfully completed.Stored Procedure to Generate External Table Using a Data Pump

If you have TDE enabled for the database,you will want the script to generate the syntax to take advantage of encryption. The p_encryption parameter can be passed a string value of enabled to enable encryption, as shown here:

begin
gen_dp(p_table_name => 'docs',
p_encryption => 'enabled');
end;
/

Execution of the gen_dp procedure with the p_encryption parameter generates the following SQL syntax with the encryption enabled option to the ACCESS PARAMETERS parameter:

create table docs_dp
organization external
(
type oracle_datapump
default directory oratmp
access parameters
( logfile docs encryption enabled)
location ('docs.dmp')
)
as
select * from docs

To take advantage of the COMPRESSION option, you can enable thep_compression parameter to the gen_dp procedure, as shown here:

begin
gen_dp(p_table_name => 'docs',
p_compression => ‘enabled’,
p_encryption => 'enabled');
end;
/

Executing the gen_dp procedure with both encryption and compression enabled yields the following output:

create table docs_dp
organization external
(
type oracle_datapump
default directory oratmp
ACCESS PARAMETERS
( logfile docs compression enabled encryption enabled)
location ('docs.dmp')
)
as
select * from docs

If you are storing Data Pump dump files inside ASM, you should be aware that Data Pump log files cannot reside inside ASM. To effectively utilize ASM, the dump files can reside in ASM, but the log files must reside on the file system.Attempting to store a log file inside of ASM will yield an error that resembles the following Oracle error message:

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file +data/DBA11g/DUMPSET/docs2.log

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics