How to install and deinstall Oracle Spatial and Oracle Multimedia

The following advice is an excerpt from a Service Request with Oracle support services, on how to administer Spatial and Multimedia:

To remove Spatial:

 connect / as sysdba 
 drop user MDSYS cascade; 

To remove Multimedia:

 connect / as sysdba 
 @?/rdbms/admin/catcmprm.sql ORDIM 

You can reinstall both of these components later if needed by running the following scripts. Note, Multimedia must be installed first.

To Install Multimedia:

 connect / as sysdba 
 @?/ord/admin/ordinst.sql SYSAUX SYSAUX 
 @?/ord/im/admin/catim.sql 
 execute sys.validate_ordim; 

To install Spatial:

 connect / as sysdba 
 @?/md/admin/mdinst.sql 

How to check if spatial and multimedia is installed in the database

The following advice was given to me by Oracle Support services, to determine if either of these components are being used in the database:

 connect / as sysdba 

 set pagesize 10000 
 col owner format a12 
 col table_name format a35 
 col column_name format a25 
 col index_name format a25 

 -- Is Spatial being used? 

 select owner, index_name 
 from dba_indexes 
 where ityp_name = 'SPATIAL_INDEX'; 

 select owner, table_name, column_name 
 from dba_tab_columns 
 where data_type= 'SDO_GEOMETRY' 
 and owner != 'MDSYS'; 

If both of these queries return no rows, Spatial is not being used.

 -- Is Multimedia being used? 

 set serveroutput on; 
 @?/ord/im/admin/imremchk.sql 

If you're not using this component, you will get the message returned "Oracle Multimedia is not being used".