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/password@localhost: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/password@localhost: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/password@localhost: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/password@localhost:1521/XEPDB1 full = Y directory = DATA_PUMP_DIR
dumpfile = xepdb1.dmp
logfile = xepdb1.log

Export database schemas:

EXPDP program syntax:

expdp username/password@localhost:port/DB SCHEMAS = schema1, schema2 
directory = DirectoryName dumpfile = file.dmp logfile = file.log

An Example:

expdp system/password@localhost:1521/XEPDB1 schemas = hr 
directory = DATA_PUMP_DIR
dumpfile = xepdb1.dmp
logfile = xepdb1.log

Import database schemas:

IMPDP program syntax:

impdp username/password@localhost:port/DB
SCHEMAS = scheme1, scheme2
directory = DirectoryName dumpfile = file.dmp
logfile = file.log

Example:

impdp system/password@localhost:1521/XEPDB1
schemas = hr directory = DATA_PUMP_DIR
dumpfile = xepdb1.dmp
logfile = xepdb1.log

When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

- Advertisement -