We need to transfer Oracle Database 10g to Oracle Database 19c, both of which are running on Windows Server. We're trying to move the schema to another Windows Server with a new installation of 19c in order to test the possibilities and issues before moving the database. I'm trying to figure out how to load a 10g schema onto a 19c server.
top of page
bottom of page
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