Oracle Data pump

Introduction

On oracle database, we can make backup and recovery using either physical or logical methods. RMAN backup and recovery is one of the physical backup & recovery methods. And we can also make logical backup and recovery using EXPORT & IMPORT utilities of oracle database. Starting from oracle 10g, a new feature of export/ import utilities that supports data pump technology (expdp & impdp). The old version or the standard export & import utilities are also called exp and imp respectively. Data pump comprises of the features of the standard import/export utilities and have new features that enables us to efficiently backup/restore and move data among different environments. We can import and export specific table, schema ,tablespace and the entire database using data pump technology, expdp and impdp. On this topic, we will going to see some practical examples on how to export and import a schema, drop a schema, and then re-create it using data pump technology. We will also do the same on a table and tablespace.

Exporting schema

Before running the export and import jobs, we shall login to the database through SYS user, using SQL*PLUS command-line tool. And then we will create database directory and grant read & write privileges on the directory object to the database schema running the export. For this practice, we assume that there is a schema named mereba and the schema comprises tables, views and other database objects inside it.

Create a database directory object named ‘data_pump’

And then grant access to schema named ‘mereba‘ on the directory created above.

SQL> grant write, read on directory data_pump to mereba;

Grant succeeded.

Finally, run the expdp utility to export the schema.

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 2 21:21:39 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL> $expdp system/orcl directory=data_pump schemas=mereba dumpfile=mereba.dmp logfile=mereba.log

Export: Release 12.2.0.1.0 - Production on Wed Mar 2 21:22:30 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=data_pump schemas=mereba dumpfile=mereba.dmp logfile=mereba.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "MEREBA"."TBL_CUSTOMER" 6.859 KB 3 rows
. . exported "MEREBA"."EMP_SAL" 5.914 KB 1 rows
. . exported "MEREBA"."MEREBA" 5.492 KB 1 rows
. . exported "MEREBA"."STTMS_CUST_ACCOUNT" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
D:\DATAPUMP_BACKUP\MEREBA.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 2 21:23:40 2022 elapsed 0 00:01:10

SQL>

Exporting table

The process is the same as exporting of schema that we have already done in the SQL commands above. But when we export a schema, we are exporting all the database objects inside it. And when we are talking about exporting tables, we mean exporting of one or more tables only. Here below is a data pump example of exporting a single table, tbl_customer .

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 2 21:33:40 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL> $expdp system/orcl directory=data_pump tables=mereba.tbl_customer dumpfile=tbl_customer.dmp logfile=tbl_customer.log;

Export: Release 12.2.0.1.0 - Production on Wed Mar 2 21:33:48 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=data_pump tables=mereba.tbl_customer dumpfile=tbl_customer.dmp logfile=tbl_customer.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "MEREBA"."TBL_CUSTOMER" 6.859 KB 3 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
D:\DATAPUMP_BACKUP\TBL_CUSTOMER.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed Mar 2 21:34:22 2022 elapsed 0 00:00:32

SQL>

How to Export tablespace

A tablespace is a logical container for physical database objects. And a tablespace can contains one or more schema objects. When we are talking about exporting a tablespace, we mean exporting of one or more schema objects that the tablespace might encompasses. Here below is a data pump example of exporting a tablespace named users.

SQL> $ expdp mereba/mereba directory=data_pump dumpfile=users.dmp tablespaces=users

Export: Release 12.2.0.1.0 - Production on Wed Mar 2 20:36:14 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": SYSTEM/******** directory=data_pump dumpfile=users.dmp tablespaces=users
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "MEREBA"."TBL_CUSTOMER" 6.859 KB 3 rows
. . exported "MEREBA"."EMP_SAL" 5.914 KB 1 rows
. . exported "MEREBA"."MEREBA" 5.492 KB 1 rows
. . exported "AMAN"."AMAN" 0 KB 0 rows
. . exported "MEREBA"."STTMS_CUST_ACCOUNT" 0 KB 0 rows
Master table "MEREBA"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MEREBA.SYS_EXPORT_TABLESPACE_01 is:
D:\DATAPUMP_BACKUP\USERS.DMP
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed Mar 2 20:36:52 2022 elapsed 0 00:00:37

