Data Pump vs EXP/IMP: Difference or Comparison Between Data Pump Expdp/impdp and Conventional EXP/IMP
- Datapump introduced in Oracle 10g whereas conventional exp/imp was used for logical backups in prior versions of oracle 10g. Exp/imp works even in all versions of Oracle.
- Conventional exp/imp can utilize the client machine resource for taking the backups but, the datapump works only in server.
- XML schema, XML types are supported in expdp/impdp but not in exp/imp
- Parallel execution is possible in datapump which is not supported in conventional exp/imp. Using the parallel option the datapump generates multiple dump files simultaneously.
- Datapump cannot export the data into sequential medias like tapes.
- Datapump has better control than exp/imp on the backup job with START, STOP and RESTART options.
- Datapump gives 15 – 50% performance improvement than exp/imp.
- Table Extent compression can be done using COMPRESS option whereas in datapump COMPRESSION does the dumpfile compression.
- Additional Features Available with datapump are:
a. Job Estimation can be done in datapump
b. SAMPLE parameter is used for taking the sample % data.
c. Failed export/import Jobs can be restarted
d. EXCLUDE/INCLUDE parameter allows the fine-grained object selection. Read More On EXCLUDE/INCLUDE...
e. Data remapping can be done using REMAPDATA parameter.
f. Export and import can be taken over the network using database links even without generating the dump file using NETWORK_LINK parameter.
g. CONTENT parameter gives the freedom for what to export with options METADATA ONLY, DATA, BOTH.
h. You don’t need to specify the BUFFER size in datapump
i. Job estimated completion time can be monitored from v$session_longops view.
j. Dump file can be compressed with COMPRESSION parameter. In conventional exp/imp you have to compress the dumps using OS utilities.
k. Data encryption can be done in datapump.
l. DATAPUMP has interactive options like ADD_FILE, START_JOB, KILL_JOB, STOP_JOB.
m. REUSE_DUMPFILES parameter asks the confirmation/rewrite the existing dumpfile.
- Few parameter name changes in datapump and it always makes confusion with parameters in normal exp/imp
SLNO
|
EXP/IMP Parameter
|
EXPDP/IMPDP Parameter
|
1
|
owner
|
schemas
|
2
|
file
|
dumpfile
|
3
|
log
|
logfile/nologfile
|
4
|
IMP: fromuser, touser
|
IMPDP: remap_schema
|
- There are many more to explain about each parameter. Please raise your question in comment column. I will try to give you the explanation. J
Posted by Babette Turner-Underwood on Jan 7, 2008
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.
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 IGNORE=Y parameter.
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 REPLACE option.
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.
- Trigger can be created along with APPEND option without a need to imp using CONTENT option. Use INCLUDE option along with TABLE_EXISTS_ACTION
impdp test/test dumpfile=test.1.dmp table_exists_action=append include=trigger
Reply
No comments:
Post a Comment