Export e import in data pump

Published by Alessandro Caredda on 16 June 202016 June 2020

In the article “Export and import with Data Pump,” I present the main methods for migrating data from one database to another.

One of the best solutions for migrating a database is through export and import with Data Pump.

This tool was introduced in Oracle 10g, and compared to the exp/imp versions, new management methods and performance enhancements have been introduced.

Data Pump export saves data logically in one or more files on the operating system.

The directory where the file is deposited must be registered in the database, so either we check for an existing directory with the following command:

select * from dba_directories;

Alternatively, it should be created with the following command:

create or replace directory DIR_PROVA as '/path/dump/dir/';

The second check that should always be performed is the size of the SCHEMA to be exported.

The check can be performed with the following query:

select owner,sum(bytes/1024/1024) MB from dba_segments where owner='&owner' group by owner;

Once you have identified the size of the schema and the directory where the export dump will be deposited, you need to verify the availability of space on the file system where the dump file and the log file will be created.

Let’s move on to the export command.

To compose the command that executes the data export, we must first call the expdp export procedure, setting: either the username and password, or /as sysdba, directory, dumpfile, and logfile.

expdp scott/scott@test directory=DIR_PROVA dumpfile=exp_tipoexport.dmp logfile=exp_tipoexport.logfile

This instruction must always be declared for all types of exports:

• Full database export
• Entire schema export
• Export of only the metadata of an entire schema
• Export of one or more tables

Let’s describe the various techniques:

Export full

The full database export makes a complete copy inside one or, in case parallelism is activated, multiple files. The first thing to check is the storage space; therefore, we need to determine the size of the database, which can be done with the following command:

select sum(bytes/1024/1024) MB from dba_segments;

Given the size of the database, let’s verify if we have enough space on the file system (FS). Keep in mind that the space in the database is fragmented, and the export applies compression to the data by default. Therefore, the file size will be reduced compared to the total size retrieved with the previous query.

Once space availability is confirmed, we can proceed with the export using the following command:

expdp "' / as sysdba'" directory=DIR_PROVA dumpfile=exp_full.dmp logfile=exp_full.log full=y

If you want to take advantage of parallelism and data compression, you need to execute the following command:

expdp "' / as sysdba'" directory=DIR_PROVA dumpfile=exp_full_%U.dmp logfile=exp_full.log full=y parallel=4 compression=ALL

Parallelism is set by the character %U on the final component of the name and by the instruction parallel= parallelism number, which is the number of files produced by the export. Parallelism should always be activated based on the number of CPUs and the Oracle version, which must be Enterprise Edition.

Exporting an entire schema

Exporting an entire schema is the most commonly used method when aligning data from a production environment to a test environment or when migrating one or more schemas from one database to another.

To check the size of a schema, you need to execute the following command:

select sum(bytes/1024/1024) MB from dba_segments where owner='PIPPO';

If there are multiple schemas:

select sum(bytes/1024/1024) MB from dba_segments where owner in ('PIPPO','PLUTO');

Once the spaces have been verified, we can proceed with the export using the following command:

expdp "' / as sysdba'" directory=DIR_PROVA dumpfile=exp_SCHEMA.dmp logfile=exp_full.log schemas=PIPPO

In this case, I exported the schemas PIPPO and PLUTO into the file exp_SCHEMAS.dmp. If I wanted to enable parallelism, I should use the same command but apply %U.dmp to the file name and set the parameter parallel=4 as in the following command:

expdp "' / as sysdba'" directory=DIR_PROVA dumpfile=exp_SCHEMAS_%U.dmp logfile=exp_full.log schema=PIPPO,PLUTO parallel=4

Export dei soli metadati di un intero schema

Exporting only the metadata of an entire schema involves using the “METADATA_ONLY” parameter in the export command. This option excludes data from the export and only exports the metadata of the specified schema objects. Here’s the command:

expdp "' / as sysdba'" directory=DIR_PROVA dumpfile=exp_SCHEMAS.dmp logfile=exp_full.log schema=PIPPO content=METADATA_ONLY

Exporting a single table

The export of one or more tables has an additional check. In addition to checking the size, which is done with the following command:

select table_name, bytes/1024/1024 MB from dba_segments where segment_name in ('TAB_PIPPO_1','TAB_PIPPO_2');

It is necessary to check if the tables have references to other tables, because if they do, the tables with the active relationship should also be exported.

