Half Automated procedure for Migration from Oracle EE to Oracle SE

Here are the steps to convert from Oracle Enterprise Edition to Oracle Standard Edition.

Step 0 - Take an export, using expdp, from Enterprise Edition Schema.


Step 1 - Before import: disable constraints, disable triggers, drop sequences.

@before_import.sql

before_import.sql contents

SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT OFF 
SET LINESIZE 400
SET PAGESIZE 0
spool disable_triggers.sql
select 'alter trigger '|| trigger_name || ' disable  '  || ';' from user_triggers;
spool off
spool disable_constraints.sql
select 'alter table '|| table_name || ' disable constraint ' || constraint_name || ';' from user_constraints where CONSTRAINT_TYPE='R';
spool off
spool drop_sequences.sql
select 'drop sequence '||sequence_name ||';' from user_sequences;
spool off

@disable_triggers.sql
@disable_constraints.sql
@drop_sequences.sql
EXIT;


Step 2 - Import data and Sequences.

impdp user/pass@orainst parfile=param.prm

param.prm contents

directory=IG_EXP_DIR 
dumpfile=my_file.dmp
logfile=my_file.log
table_exists_action=truncate 
content=data_only
EXCLUDE=TABLE:"IN ('TABLE_A', 'TABLE_A')"

impdp user/pass@igt directory=MY_EXP_DIR dumpfile=my_file.dmp logfile=my_seq.log include=sequence

Step 3 - After import: enable constraints, enable triggers, compile objects.
@after_import.sql

after_import.sql contents

SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT OFF 
SET LINESIZE 400
SET PAGESIZE 0
spool enable_triggers.sql
select 'alter trigger '|| trigger_name || ' enable  '  || ';' from user_triggers;
spool off
spool enable_constraints.sql
select 'alter table '|| table_name || ' enable constraint ' || constraint_name || ';' from user_constraints where CONSTRAINT_TYPE='R';
spool off

@enable_triggers.sql
@enable_constraints.sql


SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT OFF 
SET LINESIZE 400
SET PAGESIZE 0

spool compile_objects.sql
SELECT object_type, 'ALTER '||
       DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||
       ' ' ||owner||'.'||object_name||' COMPILE '||
       decode(object_type,'PACKAGE BODY','BODY','')||';' "ALTER ... COMPILE"
FROM DBA_OBJECTS
WHERE status='INVALID'
  AND object_type NOT IN('JAVA CLASS','JAVA SOURCE')
  AND (object_type != 'SYNONYM' AND OWNER!='PUBLIC')
  AND owner <> 'SYS'

UNION ALL

SELECT object_type, 'ALTER '||object_type||' "'||owner||'.'||object_name||'" COMPILE; '
FROM  DBA_OBJECTS 
WHERE status='INVALID' 
  AND object_type in('JAVA CLASS','JAVA SOURCE')

UNION ALL

SELECT object_type, 'ALTER ' || OWNER || ' ' || OBJECT_TYPE ||' '||OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS='INVALID'
  AND OBJECT_TYPE='SYNONYM'
  AND owner = 'PUBLIC'
ORDER BY 1,2;

spool off

@compile_objects.sql
EXIT;


Step 4 - Remove temporary files.

rm_temp_files.sh

rm_temp_files content

#!/bin/bash
rm disable_triggers.sql
rm disable_constraints.sql
rm drop_sequences.sql

rm enable_triggers.sql

rm enable_constraints.sql

rm compile_objects.sql