Thursday, February 10, 2011

Basic differences between Data pump and Export/Import


If you have worked with prior 10g database you possibly are familiar with exp/imp utilities of oracle database. Oracle 10g introduces a new feature called data pump export and import.Data pump export/import differs from original export/import. The difference is listed below.

1)Impdp/Expdp has self-tuning unities. Tuning parameters that were used in original Export and Import, such as BUFFER and RECORDLENGTH, are neither required nor supported by Data Pump Export and Import.

2)Data Pump represent metadata in the dump file set as XML documents rather than as DDL commands.

3)Impdp/Expdp use parallel execution rather than a single stream of execution, for improved performance.

4)In Data Pump expdp full=y and then impdp schemas=prod is same as of expdp schemas=prod and then impdp full=y where in original export/import does not always exhibit this behavior.

5)Expdp/Impdp access files on the server rather than on the client.

6)Expdp/Impdp operate on a group of files called a dump file set rather than on a single sequential dump file.

7)Sequential media, such as tapes and pipes, are not supported in oracle data pump.But in original export/import we could directly compress the dump by using pipes.

8)The Data Pump method for moving data between different database versions is different than the method used by original Export/Import.

9)When you are importing data into an existing table using either APPEND or TRUNCATE, if any row violates an active constraint, the load is discontinued and no data is loaded. This is different from original Import, which logs any rows that are in violation and continues with the load.

10)Expdp/Impdp consume more undo tablespace than original Export and Import.

11)If a table has compression enabled, Data Pump Import attempts to compress the data being loaded. Whereas, the original Import utility loaded data in such a way that if a even table had compression enabled, the data was not compressed upon import.

12)Data Pump supports character set conversion for both direct path and external tables. Most of the restrictions that exist for character set conversions in the original Import utility do not apply to Data Pump. The one case in which character set conversions are not supported under the Data Pump is when using transportable tablespaces.

13)There is no option to merge extents when you re-create tables. In original Import, this was provided by the COMPRESS parameter. Instead, extents are reallocated according to storage parameters for the target table.

Differences between Data Pump impdp and import utility

The original import utility dates back to the earliest releases of Oracle, and it's quite slow and primitive compared to Data Pump.  While the old import (imp) and Data Pump import (impdp) do the same thing, they are completely different utilities, with different syntax and characteristics. 
Here are the major syntax differences between import and Data Pump impdp:
  • Data Pump does not use the BUFFERS parameter
  • Data Pump export represents the data in XML format
  • A Data Pump schema import will recreate the user and execute all of the associated security privileges (grants, user password history).
  • Data Pump's parallel processing feature is dynamic. You can connect to a Data Pump job that is currently running and dynamically alter the number of parallel processes.
  • Data Pump will recreate the user, whereas the old imp utility required the DBA to create the user ID before importing. 

6 comments:

  1. That was a wonderful material. Good Job... could you share notes and interview questions asked for expereinced ORACLE DBA professionals to my email id sridevi948@gmail.com.

    Thanks in advance
    Sridevi

    ReplyDelete
    Replies
    1. Thanks a lot.. Good post..
      I found lot of datapump related topics in this link..
      http://www.acehints.com/2012/02/datapump-vs-expimp-difference-or.html

      Delete
    2. Dear Sri Devi,
      You can find the Oracle DBA related good question with answer at below link.
      http://shahnawazdba06.blogspot.com/2013/06/oracle-dba-part-1-question.html
      Thanking you
      --
      Mohammad Shahnawaz
      Oracle's Passion

      Delete
  2. Here is a thread on how to use dump utility for exporting data from 10g

    ReplyDelete
  3. there is no need of tunning in data pump is it??

    ReplyDelete
  4. Interview Questions
    http://www.sainora.com/article/category/Interview-Questions.html

    ReplyDelete