Oracle Database 10g to 19c Schema Migration on Windows

Here is a quick link for schema migration in Linux (modify it to match with your windows server): https://www.support.dbagenesis.com/post/oracle-data-pump-expdp-impdp#viewer-albi5

Use Datapump to migrate schema from Oracle Database 10g into Oracle Database 19c.

Source Database

1. Check for the Source database associated tablespace and extract the DDL script.

SET LONG 20000 
SET LONGCHUNKSIZE 20000 
SET PAGESIZE 0 
SET LINESIZE 1000 
SET FEEDBACK OFF
SET VERIFY OFF
SET TRIMSPOOL ON
 
BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
 
--extract tablespaces. 
 
SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)
FROM   dba_tablespaces
WHERE  upper(tablespace_name) in ('TBS1','TBS2');

2. Create A Directory

E:\OracleExport\Schema

3. Create A Directory Object

Create an object inside SQLPLUS for the directory by executing the following command.

create directory directory_object_name as 'location’;

4. Export Database

expdp DIRECTORY=directory_object_name DUMPFILE=dump_file_namel.dmp LOGFILE=log_file_name.log schemas=schema_name

5. Copy the export dump file to the target location, or a location that is accessible by the target database.

In windows, we can use "MAP Network" drive to transfer the file within our network servers

Target Database

1. Create A Directory and transfer the dumpfile.

D:\OracleExport\Schema

2. Create the tablespaces which are associated with the source server using the extract on the source server

3. Create A Directory Object

Create an object inside SQL PLUS for the directory by executing the following command.

Create directory directory_object_name as 'location’;

4. Import Database

impdp DIRECTORY=directory_object_name DUMPFILE=dump_file_namel.dmp LOGFILE=log_file_name.log schemas=schema_name