Wednesday, March 3, 2021
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

    Recent Comments