1. Installation Guide
  2. Exporting and Importing Data between Oracle Database XE 11.2 and 18c
  3. Exporting and Importing Data for non-APEX Users

9.1 Exporting and Importing Data for non-APEX Users

This topic describes how to export and import data between your Oracle Database XE 11.2 and XE 18c databases when Oracle Application Express (APEX) was not used in your 11.2 XE database.

Exporting Data

  1. To export data from your 11.2 XE database, perform the following steps:

    1. Create a directory /dump_folder on the local file system for the DUMP_DIR directory object.

    2. Connect to the 11.2 XE database as user SYS using the SYSDBA privilege.

    3. Create directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user. 

      sqlplus / AS SYSDBA
SQL> CREATE DIRECTORY DUMP_DIR AS '/dump_folder';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

    4. Export data from your 11.2 XE database to the dump folder.

      expdp system/system_password full=Y EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\" directory=DUMP_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

  2. Deinstall Oracle Database XE 11.2 if installation of 18c XE is planned on the same system. See Deinstalling Oracle Database XE for more information

  3. Install Oracle Database XE 18c.

Importing Data

  1. To import data in your 18c XE database, perform the following steps:

    1. Connect to the 18c XE database as user SYS using the SYSDBA privilege.

    2. Create directory object DUMP_DIR and grant READ and WRITE privileges on the directory to the SYSTEM user. 

      sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=XEPDB1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/dump_folder';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

    3. Import data to the 18c XE database from the dump folder.

      impdp  system/system_password@localhost/xepdb1 full=Y EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\" directory=DUMP_DIR dumpfile=DB11G.dmp logfile=impdpDB11G.log
    You can ignore the following errors:

    • ORA-39083: Object type TABLESPACE:"SYSAUX" failed to create with error

    • ORA-31685: Object type USER:"SYS" failed due to insufficient privileges

    • ORA-39083: Object type PROCACT_SYSTEM failed to create with error

    • ORA-01917: user or role 'APEX_040000' does not exist

    • ORA-31684 "already exists" errors