Export using the direct path method export in Oracle

Oracle 11g does support the "direct path export method" when using the Data Pump Export (expdp)[1] utility for database backup. The direct path method is a highly efficient way to extract data from the database because it bypasses much of the overhead associated with conventional export operations.
Key Points About the Direct Path Export Method in Oracle 11g:

1. Efficient Data Extraction:
   • The direct path method extracts data directly from the database blocks into the export dump file. It skips the SQL processing layers, making it faster and more efficient than conventional export methods.
2. When Used:
   • The direct path method is used automatically by Data Pump Export whenever possible, depending on the structure and data types of the tables being exported. Certain conditions or data types may prevent the use of direct path, in which case Oracle falls back to conventional path export.
3. Restrictions:
   • The direct path method may not be used if tables have certain data types or if the tables are in certain configurations, such as tables with:
     • LOB (Large Object) columns that do not meet specific criteria
     • VARRAYs or nested tables
     • Data types that are not supported by the direct path method
4. Performance Benefits:
   • Because the direct path method bypasses much of the Oracle SQL engine, it can greatly improve the performance of export operations, especially for large volumes of data.

Example of Using Data Pump Export with Direct Path:
When using Data Pump Export, you don’t explicitly specify the direct path method. Instead, Oracle will automatically determine the best method. However, you can use parameters to optimize performance, such as:

expdp username/password DIRECTORY=dpump_dir1 DUMPFILE=myexport.dmp LOGFILE=myexport.log SCHEMAS=myschema

Note:

• The "original Export utility (exp)"" also supported the direct path method in earlier versions of Oracle, but starting with Oracle 11g, it is recommended to use Data Pump Export (expdp) ^{for better performance and more features.}

To summarize, Oracle 11g supports the direct path method for export, particularly when using the Data Pump Export utility.

Up to this point, we have been exporting database information using the conventional path export. This lesson introduces the use of the direct path export to improve the performance of your database. The conventional path export uses SQL SELECT statements to extract data from the tables. Direct path export will read data directly from the database and transfer it to the export file, which avoids the overhead of the SQL statement.
A conventional path is illustrated below.

---

A direct path should look like the following:

To invoke a direct path, use the parameter direct=y as part of your export command. An example of this is:

C:/Oracle/bin> exp80 userid=system/manager full=y 


direct=y

By using direct path, you:

1. Can improve the performance of a database direct read mode by setting the init.ora parameter compatible = appropriate database version number, where the version number is 7.1.5 or higher.
2. May increase the value of the recordlength parameter, usually to a multiple of DB_BLOCK_SIZE or a multiple of the file system I/O block size.
3. Cannot export rows containing LOB, BFILE, REF, or object type columns. Only the data definition to create the table is exported.
4. Must match the database character set with the client character set. Therefore, you must use the database character set.
5. Cannot use an interactive session with direct path export.

The following series of imagesprovides sample output from a full direct path export:

• Conventional Path Export Versus Direct Path Export
  Export provides two methods for exporting table data:
  1. Conventional path Export
  2. Direct path Export
• Conventional path Export uses the SQL SELECT statement to extract data from tables. Data is read from disk into a buffer cache, and rows are transferred to the evaluating buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file.
• Direct path Export is much faster than conventional path Export because data is read from disk into the buffer cache and rows are transferred directly to the Export client. The evaluating buffer (that is, the SQL command-processing layer) is bypassed. The data is already in the format that Export expects, thus avoiding unnecessary data conversion. The data is transferred to the Export client, which then writes the data into the export file.

In the next lesson, you will learn how to use Import to restore your database objects.

[1] Data Pump Export (expdp): Data Pump Export (expdp) is a utility provided by Oracle Database for exporting data and metadata into a set of operating system files called a dump file set. This utility allows for efficient and flexible movement of data, enabling users to export an entire database, specific schemas, tablespaces, or even individual tables.