Wednesday, August 26, 2009

Oracle Data Pump Export/Import

Oracle Data Pump utility is used for exporting data and metadata into set of operating system files and it is newer, faster and flexible alternative to “export/import” utilities.

Oracle Datapump utility introduced in Oracle 10g Release1 and this utility can be invoked using expdp(export) and impdb(import) commands. User need to specify the export/import parameters to determine the operation and you can specify the parameters on the command line or in a parameter file.

The expdp and impdp uses the procedures provided in the DBMS_DATAPUMP package to execute the commands and DBMS_METADATA package is used to move the data.

Please note that it is not possible to start or restart data pump jobs on one instance in Oracle RAC if jobs currently running on other instances.

Oracle Data Pump Export :-

1. Create directory object as SYS user.
SQL> create or replace directory export_dir as '/oradata/export’;

2. Grant Read/Write privilege on the directory to the user, who invokes the Data pump export.
SQL> grant read,write on directory export_dir to test_user;

3. Take Data Pump Export

Click here to see Roles/privileges required for Export modes.

Oracle data pump export examples for all 5 modes.

(i) Full Database Export
$ expdp test_user/test123 full=y directory=export_dir dumpfile=expdp_fulldb.dmp logfile=expdp_fulldb.log

(ii) Schema Export
$expdp test_user/test123 schemas=test_user directory= export _dir dumpfile=expdp_test_user.dmp logfile=expdp_test_user.log

If you want to export more than one schema then specify the schema names separated by comma.

(iii)Table Export
$ expdp test_user/test123 tables=emp,dept directory= export _dir dumpfile=expdp_tables.dmp logfile=expdp_tables.log

You can specify more than one table.

(iv) Tablespace Export
$ expdp test_user/test123 tablespaces=test_user_tbs directory= export _dir dumpfile=expdp_tbs.dmp logfile=expdp_tbs.log

You can specify more than one tablespace.

(v) Transportable tablespace
$ expdp test_user/test123 transport_tablespaces=test_user_tbs transport_full_check=y directory= export _dir dumpfile=expdp_trans_tbs.dmp logfile=expdp_trans_tbs.log

Click here to learn more on Transportable Tablespace with examples.

Oracle Data Pump Import :-
Data Pump Import utility is used for loading an export dump files into a target system and we can load one or more files.

Copy the dump file to the target system where you to import.

1. Create directory object as SYS user.
SQL> create directory import_dir as '/oradata/import';

2. Grant Read/Write privilege on the Directory to the user, who invokes the Data Pump import.
SQL> grant read,write on directory import_dir to test_user;

3. Import the data using Data Pump Import.

Oracle data pump import examples for all 5 modes.

(i) Full Database Import
$ impdp test_user/test123 full=Y directory=imp_dir dumpfile=expdp_fulldb.dmp logfile=imp_fulldb.log

(ii) Schema Import
$impdp test_user/test123 schemas=test_user directory=imp_dir dumpfile=expdp_test_user.dmp Logfile=impdp_test_user.log

(iii) Table Import
$ impdp test_user/test123 tables=emp,dept directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log

From 11g, you can reaname a table during the import
REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename
$ impdp test_user/test123 remap_table=test_user.emp:emp1 directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log

Tables will not be remapped if they already exist even if the TABLE_EXISTS_ACTION is set to TRUNCATE or APPEND

(iv) Tablespace Import
$ impdp test_user/test123 tablespaces=test_user_tbs directory=imp_dir dumpfile=expdp_tbs.dmp logfile=impdp_tbs.log

Above example imports all tables that have data in tablespaces test_user_tbs and it assumes that the tablespaces already exist.

(v) Transportable Tablespace
Click here to to import data using Transportable Tablespace method.

Common Errors with Data pump import (impdp) utility:-

1. ORA-31631: privileges are required
   ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remapping
Cause: A user attempted to remap objects during an import but lacked the IMPORT_FULL_DATABASE privilege.
Action: Retry the job from a schema that owns the IMPORT_FULL_DATABASE privilege.

2. ORA-31631: privileges are required
    ORA-39161: Full database jobs require privileges
