Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-12.2

Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-12.2Oracle Spatial (SDO) clean up in Oracle Database 11.2-12.2 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.

Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-12.2

Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-12.2Oracle 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

Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-12.2

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:

$ 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:

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:

$ 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:

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

 

Oracle Database 12.2.0.1

non-CDB

Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-12.2

Start by dropping the user MDSYS:

$ 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

Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-12.2

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.

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

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

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

In addition I clean up the leftover synonyms. At first I generate all “drop” commands:

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;
SQL> exit

$ $ORACLE_HOME/perl/bin/perl 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.

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;
SQL> alter pluggable database pdb$seed close;
SQL> exit
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b drop_MDSYS_cdb -d '''.''' drop_MDSYS.sql

Then I remove the synonym leftovers from the CDB$ROOT as well:

$ $ORACLE_HOME/perl/bin/perl 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:

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

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b drop_others_pdbs -d '''.''' drop_others.sql
$ $ORACLE_HOME/perl/bin/perl 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

Done!

Component Clean Up Series

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

 

–Mike

Leave a Reply

Your email address will not be published. Required fields are marked *