Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-19c

Oracle Spatial (SDO) clean up in Oracle Database 11.2-19c is mostly straight forward. You can do it with the database up and running in Oracle Database 12c. Nevertheless it is very important that you check beforehand if Spatial and/or Graph are in use.

Be positively aware:
Since December 5, 2019, Oracle Spatial and Graph are both included automatically in all database licenses back to Oracle 11.2.0.4, for EE as well as for SE2. Generally. Read more here: https://mikedietrichde.com/2019/12/06/great-license-news-spatial-and-graph-machine-learning/

Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-19c

Oracle Spatial (formerly known as Spatial Data Option) and Graph are great features of the Oracle Database. You can find out more about it with this White Paper. And I had the pleasure of attending a Graph presentation of my PM colleague Hans Viehmann – I was very surprised how powerful and cool it is. If you plan to use it please check the License Guide (search for “Spatial and Graph Data“) as some parts of it require a license.

Although it is a great feature in some situations you may want to remove it. Unfortunately there’s a consolidated script for removal available ($ORACLE_HOME/md/admin/mddins.sql). But my experience with the old method of removing the users and their leftovers is actually better and gives me the expected result.  The MOS notes I will link below offer detailed information.

Before you start removing anything from your database please make sure you read my introductory blog post about how to Remove and Clean Up Components from Oracle 11.2 – 12.2 . You’ll find links to monitor proper component removal there as well as in the SQL Scripts Repository on this blog.

Oracle Database 11.2.0.4

MOS Note: 179472.1 –  Steps for Manual De-installation of Oracle Spatial describes the process of removing Spatial in all details up to Oracle Database 11.2.0.4. Please see also the MOS Note: 1070647.1 – How To Deinstall Oracle Spatial Keeping Oracle Locator.

If you don’t use any Spatial and/or Graph functionality you start by dropping the user MDSYS:

*** Caution: When you drop the user MDSYS, you must remove ORDIM as well ***

$ sqlplus / as sysdba
SQL> drop user MDSYS cascade;

Be aware:
Dropping the user MDSYS may require the database to be in STARTUP RESTRICT mode. You’ll need to restart the database in this mode if you receive an ORA-604 and ORA-14452.

In the next step you must clean up all the leftover synonyms where table_owner='MDSYS'. Run the following script which spools the “drop synonym” statements into a script file:

SQL> set pagesize 0 
SQL> set feed off 
SQL> spool dropsyn.sql 
SQL> select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS'; 
SQL> spool off;

Then run the spool file:

SQL> @dropsyn.sql

Finally you’ll have to clean up two additional users:

SQL> drop user mddata cascade;
SQL> drop user spatial_csw_admin_usr cascade;

And as I received several ORA-14452 errors I figured out a restart of the database will solve those.

Oracle Database 12.1.0.2

Start by dropping the user MDSYS:

*** Caution: When you drop the user MDSYS, you must remove ORDIM as well ***

$ sqlplus / as sysdba
SQL> drop user MDSYS cascade;

Be aware:
Dropping the user MDSYS may require the database to be in STARTUP RESTRICT mode. You’ll need to restart the database in this mode if you receive an ORA-604 and ORA-14452.

In the next step you must clean up all the leftover synonyms where table_owner='MDSYS'. Run the following script which spools the “drop synonym” statements into a script file:

SQL> set pagesize 0 
SQL> set feed off 
SQL> spool dropsyn.sql 
SQL> select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS'; 
SQL> spool off;

Then run the spool file:

SQL> @dropsyn.sql

Finally you’ll have to clean up two additional users:

SQL> drop user mddata cascade;
SQL> drop user spatial_csw_admin_usr cascade;

Oracle Database 12.2.0.1

non-CDB

Start by dropping the user MDSYS:

*** Caution: When you drop the user MDSYS, you must remove ORDIM as well ***

$ sqlplus / as sysdba
SQL> drop user MDSYS cascade;

