E XPORT T ABLES

The Export Tables tool allows you to export one or more table definitions and their data into a file, so that you can import the tables later. After starting the Export Tables tool, you can select the tables you wish to export, choose an export method (Oracle Export, SQL Inserts, or PL/SQL Developer), and set various options that apply to the export method:

The export function analyzes the foreign key dependencies that may exist between the tables, and places the tables in the correct order in the output file.

Oracle Export method

The screenshot in the previous chapter shows the options of the Oracle Export method. This method lets you specify the options that you can supply on the command line of Oracle’s export utility (see the

“Oracle Server Utilities” guide), and will subsequently launch it to perform the export. The Export Executable field allows you to select a specific version of Oracle’s export utility. By default the most recent version from the current Oracle Home will be used.

After the export is finished, a new Log tab page will be visible. This page contains the logging that was created by the export utility.

Advantages of this method are the speed and the portability of the output file. After all, you can import it anywhere with Oracle’s import utility.

SQL Inserts method

This export method creates a standard SQL script with insert statements and (optionally) DDL statements to recreate the table definition. The tab page contains the following options:

• Drop tables – The generated SQL script contains statements to drop the tables before they are recreated and loaded. The Create tables, Truncate tables, and Delete records options are disabled in this situation, and have an implicit value.

• Create tables – The generated SQL script contains statements to create the tables before they are loaded. This includes constraints, indexes and grants.

• Truncate tables – The generated SQL script uses truncate table statements to empty the tables before they are loaded. This option is faster than the Delete tables, but cannot be used if foreign key constraints exist.

• Delete records – The generated SQL script uses delete statements to empty the tables before they are loaded.

• Disable triggers – The generated SQL script disables all triggers of the tables before they are loaded, and enables them afterwards. This can improve performance, and can sometimes be necessary if the triggers contain checks that are not applicable to the import process.

• Disable foreign key constraints – The generated SQL script disables all foreign keys of the tables before they are loaded, and enables them afterwards. This can be necessary for self-referencing foreign keys. Foreign keys between different tables will not lead to conflicts, because the tables are exported in a correct order. Disabling foreign keys can also improve performance.

• Include storage – The generated SQL includes the original storage information such as tablespace names and initial sizes for table creation statements. These may differ across databases, so this may not always be appropriate.

• Include privileges – The generated SQL includes the grants of the object privileges to other users and roles. When the objects are recreated in a different database, these users and roles need to exist for obvious reasons.

• Commit every – Controls how many records will be inserted in the generated SQL script before a commit is executed. If this value is zero, all inserts will be committed at once at the end of the SQL script. For large export files and small rollback segments it will be necessary to supply an appropriate value here.

• Where clause – Only records that match the where clause will be exported. The where clause must be applicable to all selected tables!

Advantages of this method are the portability of the SQL script, and the possibility to edit the results with a text editor.

PL/SQL Developer method

This export method creates a custom, compressed PL/SQL Developer export file with the table definitions and data. The tab page contains the following options:

• Where clause – Only records that match the where clause will be exported. The where clause must be applicable to all selected tables!

• Compress file – Disabling this option will disable the compression. This can lead to improved performance if the data cannot be very well compressed (e.g. for long raw or BLOB data that is already compressed in the database).

• Include storage – The generated SQL includes the original storage information such as tablespace names and initial sizes for table creation statements. These may differ across databases, so this may not always be appropriate.

• Include privileges – The generated SQL includes the grants of the object privileges to other users and roles. When the objects are recreated in a different database, these users and roles need to exist for obvious reasons.

Advantages of this method are the greatly reduced file sizes of the export files, the fact that all options are deferred to the import phase, and that you can easily select tables during import.