--
-- Connect to Oracle as system
--
conn system/&&password_user_system@&&db_name

--
-- Prompt for source and target schema names
--
accept input_source_schema prompt "Enter input_source_schema : ";
accept input_target_schema prompt "Enter input_target_schema : ";

set heading off
set pagesize 2000
set verify off
set term off
set feedback off

--
-- Truncate table
-- First create .sql file containing truncate table command, then
-- run that file.
--
spool transfer_data_between_schema_01.sql

select 'truncate table '||owner||'.'||table_name||';'
from all_tables
where owner = upper('&&input_target_schema')
order by table_name;

spool off

@transfer_data_between_schema_01.sql

-- 
-- Transfer data from source schema to target schema
-- First create .sql file containing command to copy the data, then
-- run that file.
--
spool transfer_data_between_schema_02.sql

select 'insert into &&input_target_schema..'||table_name||' select * from &&input_source_schema..'||table_name||';'
from all_tables
where owner = upper('&&input_target_schema')
order by table_name;

spool off

@transfer_data_between_schema_02.sql