Cause: Either an attempt to perform a full database export without the EXP_FULL_DATABASE role or an attempt to perform a full database import over a network link without the IMP_FULL_DATABASE role.
Action: Retry the operation in a schema that has the required roles.

3. ORA-01950: no privileges on tablespace "string"
    Cause: User does not have privileges to allocate an extent in the specified tablespace.
   Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.

Click here to learn Roles/ privileges required for Data pump Export and Import.

4. import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    IMP-00017: following statement failed with ORACLE error 3113:
    "BEGIN "
    "SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE   SYS.DBMS_RULE_ADM.CREATE_EVALUATIO" "N_CONTEXT_OBJ, 'SYS',TRUE);"
Cause: Import fails while executing the following command.
Action: Login as sys and run the following scripts
$ORACLE_HOME/rdbms/admin/dbmsread.sql
$ORACLE_HOME/rdbms/admin/prvtread.plb

5. Import failed with below errors
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
Cause: The user that you are importing does not have privileages on CTXSYS.DRIIMP package or CTXSYS user does not exists
Action: Create CTXSYS user or grant required permissions

Please see the Data pump Export and Import related documents:
Click here for Data Pump Export modes and Interfaces.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

22 comments:

  1. Good information about export and import

    ReplyDelete
  2. similar post here :

    http://chandu208.blogspot.com/2011/04/oracle-data-pump.html

    ReplyDelete
  3. Dear Satish Sir,
    while exporting tablespace iam getting following errors
    ORA-39167 : tablespace exptbs was not found
    ora- 31655 : no data or metadata obejects selected for job
    however i have created the tablespace exptbs before exporting this tablespace also for avoiding privilege issue i am using system/manager login

    expdp system/manager dumpfile=exptbs.dmp directory=export tablespaces=exptbs logfile=tbs.log

    Please help me sir

    Regards

    Shabab Rafi

    ReplyDelete
  4. Can you provide the output of below SQL and also provide the expdp log

    SQL> select tablespace_name,file_name from dba_data_files where tablespace_name like '%EXPTBS%';

    ReplyDelete
  5. I have dump file of remote server version 11.2.0.3.0 using expdp utility and
    this dump i am importing into local server version 11.2.0.1.0 using impdp utility but
    only 5 tables have getting following error :

    ORA-31693: Table data object "BEAMNEW"."TRAIN_SAVEPLAN_DETAILS" failed to load/unload and is being skipped due to error:
    ORA-29913: error in executing ODCIEXTTABLEFETCH callout
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00216: invalid character 0 (0x0)
    Error at line 1

    Kindly give me solution ASAP....Thanks in advance

    ReplyDelete
  6. Refer below metalink note, it should help

    MOS Doc 1391688.1 (DataPump Import Of XMLTYPE Fails With Errors ORA-31693 ORA-29913 ORA-31011 ORA-19202 LPX-217 Invalid Character) may be helpful

    Regards
    Satish

    ReplyDelete
  7. Remote DBA Experts are the professionals who have skills and experience in database administration. Due to these people database administration gets security and it services as bug-free administration.

    ReplyDelete
  8. Thanks for the information. Helped us to convince most on how this process works and what they could achieve by following these guidelines services web design

    ReplyDelete
  9. data pump export and import these are the awesome tools to change the location of huge data in small times. as this tool is best used for oracle data server change. we have oracle fusion tutorial

    ReplyDelete
  10. Really very informative and creative contents. This concept is a good way to enhance the knowledge.thanks for sharing please

    keep it up
    Oracle Training in Gurgaon


    ReplyDelete
  11. Hi Satish,

    Thank you! Thank you! Thank you! Your blog was a total game changer!

    Hello people, So I am having trouble uploading a file to my bucket. I have the proper credentials all set. The problem is when I try doing a putRequest('bucket', 'keyName', 'fileFullPath') it will only save the file name and not the bytes. So I try sending it as an input stream to a byte array. This way it says there is no such file at specified location? I am running linux so It says no specified file at /home/{user}/pictures/example.jpg This is the problem I believe because it is searching from /home and not /

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thanks a heaps,
    Mouni

    ReplyDelete
  12. Thus, you ought to consider the above told factors before you go for the choice of logo configuration administration! logo design service

    ReplyDelete