Friday, September 25, 2020
dbametrix
More
    Home Oracle DBA Tips Export and Import in Oracle XE

    Export and Import in Oracle XE

    This article will focus on exporting and importing Oracle XE database through the DATA PUMP programs using commands of Expdp and Impdp.

    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 -
    dbametrix

    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 -
    dbametrix
    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 -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to enable Archivelog

    This blog post explains how to enable archive log mode in Oracle database for newest versions.

    Shared vs Static Library Performance

    The article explains the benefits of a shared library and static library usage in application building for improving application performance.

    Migration methods of Oracle Database

    Blog post explains which tricks and methods are simple to perform migration of small and large Oracle database

    Recent Comments