How to copy one schema into another using SQL

Some times we don’t have the DBA access and need to copy data from one schema to another. You can perform that by executing migration SQL script in Oracle XE.

Step 1. Generate migration script.

SELECT table_name, ('Select '''||TABLE_NAME||''' as tbl from dual;'
 ||CHR(10)||'delete from <TARGET_SCHEMA>.'||TABLE_NAME||';'||CHR(10)||
'insert into <TARGET_SCHEMA>.'||TABLE_NAME||
' select * from <SOURCE_SCHEMA>.'||TABLE_NAME||'@<XE_CONNECTION>;') as GENERATED_SQL 
FROM all_tables where Owner='<SOURCE_SCHEMA>' Order By TABLE_NAME;

Step 2. Run Below script by updating Source and Target connection details

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
 connect system/password@xe   -- Where system is Oracle XE root user ID
drop PUBLIC DATABASE LINK <XE_CONNECTION>;
CREATE PUBLIC DATABASE LINK <XE_CONNECTION> CONNECT TO <SOURCE_USER_NAME> 
IDENTIFIED BY "PASSWORD" USING <SOURCE_CONNECTION_NAME>;
 
set sqlprompt '';
 
-- ----------- Echo ON / autocommit OFF for the logged in session
set autocommit off;
set echo off;
connect <TARGET_SCHEMA>/password@<XE_CONNECTION>
 
set echo on;
 
-- Add SQL generated from Step 1 --

Step 3. Execute the script. and commit the changes.

This will migrate data from one schema to another.