Oracle Data Pump Example

How to export and import table using Data Pump

This example illustrates how to export a table from one schema and import it into another schema.

Create a directory on the host where database is running.

$ mkdir -p /scratch/dtpump

Use this dir to create directory in database. Data pump will export data to this folder.

Connect as sys or system user and create directory

create directory dtpump as '/scratch/dtpump';

--create a user and sample table to try export

create user rajiv identified by welcome1;
grant connect,resource to rajiv;
grant read,write on directory dtpump to rajiv;

Create Table and insert sample data. This table will be exported.

create table test( name varchar2(100));
insert into  test values('rajiv');
insert into  test values('venkat');
commit;

Enter bash

$bash

Set below environment variables

ORACLE_HOME=/scratch/oracledb/base/rdbms/12.1.0.2/;export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH;export PATH
NLS_LANG=AMERICAN_AMERICA.AL32UTF8;export NLS_LANG
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH

CON_STR=rajiv/welcome1@//hostname:1521/orcl.xyz.com; export CON_STR

Export:

expdp $CON_STR tables=TEST directory=dtpump dumpfile=TEST.dmp logfile=TEST.log

got below error message

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "RAJIV.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-01950: no privileges on tablespace 'USERS'

Solution:

Connect to sys or system user and run below command.

SQL> ALTER USER rajiv quota unlimited on users;

Try again:

bash-4.1$ expdp $CON_STR tables=TEST directory=dtpump dumpfile=TEST.dmp logfile=TEST.log

Export: Release 12.1.0.2.0 - Production on Mon Jan 25 07:32:28 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "RAJIV"."SYS_EXPORT_TABLE_01":  rajiv/********@//hostname.xyz.com:1521/orcl.xyz.com tables=TEST directory=dtpump dumpfile=TEST.dmp logfile=TEST.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "RAJIV"."TEST"                              5.531 KB       2 rows
Master table "RAJIV"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RAJIV.SYS_EXPORT_TABLE_01 is:
  /scratch/dtpump/TEST.dmp
Job "RAJIV"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jan 25 15:32:37 2016 elapsed 0 00:00:08

Import data 

Create another user to import table

create user rk identified by welcome1;
grant connect,resource to rk;
grant read,write on directory dtpump to rk;
ALTER USER rk quota unlimited on users;


Prepare a par file


bash-4.1$ cat dp1.par

directory=dtpump
dumpfile=TEST.dmp
remap_schema=rajiv:rk

Import

bash-4.1$ impdp rk/welcome1@//hostname.xyz.com:1521/orcl.xyz.com  parfile=dp1.par

Import: Release 12.1.0.2.0 - Production on Mon Jan 25 07:37:29 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.

Solution:
--connect as system or sys user and grant below privilege
grant IMP_FULL_DATABASE to rk;

Try again:

bash-4.1$ impdp rk/welcome1@//hostname.xyz.com:1521/orcl.xyz.com  parfile=dp1.par

Import: Release 12.1.0.2.0 - Production on Mon Jan 25 07:44:10 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "RK"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "RK"."SYS_IMPORT_FULL_01":  rk/********@//hostname.zyx.com:1521/orcl.zyz.com parfile=dp1.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RK"."TEST"                                 5.531 KB       2 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "RK"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 15:44:22 2016 elapsed 0 00:00:12