How to export / import schema with Data Pump

Posted on by admin

There are several export/import modes. These are listed in the following section.

  • Full Mode
  • Schema Mode
  • Table Mode
  • Tablespace Mode
  • Transportable Tablespace Mode

In this article, I am going to explain how to export and import schemas using data pump utility. First, let’s create a directory that we use during export. Our dump and log files will be exported to this directory. Before creating a new directory, make sure that it has already been created at OS level. Because, CREATE DIRECTORY command does not create a new directory in the operating system, it points the existing directory. 

CREATE DIRECTORY DATAPUMP_DIR AS 'C:\datapump';

After creating the directory we need to give the necessary privileges to the user who wants to perform the export.

GRANT READ, WRITE ON DIRECTORY DATAPUMP_DIR TO HR;

If the user wants to export table, tablespace or scheme of other users, then DATAPUMP_EXP_FULL_DATABASE privilege must be given to the user. It should also be given for full database export. In the same way, DATAPUMP_IMP_FULL_DATABASE role should be granted for usage of import.

GRANT DATAPUMP_IMP_FULL_DATABASE TO HR;

Before export, if you want to learn how many space you have to be allocate, then you have to use ESTIMATE_ONLY parameter.

C:\>expdp SCHEMAS=HR DIRECTORY=DATAPUMP_DIR LOGFILE=EXP_HR_LOG.LOG ESTIMATE_ONLY=Y

Export: Release 11.2.0.1.0 - Production on Pzt Kas 23 21:29:31 2020

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

Kullan²c² Ad²: hr
Parola:

