Sunday, November 29, 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

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    Recent Comments