set lines 300
spool disable_constraints.sql
select 'alter table '||owner||'.'||table_name||' enable novalidate constraint '||constraint_name||';' from dba_constraints where table_name ='&TABLE';
spool off

Once the checks are completed, we can execute the export with the following command:

expdp "' / as sysdba'" directory=DIR_PROVA dumpfile=exp_SCHEMAS.dmp logfile=exp_full.log tables=PIPPO.TAB_PIPPO_1,PIPPO.TAB_PIPPO_2

File dump transfer

When we perform the data export and have the export dump available, we need to transfer it to the server where the instance for import is located.

The transfer can be done on Linux and Unix systems using the command:

scp dumpfile username@ipaddress:/destination/path/

Alternatively, through the sftp protocol using the following command:

sftp username@ipaddress

cd /destination/path/

put dumpname

For MS systems, search for “Run” in Cortana search and type:

\\ipserver\c$

Perform the login and transfer the file.

Now let’s move on to the Import phase.

When performing data import, in addition to conducting checks related to space, we need to verify other types of checks which we’ll address.

Import del database

L’import full del database può essere effettuato nel caso in cui l’ambiente di test deve essere ricoperto completamente. In questo caso può essere effettuato l’import full.
L’unico controllo da eseguire è la presenza dei tablespace esattamente come nell’ambiente in cui sono stati esportati. Nel caso un cui i tablespace dovessero essere diversi occorre aggiungere la clausola REMAP_TABLESPACE=OLD_TBSP:NEW_TBSP

Controllare se sono presenti tablespace come in produzione e controllare le relative dimensioni.
Il controllo può essere effettuato attraverso la query riportata nell’articolo Query per vedere le dimensioni di tutte le TABLESPACE del database Oracle – https://www.scriptdba.com/query-per-vedere-le-dimensioni-di-tutte-le-tablespace-del-database-oracle/

Se non ci fosse abbastanza spazio estendere il/i tablespace.

L’import è eseguito con il seguente comando:

impdp "' / as sysdba'" directory=DIR_PROVA dumpfile=exp_full.dmp logfile=imp_full.log full=y

Eseguire il login e trasferire il file.

impdp "' / as sysdba'" directory=DIR_PROVA dumpfile=exp_full_%U.dmp logfile=imp_full.log full=y parallel=4

Importing an entire schema

Importing an entire schema may be required for data migration from one database to another or for refreshing data from a production environment to a test environment. During a data refresh from a production environment to a test environment, unless there are requests to save specific data in a particular table, you can execute the DROP schema command as follows:

drop user PIPPO cascade;

Additionally, you should perform a check on the space availability of the destination tablespaces.

This check can be done using the query provided in the article Query to see the sizes of all tablespaces in the Oracle database.

Finally, you can execute the import with the following command:

impdp "' / as sysdba'" directory=DIR_PROVA dumpfile=exp_SCHEMAS.dmp logfile=impd_full.log schema=PIPPO

Importing a set of tables

To perform the import of certain tables, it is necessary to have some information from the requester of the alignment.

It is necessary to verify if there are active constraints with the following query:

set lines 300
spool disable_constraints.sql
select 'alter table '||owner||'.'||table_name||' disable novalidate constraint '||constraint_name||';' from dba_constraints where table_name ='&TABLE';
spool off

Additionally, if a table already exists, you need to add the TABLE_EXISTS_ACTION clause, which accepts 4 options:

• APPEND: Appends the records by duplicating them
• REPLACE: Drops the table and recreates it
• SKIP: Skips the table and moves on to the next object
• TRUNCATE: Deletes the records of the table and reinserts them

Once the active constraints are disabled and the tablespace in which the table resides is checked, you can execute the import with the following command:

expdp "' / as sysdba'" directory=DIR_PROVA dumpfile=exp_SCHEMAS.dmp logfile=exp_full.log tables=PIPPO.TAB_PIPPO_1,PIPPO.TAB_PIPPO_2 TABLE_EXISTS_ACTION=TRUNCATE

Once the import is complete, re-enable the constraints by modifying the commands in the spool file generated before the import, changing the disable statement to enable.

These are the main methods of export and import in data pump.

Stay updated on our activities by joining our community (make sure to like our pages, let’s connect, and stay updated on each of our new guides!):
Facebook
Youtube
Linkedin

For those who haven’t done so yet, we recommend saving this site among your bookmarks, so you’ll always have our solutions to the most common database problems at your fingertips, especially Oracle databases.