In the next step you must clean up all the leftover synonyms where table_owner='MDSYS'. Run the following script which spools the “drop synonym” statements into a script file:

SQL> set pagesize 0 
SQL> set feed off 
SQL> spool dropsyn.sql 
SQL> select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS'; 
SQL> spool off;

Then run the spool file:

SQL> @dropsyn.sql

Finally you’ll have to clean up two additional users and a leftover package which belongs to Graph and got introduced in Oracle 12.2.0.1:

SQL> drop user mddata cascade;
SQL> drop user spatial_csw_admin_usr cascade;
SQL> drop package SYS.SDO_RDF_EXP_IMP;

CDB

You can remove Spatial from a Multitenant container database as well. As a general best practice I remove components from all PDBs including the PDB$SEED first before removing them from the CDB$ROOT. As you can see below, the entire removal process requires quite a number of steps.

Script Preparation

At first I start with dropping the user MDSYS from all PDBs:

*** Caution: When you drop the user MDSYS, you must remove ORDIM as well ***

cd $ORACLE_HOME/rdbms/admin
vi drop_MDSYS.sql
     drop user MDSYS cascade;

In addition I create a script to clean up all the leftover synonyms. I generate all “drop” commands with:

set pagesize 0 
set feed off 
set linesize 200
set termout off
set serverout on
spool dropsyn.sql 
exec DBMS_OUTPUT.PUT_LINE('alter session set "_ORACLE_SCRIPT"=TRUE;');
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS'; 
spool off;
exit

In addition I will prepare a script for later to remove the leftover Spatial users:

vi drop_others.sql
      drop user mddata cascade;
      drop user spatial_csw_admin_usr cascade;

Remove MDSYS and leftover-synonyms from PDBs

Now I can drop MDSYS from the PDBs and the PDB$SEED at first, then I clean up the leftover synonyms:

*** Caution: When you drop the user MDSYS, you must remove ORDIM as well ***

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -C 'CDB$ROOT' -e -b drop_MDSYS_pdbs -d '''.''' drop_MDSYS.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -C 'CDB$ROOT' -e -b dropsyn_pdbs -d '''.''' dropsyn.sql

This approach assumes silently that there is an identical set of synonyms to drop in all PDBS which is the case in a default installation.

Remove MDSYS and leftover-synonyms from CDB$ROOT

*** Caution: When you drop the user MDSYS, you must remove ORDIM as well ***

Before I can remove MDSYS from the CDB$ROOT container I will have to shutdown all PDBs including the PDB$SEED as otherwise the drop statement fails with ORA-604 and ORA-14452: attempt to create, alter or drop an index on temporary table already in use.

sqlplus / as sysdba
SQL> alter pluggable database all close immediate;
SQL> alter pluggable database pdb$seed close;
SQL> exit

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -e -b drop_MDSYS_cdb -d '''.''' drop_MDSYS.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -e -b dropsyn_cdb -d '''.''' dropsyn.sql

Afterwards I’m opening all PDBs again:

sqlplus / as sysdba
SQL> alter pluggable database all open;
SQL> alter pluggable database pdb$seed open read only;
SQL> exit

In the next step I drop the remaining Spatial users:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -C 'CDB$ROOT' -e -b drop_others_pdbs -d '''.''' drop_others.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -e -b drop_others_cdb -d '''.''' drop_others.sql

Finally I remove a leftover package from all containers:

$ vi drop_leftovers.sql
     drop package sys.SDO_RDF_EXP_IMP;

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b drop_leftovers -d '''.''' drop_leftovers.sql

Oracle Database 19c

CDB

You can remove Spatial from a Multitenant container database as well. As a general best practice I remove components from all PDBs including the PDB$SEED first before removing them from the CDB$ROOT. As you can see below, the entire removal process requires quite a number of steps.

Script Preparation

At first, create 3 scripts – one of them needs to be executed in CDB$ROOT to generate a script to drop the synonym leftovers:

