Exporting Data

To export data from your 21c XE database:

1. Create a C:\Temp\dump1 directory for the DUMP_DIR directory object.

   mkdir C:\Temp\dump1
   

2. Perform the following steps for each pluggable database (PDB). The steps in this section are for the PDB xepdb1.

   1. Set the ORACLE_HOME and ORACLE_SID environment variables.

      set ORACLE_SID=XE
      set ORACLE_HOME=C:\oldexpxe\dbhomeXE

   2. Connect to the 21c XE database as user SYS using the SYSDBA privilege, and switch the container to xepdb1.

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

      
      SQL> ALTER SESSION SET CONTAINER=xepdb1;
      SQL> CREATE DIRECTORY DUMP_DIR6 AS 'C:\TEMP\dump1';
      SQL> GRANT read, write ON DIRECTORY DUMP_DIR6 TO SYSTEM;
      

   4. Export data from your 21c XE PDB xepdb1 to the dump folder.

      %ORACLE_HOME%/bin/expdp system/system_password@dbhost.example.com:1521/xepdb1 full=Y directory=DUMP_DIR6 dumpfile=DB10G.dmp logfile=expdpDB10G.log
      

      
      OUTPUT:Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
      ******************************************************************************
      Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
        C:\TEMP\DUMP1\DB10G.DMP
      Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at .*

      Note:

      • Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
      • The default PDB name created is xepdb1 in 21c and freepdb1 in 23ai.

3. Deinstall Oracle Database 21c XE if you plan to install 23ai Free on the same system. See, Deinstalling Oracle Database XE for more information.

4. Install Oracle Database 23ai Free.

Importing Data

To import data to your Oracle Database 23ai Free, perform the following steps for each PDB. The steps in this section are for importing data from PDB xepdb1 to freepdb1.

1. Set the ORACLE_HOME and ORACLE_SID environment variables.

   set ORACLE_SID=FREE
   set ORACLE_HOME=C:\newimpfree\dbhomeFree

2. Connect to the 23ai Free database as user SYS using the SYSDBA privilege and switch the container to freepdb1.

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

   
   SQL> ALTER SESSION SET CONTAINER=freepdb1;
   SQL> CREATE DIRECTORY DUMP_DIR6 AS 'C:\Temp\dump1';
   SQL> GRANT read, write ON DIRECTORY DUMP_DIR6 TO SYSTEM;
   

4. Import data to the 23ai Free PDB freepdb1 from the dump folder created during the export operation.

   %ORACLE_HOME%/bin/impdp system/system_password@dbhost.example.com:1521/freepdb1 full=Y directory=DUMP_DIR6
    dumpfile=DB10G.dmp logfile=impdb23ai_freepdb1.log

   Note:

   • Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
   • The default PDB name created is xepdb1 in 21c and freepdb1 in 23ai.

Ignore the following errors:

• ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
• ORA-31684: Object type TABLESPACE:"TEMP" already exists
• ORA-31684: Object type TABLESPACE:"USERS" already exists
• ORA-39083: Object type PROC_SYSTEM_GRANT failed to create with error:
• ORA-29393: user EM_EXPRESS_ALL does not exist or is not logged on

Failing sql is:
BEGIN 
dbms_resource_manager_privs.grant_system_privilege(grantee_name => '"EM_EXPRESS_ALL"', admin_option => TRUE);COMMIT; END; 
ORA-31684: Object type DIRECTORY:"DUMP_DIR6" already exists