Ba­lan²lan: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
"HR"."SYS_EXPORT_SCHEMA_01" ba■lat²l²yor:  hr/******** SCHEMAS=HR DIRECTORY=DATA
PUMP_DIR LOGFILE=EXP_HR_LOG.LOG ESTIMATE_ONLY=Y
BLOCKS y÷ntemi kullan²larak devam eden oran tahmini...
SCHEMA_EXPORT/TABLE/TABLE_DATA nesne t³r³ i■leniyor
.  tahmin edilen "HR"."COUNTRIES"                               64 KB
.  tahmin edilen "HR"."DEPARTMENTS"                             64 KB
.  tahmin edilen "HR"."EMPLOYEES"                               64 KB
.  tahmin edilen "HR"."JOB_HISTORY"                             64 KB
.  tahmin edilen "HR"."JOBS"                                    64 KB
.  tahmin edilen "HR"."LOCATIONS"                               64 KB
.  tahmin edilen "HR"."REGIONS"                                 64 KB
BLOCKS y÷ntemi kullan²larak yap²lan toplam tahmin: 448 KB
"HR"."SYS_EXPORT_SCHEMA_01" i■i 21:29:58 konumunda ba■ar²yla tamamland²

So, as it can be seen from the output the estimated size is 448 KB for HR schema. Let’s perform export.

C:\>expdp SCHEMAS=HR DIRECTORY=DATAPUMP_DIR DUMPFILE=EXP_HR.DMP LOGFILE=EXP_HR_L
OG.LOG

Export: Release 11.2.0.1.0 - Production on Pzt Kas 23 21:36:45 2020

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

Kullan²c² Ad²: hr
Parola:

Ba­lan²lan: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
"HR"."SYS_EXPORT_SCHEMA_01" ba■lat²l²yor:  hr/******** SCHEMAS=HR DIRECTORY=DATA
PUMP_DIR DUMPFILE=EXP_HR.DMP LOGFILE=EXP_HR_LOG.LOG
BLOCKS y÷ntemi kullan²larak devam eden oran tahmini...
SCHEMA_EXPORT/TABLE/TABLE_DATA nesne t³r³ i■leniyor
BLOCKS y÷ntemi kullan²larak yap²lan toplam tahmin: 448 KB
SCHEMA_EXPORT/USER nesne t³r³ i■leniyor
SCHEMA_EXPORT/SYSTEM_GRANT nesne t³r³ i■leniyor
SCHEMA_EXPORT/ROLE_GRANT nesne t³r³ i■leniyor
SCHEMA_EXPORT/DEFAULT_ROLE nesne t³r³ i■leniyor
SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA nesne t³r³ i■leniyor
SCHEMA_EXPORT/SEQUENCE/SEQUENCE nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/TABLE nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/INDEX/INDEX nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/COMMENT nesne t³r³ i■leniyor
SCHEMA_EXPORT/PROCEDURE/PROCEDURE nesne t³r³ i■leniyor
SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE nesne t³r³ i■leniyor
SCHEMA_EXPORT/VIEW/VIEW nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/TRIGGER nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS nesne t³r³ i■leniyor
. . "HR"."COUNTRIES"                            6.375 KB      25 sat²r d²■a akta
r²ld²
. . "HR"."DEPARTMENTS"                          7.015 KB      27 sat²r d²■a akta
r²ld²
. . "HR"."EMPLOYEES"                            16.81 KB     107 sat²r d²■a akta
r²ld²
. . "HR"."JOB_HISTORY"                          7.054 KB      10 sat²r d²■a akta
r²ld²
. . "HR"."JOBS"                                 6.992 KB      19 sat²r d²■a akta
r²ld²
. . "HR"."LOCATIONS"                            8.273 KB      23 sat²r d²■a akta
r²ld²
. . "HR"."REGIONS"                              5.484 KB       4 sat²r d²■a akta
r²ld²
"HR"."SYS_EXPORT_SCHEMA_01" ana tablosu ba■ar²yla y³klendi/y³klemesi kald²r²ld²
******************************************************************************
HR iin d÷k³m dosyas². SYS_EXPORT_SCHEMA_01:
  C:\DATAPUMP\EXP_HR.DMP
"HR"."SYS_EXPORT_SCHEMA_01" i■i 21:37:32 konumunda ba■ar²yla tamamland²

You can also check the log file from the location that we created earlier.

C:\>cd datapump

C:\datapump>dir
 Volume in drive C has no label.
 Volume Serial Number is 4EB4-6976

 Directory of C:\datapump

23.11.2020  21:36    <DIR>          .
23.11.2020  21:36    <DIR>          ..
23.11.2020  21:37           425.984 EXP_HR.DMP
23.11.2020  21:37             2.567 EXP_HR_LOG.LOG
               2 File(s)        428.551 bytes
               2 Dir(s)  71.180.779.520 bytes free

In addition, you can use parameter file. It allows you to specify export / import parameters within a file. Now, let’s import the HR schema with a new name using with REMAP_SCHEMA parameter. I created a parameter file with the below content. REMAP_SCHEMA parameter loads all objects from the source schema into a target schema. If the schema you are remapping to does not already exist, then it creates a new schema for the specified name.

The name of the parameter file is HR_SCHEMA.PAR. Now, Let’s perform import with PARFILE parameter.

C:\datapump>impdp PARFILE=HR_SCHEMA.PAR

Import: Release 11.2.0.1.0 - Production on Pzt Kas 23 23:46:33 2020

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

Kullan²c² Ad²: hr
Parola:

Ba­lan²lan: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
"HR"."SYS_IMPORT_SCHEMA_01" ana tablosu ba■ar²yla y³klendi/y³klemesi kald²r²ld²
"HR"."SYS_IMPORT_SCHEMA_01" ba■lat²l²yor:  hr/******** PARFILE=HR_SCHEMA.PAR
SCHEMA_EXPORT/USER nesne t³r³ i■leniyor
SCHEMA_EXPORT/SYSTEM_GRANT nesne t³r³ i■leniyor
SCHEMA_EXPORT/ROLE_GRANT nesne t³r³ i■leniyor
SCHEMA_EXPORT/DEFAULT_ROLE nesne t³r³ i■leniyor
SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA nesne t³r³ i■leniyor
SCHEMA_EXPORT/SEQUENCE/SEQUENCE nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/TABLE nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/TABLE_DATA nesne t³r³ i■leniyor
. . "HR2"."COUNTRIES"                           6.375 KB      25 sat²r ie aktar
²ld²
. . "HR2"."DEPARTMENTS"                         7.015 KB      27 sat²r ie aktar
²ld²
. . "HR2"."EMPLOYEES"                           16.81 KB     107 sat²r ie aktar
²ld²
. . "HR2"."JOB_HISTORY"                         7.054 KB      10 sat²r ie aktar
²ld²
. . "HR2"."JOBS"                                6.992 KB      19 sat²r ie aktar
²ld²
. . "HR2"."LOCATIONS"                           8.273 KB      23 sat²r ie aktar
²ld²
. . "HR2"."REGIONS"                             5.484 KB       4 sat²r ie aktar
²ld²
SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/INDEX/INDEX nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/COMMENT nesne t³r³ i■leniyor
SCHEMA_EXPORT/PROCEDURE/PROCEDURE nesne t³r³ i■leniyor
SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE nesne t³r³ i■leniyor
SCHEMA_EXPORT/VIEW/VIEW nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/TRIGGER nesne t³r³ i■leniyor
SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS nesne t³r³ i■leniyor
"HR"."SYS_IMPORT_SCHEMA_01" i■i 23:47:10 konumunda ba■ar²yla tamamland²

As in our example, if the import creates a new schema, you will need to assign a new password for it once this process is complete.

ALTER USER schema_name IDENTIFIED BY new_password

I hope you found this post helpful.

Posted in DBA
Leave a Reply

Your email address will not be published. Required fields are marked *