Convert Oracle database from Enterprise Edition to Standard Edition

Oracle 10g

Applies to:

Oracle Server – Enterprise Edition – Version: and later [Release: 10.2 and later ]
Oracle Server – Standard Edition – Version: and later [Release: 10.2 and later]

Goal :

How to convert from Enterprise Edition to Standard Edition?

Solution :

1) Make sure that you have all the database objects under the SYS schema in VALID status.

Please execute the following sql query:
spool objects.lst
set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status=’INVALID’ order by owner,object_type;
select owner,object_type,count(*) from dba_objects where status=’INVALID’ group by owner,object_type order by owner,object_type ;
spool off
If you found any objects in INVALID status, then make SYS schema objects are VALID then proceed with the next step.

2) Stop all the ongoing transactions and take a full (SYS level) export of the whole database.
3) Take cold backup of the ORACLE_HOME, database and central inventory.
4) Install the Oracle 10g SE software in a different ORACLE_HOME or deinstall
the EE and install the SE into the same ORACLE_HOME.
5) Create the database after installing the Oracle SE.
6) Import the exported DUMP file to the newly created SE database

impdp dumpfile=sys.dmp logfile=sysimp2.log TABLE_EXISTS_ACTION=SKIP exclude=user