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
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
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
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
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:
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
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');
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
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
To take advantage of the COMPRESSION option, you can enable thep_compression parameter to the gen_dp procedure, as shown here:begin
Executing the gen_dp procedure with both encryption and compression enabled yields the following output:create table docs_dp
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:
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.