Check on Oracle version, Oracle Edition

=========================
General
=========================
1. How to tell if Oracle is Enterprise Edition (EE) or Standard Edition (SE)
2. How to view oracle Component versions.


=========================
Oracle SE or EE?
=========================

Option A. - check in V$VERSION

SQL> SELECT * FROM V$VERSION;

For Standard Edition the output is:

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production


For Enterprise Edition the output is:

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for Linux: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production

Option B. - check on the server, in file context.xml

/software/oracle/111/inventory/Components21/oracle.server/11.1.0.6.0>% 
less context.xml | grep  s_serverInstallType

For Standard Edition the output is:
<VAR NAME="s_serverInstallType" TYPE="String" DESC_RES_ID="s_serverInstallType_DESC" SECURE="F" VAL="SE" ADV="F" CLONABLE="F" USER_INPUT="CALC"/>

For Standard Edition the output is:
<VAR NAME="s_serverInstallType" TYPE="String" DESC_RES_ID="s_serverInstallType_DESC" SECURE="F" VAL="EE" ADV="F" CLONABLE="F" USER_INPUT="CALC"/>

=========================
Oracle components versions

=========================

SELECT * FROM SYS.PRODUCT_COMPONENT_VERSION;

PRODUCT                        VERSION                        STATUS

------------------------------ ------------------------------ ------------------------------

NLSRTL                         11.2.0.4.0                     Production

Oracle Database 11g            11.2.0.4.0                     64bit Production

PL/SQL                         11.2.0.4.0                     Production

TNS for Linux:                 11.2.0.4.0                     Production

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