Pythian Group

While perusing the Oracle 11g Data Pump documents recently, I noticed a new parameter that was introduced in Oracle 10g, but I had missed it there. The parameter is TABLE_EXISTS_ACTION, and it applies only to the Data Pump Import.

Basically, this feature allows you to decide how to handle importing data if a table already exists. The options are SKIP (defaut), APPEND, TRUNCATE, and REPLACE. Do these parameters look familiar? Possibly reminiscent of the SQL*Loader parameters (INTO TABLE x INSERT/REPLACE/TRUNCATE/APPEND)? They are very similar and they work the same way, with the exception of SKIP.

Here’s an explanation of each option.

• SKIP: The default value for TABLE_EXISTS_ACTION. If the table exists, then SKIP will cause the table (and any related data) to not be loaded. The table will be skipped.
• TRUNCATE: This will truncate the data in the table and load the data. If the table already exists, no metadata will be applied.
• APPEND: This will append the data to the end of the table. Again, if the table already exists, no metadata will be applied.
• REPLACE: This will drop the existing object, recreate the object with the information from the dumpfile, and then load the data.

Let’s create a simple test case to demonstrate. Read the rest of this entry . . .