Data Pump Interview Questions and Answers

1. How to import only metadata?

      CONTENT= METADATA_ONLY

2. How to import into different user/tablespace/datafile/table?

       REMAP_SCHEMA

3. What is use of DIRECT=Y option in exp?

   Setting direct=yes, to extract data by reading the data directly, bypasses the SGA, bypassing the SQL               command-processing layer (evaluating buffer), so it should be faster. Default value N.

 4. How to Reduce the Dump file size ?

       Compressed = Y

5. How to check the  dump file size before ?

       ESTIMATE_ONLY=Y


6. How to take Query Import ?

   expdp dumpfile=dumpfilename.dmp logfile=logfilename.log query=username.tablename:where                          columnname 



7. How to Check User Data Stored which Tablespace ?

     select distinct tablespace_name from dba_segments where owner='rms12';


8. What are all the many different command-line parameters that can be used with datapump?

    List of command-line paeters used with Datapump export expdp isgiven below:

9. How to improve exp performance?

      1. Set the BUFFER parameter to a high value. Default is 256KB.

      2. Stop unnecessary applications to free the resources.

10. What is use of IGNORE option in imp?

       Will ignore the errors during import and will continue the import.

11. What are the differences between EXPDP and exp (Data Pump or normal exp/imp)?

     Data Pump is server centric (files will be at server).

     Data Pump has APIs, from procedures we can run Data Pump jobs.

12. Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import?

       Data Pump is block mode, exp is byte mode. 

13. What is the order of importing objects in impdp?

       Tablespaces

14. Give details on datapump include and exclude parameter:-

   Data Pump INCLUDE And Exclude Parameters are two mutually exclusive parameters.We can use
  them to  perform metadata filtering..Metadata filtering enables us to selectively leave out or include certain       type of  objects during a data pump export or import job.Since 9i, in the old export and import utility we were   using GRANTS, CONSTRAINTS, INDEXES parameters to specify whether we wanted to export/import  those objects.INCLUDE and EXCLUDE can be used for :

    1) INDEXES

    2) GRANTS

    3) CONSTRAINTS

    4) PACKAGES

    EXCLUDE Parameter – omit specific database object types from an export or import operation

    INCLUDE Parameter – helps us include a specific set of objects

        Usage :

               EXCLUDE=object_type[:name_clause]

              INCLUDE=object_type[:name_clause]

               name_clause is optional

15. What is the role of datapump in upgrade?

     We can make use of datapump to perform indirect upgrade of an oracle database. Say, you want 

     to test an  upgrade, create a test version that is 100% in-sync with production after major prod release or          patch release. In all these cases datapump comes handy.Coming to the latest oracle 12c, this needs oracle 10g minimum for direct upgrade. If the database is at a version lower than 10g, upgrade it to 10g, use    datapump create a dump, install oracel 12c software, perform import using impdp.

16. Expdp and Impdp all Parameters ?

    ATTACHCOMPRESSIONCONTENT

    DATA_OPTIONS

    DIRCTORY

    DUMPFILE

    ENCRYPTION

    ENCRYPTION_ALGORITHM

    ENCRYPTION_MODE

    ENCRYPTION_PASSWORD

    ESTIMATE

    ESTIMATE_ONLY

    EXCLUDE

    FILESIZE

    FLASHACK_SCN

    FLASHBACK_TIME

    FULL

    HELP

    INCLUDE

   JOB_NAME

   LOGFILE

   NETWORK_LINK

   NOLOGFILE

   PARALLEL

   PARFILE

   QUERY

   REMAP_DATA

   REUSE_DUMPFILES

   SAMPLE

   SCHEMAS

   STATUS

   TABLES

   TABLESPACES

   TRANSPORT_FULL_CHECK

   TRANSPORT_TABLESPACES

   TRANSPORTABLE

   VERSION

   REMAP_DATAFILE
  

                                                             Thank You