• Script to drop the MDSYS user

  *** Caution: When you drop the user MDSYS, you must remove ORDIM as well ***

  cd $ORACLE_HOME/rdbms/admin
  vi drop_MDSYS.sql
  
  drop user MDSYS cascade;

• Script to drop the MDDATA user

  cd ?/rdbms/admin
  vi drop_MDDATA.sql
  
  drop user mddata cascade;

• Script to drop the leftover synonyms – you need to run this in CDB$ROOT

  cd ?/rdbms/admin
  vi syn.sql
  
  set pagesize 0 
  et feed off
  set linesize 200
  set termout off
  set serverout on
  
  spool dropsyn.sql
  exec DBMS_OUTPUT.PUT_LINE('alter session set "_ORACLE_SCRIPT"=TRUE;');
  select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
  spool off;
  exit

  Please run your syn.sql script in CDB$ROOT to generate the dropsyn.sql you will use later.

Prepare this 3 items and then proceed with the actual removal.

Remove MDSYS and leftover-synonyms from PDBs

Now I can drop MDSYS from the PDBs and the PDB$SEED at first, then I clean up the leftover synonyms:

*** Caution: When you drop the user MDSYS, you must remove ORDIM as well ***

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -C 'CDB$ROOT' -e -b drop_SDO_pdbs -d $ORACLE_HOME/md/admin mddins.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -C 'CDB$ROOT' -e -b dropsyn_pdbs -d '''.''' dropsyn.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -C 'CDB$ROOT' -e -b drop_MDSYS_pdbs -d '''.''' drop_MDSYS.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -C 'CDB$ROOT' -e -b drop_MDDATA_pdbs -d '''.''' drop_MDDATA.sql

This approach assumes silently that there is an identical set of synonyms to drop in all PDBS which is the case in a default installation.

Remove MDSYS and leftover-synonyms from CDB$ROOT

*** Caution: When you drop the user MDSYS, you must remove ORDIM as well ***

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -e -b drop_SDO_cdb -d $ORACLE_HOME/md/admin mddins.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -e -b dropsyn_cdb -d '''.''' dropsyn.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -e -b drop_MDSYS_cdb -d '''.''' drop_MDSYS.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'CDB$ROOT' -e -b drop_MDDATA_cdb -d '''.''' drop_MDDATA.sql

Leftovers?

At this point – at least when I remove ORDIM upfront – I end up with the following list of INVALID objects:

SQL> select object_name, object_type, con_id, owner from cdb_objects where status='INVALID' order by con_id

OBJECT_NAME			OBJECT_TYPE	     CON_ID OWNER
------------------------------- -------------------- ------ ---------
JAVA$POLICY$SHARED$00000018	JAVA DATA		  1 OJVMSYS
JAVA$POLICY$SHARED$00000018	JAVA DATA		  3 OJVMSYS

2 rows selected.

I decided to ignore these and rather remove OJVM in the next step.

Component Clean Up Series

• Remove and Clean Up Components from Oracle 11.2 – 12.2 – General Guidelines and Precautions
• APEX – Oracle Application Express Clean Up
• OWM – Oracle Workspace Manager Clean Up
• DV – Oracle Database Vault Clean Up
• OLS – Oracle Label Security Clean Up
• SDO – Oracle Spatial Data Option Clean Up
• CONTEXT – Oracle Text Clean Up
• ORDIM – Oracle Multi Media Clean Up
• XOQ – Oracle OLAP API Clean Up
• APS – Oracle OLAP Analytical Workspace Clean Up
• AMD – Oracle OLAP Catalog Clean Up
• OWB – Oracle Warehouse Builder Clean Up
• EXF/RUL – Oracle Expression Filters and Rules Manager Clean Up
• EM – Enterprise Manager Database Control Clean Up
• JAVAVM/XML – Oracle Java Virtual Machine and XDK Clean Up
• XDB – Oracle XML Database Clean Up

Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/

–Mike