Upgrade Oracle Database from Oracle Standard Edition to Oracle Enterprise Edition

How to Convert/Upgrade an Oracle Database from Oracle Standard to Oracle Enterprise Edition

 The following steps should be done to convert your Oracle Standard Edition database to Oracle Enterprise Edition: 

  1. Backup the database
  2. UnInstall or De-install the Oracle Standard Edition software 
  3. Install the Oracle Enterprise Edition software  
  4. If you have an existing database, point your ORACLE_SID to this pre-existing database
  5. Startup the database
  6. Run the "catalog.sql" and "catproc.sql" scripts (This make sures all objects for Oracle Enterprise Edition are up to date)

That is pretty much it Oracle Enterprise Edition and ready to pay thoose yearly mainteance fees! 

Oracle Archive Log Mode Status or Status of Archive Log Subsystem

One can  can check to see if Oracle is in archive log mode or has logging turned on.  This means that the database is able to recover forward or backward in time.  Sometimes referred to as point in time recovery

Oracle Archive Log Mode Status or Status of Archive Log Subsystem

ARCHIVE LOG LIST;

Check the Oracle log mode of  the databases

SELECT name, log_mode FROM v$database;

Check the Oracle Archive mode in an  instance 

SELECT archiver FROM v$instance;

What version of Oracle is running

What version of Oracle is running?

select * from V$VERSION;

Another Option is to use the product table

set lines 200 
column product format a50
column version format a15
column status format a20
SELECT * FROM product_component_version ;

These are examples of running the  SELECT * FROM V$VERSION;  from different versions Oracle would be 

 

The resultant banner will show the current Oracle Database Edition if the Enterprise Edition is installed.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

The resultant banner will show the current Oracle Database Edition if the Standard Edition is installed.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

The resultant banner will show the current Oracle Database Edition if the Personal Edition is installed.

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Drop Oracle Table or Object and Don’t Store In The RecycleBin

 How does one drop an Oracle Table without putting it in the recycle bin.   
  drop   purge;

	or drop and object

  drop   purge;

				

			
						

				Empty Oracle Recycle Bin
			

								


				

			
How does one purge, empty, clean out the Oracle Recyclebin or Recycle Bin?


A new feature in Oracle 10g Release 2 was the Reycycle Bin. The recycle bin in the Oracle database is where all dropped objects reside. Underneath the covers the object are taking up the same space when they were created.   



 "DROP TABLE mytable", it doesn't really drop it. It instead renames it to e.g.: BIN$67815682942.


 Dropped objects are not deleted they are just rename with a prefix of BIN$$. One can get access to the data in a dropeed table or even use a Flashback Query if you have this feature enabled on your version of Oracle.


To completely remove a table from the system and not keep in recycle been use PURGE TABLE command. So if table is called mytable.


The syntax would be PURGE TABLE mytable;



Other ways to purge the recycle bin are:




PURGE TABLE mytable; (purges mytable from system and recycle bin)

PURGE INDEX myindex; (purges myindex from system and recycle bin ) PURGE recyclebin;




Purge all object in recyclebin PURGE dba_recyclebin; (purge all objects / only sysdba can do this command)


To drop and purge a table in one command it would be: DROP TABLE mytable PURGE; So thats It.

					

		
		
	

				

			
						

				Killing Oracle Sessions
			

								


				

			
Finding a Oracle session to kill starts with First finding the offending Query. Than kill it with the alter system kill command. 

	



SET LINESIZE 100 
COLUMN spid FORMAT A10 
COLUMN username FORMAT A10 
COLUMN program FORMAT A45 
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program 
FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id 
WHERE s.type != 'BACKGROUND'; 
INST_ID SID SERIAL# SPID USERNAME PROGRAM 

---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 69 99 4879 TEST billbprog@my.telly 



ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; or ALTER SYSTEM KILL SESSION '69,99' IMMEDIATE;