Oracle Data Pump 11g: Little-Known New Feature
Jan 7, 2008 / By Babette Turner-Underwood
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
REPLACE. Do these parameters look familiar? Possibly reminiscent of the SQL*Loader parameters (
INTO TABLE x INSERT/
APPEND)? They are very similar and they work the same way, with the exception of
Here’s an explanation of each option.
SKIP: The default value for
TABLE_EXISTS_ACTION. If the table exists, then
SKIPwill 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.
drop table test1; create table test1 ( col1 number primary key, col2 number default 99, col3 number ); create index ix_test1_col2 on test1 ( col2); create trigger trg_test1 before insert or update on test1 for each row begin :new.col3 := :new.col1 + :new.col2; end; / begin for i in 1 .. 1001 loop insert into test1 (col1) values (i); end loop; end; / select col1, col2, col3 from test1 where rownum < 5; COL1 COL2 COL3 ---------- ---------- ---------- 1 99 100 2 99 101 3 99 102 4 99 103
In the first test, we will do a full export of the table, modify the data and drop the trigger, and then do a Data Pump import with the
REPLACE option. After the Data Pump import, we can see that the data is the same and that the trigger has been recreated.
expdp babette/babette tables=test1 dumpfile=test.1.dmp update test1 set col1 = col1 + 2000, col2 = 500; drop trigger trg_test1; select col1, col2, col3 from test1 where rownum < 5; COL1 COL2 COL3 ---------- ---------- ---------- 2001 500 2501 2002 500 2502 2003 500 2503 2004 500 2504 impdp babette/babette dumpfile=test1.dmp table_exists_action=replace -- -- data is the same as before update ( was replace with data in dumpfile) -- SQL> select count(*), col2 from test1 group by col2; COUNT(*) COL2 ---------- ---------- 1001 500 select col1, col2, col3 from test1 where rownum < 5; COL1 COL2 COL3 ---------- ---------- ---------- 1 99 100 2 99 101 3 99 102 4 99 103 -- -- trigger has been re-created -- SQL> select trigger_name from user_triggers; TRIGGER_NAME ------------------------------ TRG_TEST1
In this second test, we will update the data (so we don’t get PK violations on importing data). We will reuse the export dumpfile previously created. After the Data Pump import, notice that the data has been added instead of replaced.
update test1 set col1 = col1 + 2000, col2 = col3 / 2; impdp babette/babette dumpfile=test1.dmp table_exists_action=append -- data is appended and trigger is effective select count(*), grouping from (select case when col2 between 1 and 100 then 1 when col2 between 101 and 499 then 2 when col2 between 500 and 1000 then 3 when col2 between 1001 and 99999 then 4 end grouping from test1) a group by grouping COUNT(*) GROUPING ---------- ---------- 1001 1 1001 4
In this third test, we will restore the original data (using
expdp REPLACE). Before the import, we will drop the trigger. After the Data Pump import, we can see that the data has been added. We also notice that the trigger on the table has not been re-created. This is because the table existed, so metadata was not applied.
impdp babette/babette dumpfile=test1.dmp table_exists_action=replace drop trigger trg_test1; update test1 set col1 = col1 + 2000, col2 = col3 / 2; impdp babette/babette dumpfile=test1.dmp table_exists_action=append -- notice that the append did NOT execute any DDL -- due to the existence of the table ALL metdata is excluded. SQL> select trigger_name from user_triggers; no rows selected
So what if we want to apply the metadata from the export file even if the table already exists? Oracle has provided a way to do that too. We can use an
INCLUDE statement to include the type of objects you want to include. However, in my testing I found this only worked when I selected
CONTENT=METADATA_ONLY, and it did not work when I did
CONTENT=ALL (data and metadata)
impdp babette/babette dumpfile=test1.dmp content=metadata_only include=trigger
How does this compare with the original import? It behaves very similarly: import does not apply any metadata if the object already exists. However, this behaviour can be overridden with
exp babette/babette tables=test1 file=test1.exp.dmp update test1 set col1 = col1 + 2000, col2 = col3 / 2; drop trigger trg_test1; imp babette/babette file=test1.exp.dmp full=y ignore=Y SQL> select count(*) from test1 COUNT(*) ---------- 2002 SQL> select trigger_name from user_triggers; TRIGGER_NAME ------------------------------ TRG_TEST1
Having looked at the parameter options and how it works, the next logical question is, how can this be useful?
There have been times where I have had full schema imports that failed part-way through. One option available was to figure out which tables had not yet been done, and try to dynamically create a
TABLES= parameter to load those. But, if the tables were re-imported, the data would have been duplicated. Alternatively, I would clean up the schema and start again. With 11g, the
TABLE_EXISTS_ACTION gives us more flexibility. We can simply re-run the import and Oracle will automatically skip all objects that already exist.
We can also use the
REPLACE option to undo all object changes and restore to a baseline. For example, you have an export of test data that you use to refresh certain tables in a schema. Rather than having to manually locate and remove the old versions of the tables (which may have had DDL changes applied in a test environment), you simply run your Data Pump import with the
Having unearthed this parameter for myself and put it to work, I am sure that others can think of lots of other uses for it. Please add your comments to show how you have used this parameter.
2 comments on “Oracle Data Pump 11g: Little-Known New Feature”
Pingback: Back to work links « I’m just a simple DBA on a complex production system