How to Export and Import in Oracle XE via command line

One of the relevant tasks of a DBA is exporting and importing the database or some component of it, such as its schemas and tablespaces. For this purpose there are tools such as RMAN and the EXPDP and IMPDP programs.

This article will focus on exporting and importing through the DATA PUMP programs (Expdp and Impdp).

- Advertisement -

To execute expdp and impdp, you must have users with DBA privileges, such as SYSTEM.

How to Export a Oracle XE database:

EXPDP program syntax:

expdp username/[email protected]:port/DB full = value directory = DirectoryName dumpfile = file.dmp
logfile = file.log

Explanation of the parameters:

- Advertisement -

EXPDP is the name of the export program or command.
User corresponds to a user with DBA privileges such as SYSTEM.
Port is 1521 by default.
BD is the name of the XE database or a PDB if our server is Database XE 18c.
FULL indicates whether the entire database is exported (Y value).
DIRECTORY is assigned the name of the virtual directory.
DUMPFILE is assigned the name of the export file.
LOGFILE is assigned the name of the log file.

An Example:

expdp system/[email protected]:1521/XEPDB1 full = Y directory = DATA_PUMP_DIR dumpfile = xepdb1.dmp
logfile = xepdb1.log
table_exists_actions = replace

How to Import a Oracle XE database:

IMPDP program syntax:

impdp username/[email protected]:port/DB full = value
directory = DirectoryName dumpfile = file.dmp
logfile = file.log
table_exists_actions = replace

table_exists_actions is assigned relace to indicate that existing tables are replaced.

An Example:

impdp system/[email protected]:1521/XEPDB1 full = Y directory = DATA_PUMP_DIR
dumpfile = xepdb1.dmp
logfile = xepdb1.log

Export database schemas:

EXPDP program syntax:

expdp username/[email protected]:port/DB SCHEMAS = schema1, schema2 
directory = DirectoryName dumpfile = file.dmp logfile = file.log

An Example:

expdp system/[email protected]:1521/XEPDB1 schemas = hr 
directory = DATA_PUMP_DIR
dumpfile = xepdb1.dmp
logfile = xepdb1.log

Import database schemas:

IMPDP program syntax:

impdp username/[email protected]:port/DB
SCHEMAS = scheme1, scheme2
directory = DirectoryName dumpfile = file.dmp
logfile = file.log

Example:

impdp system/[email protected]:1521/XEPDB1
schemas = hr directory = DATA_PUMP_DIR
dumpfile = xepdb1.dmp
logfile = xepdb1.log

Consider Reading to these articles:

- Advertisement -