SQL>

How to import schema

Here below is a data pump example on how to drop a schema and recreate it using impdp utility, from the export dump file of a schema created previously .

--Dropping a schema named mereba
D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 2 20:42:08 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL> drop user mereba;
drop user mereba
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'MEREBA'

SQL> drop user mereba cascade;

User dropped.
SQL>


---Re-creating schema mereba from dumpfile mereba.dmp create previously.
D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 2 20:48:17 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL> $impdp system/orcl directory=data_pump schemas=mereba dumpfile=mereba.dmp logfile=mereba.log

Import: Release 12.2.0.1.0 - Production on Wed Mar 2 20:48:48 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=data_pump schemas=mereba dumpfile=mereba.dmp logfile=mereba.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MEREBA"."TBL_CUSTOMER" 6.859 KB 3 rows
. . imported "MEREBA"."EMP_SAL" 5.914 KB 1 rows
. . imported "MEREBA"."MEREBA" 5.492 KB 1 rows
. . imported "MEREBA"."STTMS_CUST_ACCOUNT" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Mar 2 20:49:25 2022 elapsed 0 00:00:36

SQL>

Now, to confirm whether the schema is recreated or not let’s run the following SQL statement.

SQL> conn mereba/mereba@orcl
Connected.
SQL>

Importing table

Let’s drop a table named ‘tbl_customer’ and re-create it from the dumpfile ‘TBL_CUSTOMER.dmp’ created previously.

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 2 20:55:06 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn mereba/mereba@orcl
Connected.

---Drop table tbl_customer
SQL> drop table tbl_customer;

Table dropped.

SQL>

---Import table tbl_customer
D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 2 20:59:34 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL> $impdp system/orcl directory=data_pump tables=mereba.tbl_customer dumpfile=tbl_customer.dmp

Import: Release 12.2.0.1.0 - Production on Wed Mar 2 20:59:48 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=data_pump tables=mereba.tbl_customer dumpfile=tbl_customer.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MEREBA"."TBL_CUSTOMER" 6.859 KB 3 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Mar 2 20:59:59 2022 elapsed 0 00:00:10
SQL>

And again to confirm whether the table is re-created or not and let’s run the following SQL statement.

SQL> select * from mereba.tbl_customer;

CUST_NO CUSTOMER_T CUST_NAME COUNTRY SWIFT_CODE
---------- ---------- ------------------------- --------------- ------------
########## I Messi John Dave USA
########## C Mereba Technology UK
########## B Swiss Bank Switzerland
SQL>

Using a parameter file

The parameter file enables us to store all the commands required to export/import database objects in a single .par parameter file. And the expdp/imdp utility references the parameter file using the PARFILE command. Below is a data pump example of exporting a table using parameter file. Let’s create a tbl_customer.par parameter file, for instance on my machine located on ‘D:\oracle\tbl_customer.par’ and let the par file has the following commands.

---Contents of the tbl_customer.par file
userid=system/orcl
directory=data_pump
dumpfile=table_exp.dmp
logfile=table_exp.log
tables=mereba.tbl_customer
reuse_dumpfiles=y

Now, let’s run the expdp job to export the table.

As you can see also in the query above, mereba.tbl_customer table is exported successfully.

Renaming a table

While importing a table into oracle database, data pump has an option of renaming a table. We use REMAP_TABLE parameter to rename a table while importing. Here below is a data pump example code that imports tbl_customer table of mereba schema to mereba schema of table customer:

SQL> $ impdp system/orcl directory=data_pump dumpfile=tbl_customer.dmp tables=mereba.tbl_customer remap_table=mereba.tbl_customer:customer

Import: Release 12.2.0.1.0 - Production on Sun Mar 6 16:00:42 2022

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=data_pump dumpfile=tbl_customer.dmp tables=mereba.tbl_customer remap_table=mereba.tbl_customer:customer
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MEREBA"."CUSTOMER" 6.859 KB 3 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed successfully at Sun Mar 6 16:00:54 2022 elapsed 0 00:00:10

Data pump