Migrate Tables Data from schema to schema

====================================
General
====================================
Example of how to move data from schema to schema.
This includes creating tables using 'CREATE TABLE AS SELECT' and DBMS_METADATA.get_ddl.


====================================
Code
====================================

Step A. - Create DDL and INSERT SQLs

--Generate DDL for Partitioned Tables script
SET LONG 5000
SET PAGESIZE 0
SET LINESIZE 400
SET FEEDBACK OFF
COL ddl_cmd for A400 WORD_WRAP

spool cre_FACT_ROAMER_SCENARIO.sql
SELECT 
DBMS_METADATA.get_ddl('TABLE','FACT_ROAMER_SCENARIO') as ddl_cmd
FROM DUAL;
spool off

spool cre_FACT_ROAMER_CAMPAIGNS.sql
SELECT 
DBMS_METADATA.get_ddl('TABLE','FACT_ROAMER_CAMPAIGNS') as ddl_cmd
FROM DUAL;
spool off

--Generate Populate Partitioned Tables script
SET LONG 10000
SET PAGESIZE 0
SET LINESIZE 400
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
COL sql_str FOR A300

spool populate_FACT_ROAMER_SCENARIO.sql
SELECT 'INSERT /*+ APPEND */ INTO SCHEMA_B.FACT_ROAMER_SCENARIO SELECT * FROM SCHEMA_A.FACT_ROAMER_SCENARIO PARTITION('||partition_name||') WHERE ROWNUM < 1000000;'||CHR(10)||'commit;' AS sql_str 
FROM USER_TAB_PARTITIONS 
WHERE table_name = 'FACT_ROAMER_SCENARIO';
spool off

spool populate_FACT_ROAMER_CAMPAIGNS.sql
SELECT 'INSERT /*+ APPEND */ INTO SCHEMA_B.FACT_ROAMER_CAMPAIGNS SELECT * FROM SCHEMA_A.FACT_ROAMER_CAMPAIGNS PARTITION('||partition_name||') WHERE ROWNUM < 1000000;'||CHR(10)||'commit;' AS sql_str 
FROM USER_TAB_PARTITIONS 
WHERE table_name = 'FACT_ROAMER_CAMPAIGNS';
spool off

--Generate Big Non Partitioned Tables using CREATE AS SELECT script
SET LONG 10000
SET PAGESIZE 0
SET LINESIZE 400
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
COL sql_str FOR A300

spool create_big_tables.sql
SELECT 'CREATE TABLE SCHEMA_B.'||SEGMENT_NAME||' AS SELECT * FROM SCHEMA_A.'||SEGMENT_NAME||' WHERE 1=2' as sql_str
from dba_segments 
WHERE OWNER = 'SCHEMA_A' 
  AND bytes > 100000 
  AND SEGMENT_TYPE = 'TABLE';
spool off

--Generate Populate Non Partitioned Tables script
spool populate_big_tables.sql
SELECT 'INSERT /*+ APPEND */ INTO SCHEMA_B.'||SEGMENT_NAME||' SELECT * FROM SCHEMA_A.'||SEGMENT_NAME||' WHERE ROWNUM < 1000000;'||CHR(10)||'commit;' 
from dba_segments 
WHERE OWNER = 'SCHEMA_A' 
  AND bytes > 100000 
  AND SEGMENT_TYPE = 'TABLE';
spool off


--Generate TRUNCATE SQLs - for Partitioned Tables
SET LONG 10000
SET PAGESIZE 0
SET LINESIZE 400
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
COL sql_str FOR A300

spool truncate_partitiones.sql
SELECT 'ALTER TABLE '||table_name||' TRUNCATE PARTITION '||partition_name||';' as sql_str  
FROM USER_TAB_PARTITIONS 
WHERE table_in ('.FACT_ROAMER_CAMPAIGNS','FACT_ROAMER_SCENARIO');

--Generate TRUNCATE SQLs - for Non Partitioned Tables
spool truncate_tables.sql
SELECT 'TRUNCATE TABLE '||table_name||';' as sql_str 
FROM USER_TABLES;

Step B. - Execute Generated SQLs
sqlplus  SCHEMA_B/SCHEMA_B@orainst

--For Non Partitioned Tables
@create_big_tables.sql
@populate_big_tables.sql

--For Partitioned Tables
@cre_FACT_ROAMER_SCENARIO.sql

@cre_FACT_ROAMER_SCENARIO.sql

@populate_FACT_ROAMER_SCENARIO.sql

@populate_FACT_ROAMER_CAMPAIGNS.sql