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
Hi Mike,
Got 2 issues:
In stead of
alter pluggable database all close;
alter pluggable database pdb$seed close;
I head to use:
alter pluggable database all close immediate;
alter pluggable database pdb$seed close;
And
alter session set “_oracle_script”=true;
Laurens,
this makes sense (especially the _ORACLE_SCRIPT=TRUE for my home-grown script).
Oracle scripts will have it by default, but mine hasn’t.
I updated the post.
Thanks a lot!
Mike
Hi, does the same procedure work on 19c?
Patrick,
the same way as for 12.2.0.1 – but I’ve had not time to verify and update all procedures yet.
Cheers,
Mike
After removing SDO, EXPDP with FULL export catch error, ODB 12.2.0.1
BEGIN “SYS”.”SDO_RDF_EXP_IMP”.SYSTEM_CALLOUT(0,’12.02.00.01.00′); END;
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06550: line 1, column 8:
PLS-00201: identifier ‘SYS.SDO_RDF_EXP_IMP’ must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
how to solve this error?
MOS Doc ID 1430758.1 advise grant, but package not exists anymore.
Hi Victor,
how did you remove SDO?
And you removed it from 12.2.0.1 or another release, from a non-CDB or a CDB environment?
And then you start an export and you hit the above error?
Thanks for some clarification!
Cheers,
Mike
Hi Mike,
I removed SDO according Your post above for NonCDB 12.2.0.1.
And now I try to make Full Export with EXPDP and in logfile was an error:
>>> ORA-31642: the following SQL statement fails:
BEGIN “SYS”.”SDO_RDF_EXP_IMP”.SYSTEM_CALLOUT(1,’12.02.00.01.00′); END;
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06550: line 1, column 8:
PLS-00201: identifier ‘SYS.SDO_RDF_EXP_IMP’ must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
But damp files was successfully made:
Job “EXPORT”.”SYS_EXPORT_FULL_02″ successfully completed at Thu Mar 19 11:37:06 2020 elapsed 0 00:33:38
Simply information for checking your post.
PS. All latest RU and OJVM patches installed on system.
Victor.
Victor, Did you ever figure how to fix this? I am running into the same error now.
We have followed autoupgrade method to upgrade our DW environment from 12c to 19c. After that 1 week it was working fine. then we changed compatible parameter to 19.0.0 after that we noticed SPATIAL component became invalid, Export failing with ORA-600 , GG replicates abend’g with ORA-06600 errors.
When i compared with other environment we didnt see the same behavior and i compared MDSYS objects and see 11 tables , 38 indexes, 2 Java class, 19 LOB missing/less than other enviornment. 1 type more in the problematic database. Tables lists are
SYS_NTNgjHlWghpQ/gUxDr+QrSRw==
SYS_NTNgjHlWgjpQ/gUxDr+QrSRw==
SYS_NTNgjHlWgkpQ/gUxDr+QrSRw==
SYS_NTNgjHlWglpQ/gUxDr+QrSRw==
SYS_NTNgjHlWgmpQ/gUxDr+QrSRw==
SYS_NTNgjHlWgppQ/gUxDr+QrSRw==
SYS_NTNgjHlWgrpQ/gUxDr+QrSRw==
SYS_NTNgjHlWgspQ/gUxDr+QrSRw==
SYS_NTNgjHlWgnpQ/gUxDr+QrSRw==
GridFile602_TAB
CS_SRS_ORIG
ORA-0600 error arguments are keep changing for one query. GG and export keep throwing
ORA-00600: internal error code, arguments: [15851]
ORA-00600: internal error code, arguments: [25027]
other one keep changing –
ORA-00600: internal error code, arguments: [15593] , before that it was [15851] and we applied 30696566 as per Oracle support.
Please let me know your thoughts on this.
Hi Kanann,
actually AutoUpgrade does NOTHING different from the upgrade perspective as command line (or if you would like to, DBUA). All use the exact same scripts. There is ZERO difference. Do you have an SR opened you can share with me? Please either post the SR number here (others can’t look it up) or send me an email with the SR number.
Cheers,
Mike
When I upgraded my prod database, spatial data showed invalid.
This is a very nice article. Thank you.
Any idea how to reinstall.
We are not using it but like to keep our envs in sync.
COORD_OPERATION_FOREIGN_SOURCE SDO_COORD_OPS constraint
I recreated mdsys user and these:
Work notes
Activities: 14
S
system
Email sent•06-16-2020 08:37:49
Email sent
Subject:USG-ITASK0024574 – Pending – Oracle 19 upgrade USG123 PROD enviroment on 6/12 after 11 pm — Ref:USG-MSG4734665
From:
To:denise.fulton@usg.edu
Show email details
DF
Denise Fulton
Work notes•06-16-2020 08:37:42
Stilll missing the constraints that USGWH had the issue.
I see them in D and I am searching for the script to run to create them.
Found them in ?/md/admin
S
system
Email sent•06-15-2020 16:49:01
Email sent
Subject:USG-ITASK0024574 – Pending – Oracle 19 upgrade USG123 PROD enviroment on 6/12 after 11 pm — Ref:USG-MSG4733391
From:
To:denise.fulton@usg.edu
Show email details
DF
Denise Fulton
Work notes•06-15-2020 16:49:00
I have this cleaned up in the clone:
I did this previously a while back in the USGWHDR and had the steps.
So weird it crapped in prod.
sqlplus / as sysdba @/sw/global/usg123/create_mdsys
sqlplus / as sysdba @?/md/admin/mdprivs.sql
sqlplus / as sysdba @?/md/admin/mdinst.sql
sqlplus / as sysdba @/sw/global/usg123/verify_db_registry.sql
sqlplus / as sysdba @//sw/global/usg123/mdsys_lock
select ‘drop ‘ || object_type || ‘ ‘ || owner || ‘.’ || object_name || ‘; ‘ from dba_objects where status = ‘INVALID’ and owner = ‘SYS’ order by owner, object_name;
S
system
Email sent•06-13-2020 16:10:47
Email sent
Subject:USG-ITASK0024574 – Pending – Oracle 19 upgrade USG123 PROD enviroment on 6/12 after 11 pm — Ref:USG-MSG4724695
From:
To:denise.fulton@usg.edu
Show email details
DF
Denise Fulton
Work notes•06-13-2020 16:10:36
Cloning database USGWH onto server whsdb1.
S
system
Email sent•06-13-2020 04:53:03
Email sent
Subject:USG-ITASK0024574 – Pending – Oracle 19 upgrade USG123 PROD enviroment on 6/12 after 11 pm — Ref:USG-MSG4724593
From:
To:denise.fulton@usg.edu
Show email details
DF
Denise Fulton
Work notes•06-13-2020 04:52:48
I hit one issue with MDSYS (SDO)
I can see the difference in D vs P but not sure how to fix.
We had issues in the past with DR and had some scripts in place but I’m not sure. I opened a ticket with Oracle and will touch base with the team in the morning.
We have verified everything and i don’t believe we use this but will get it corrected.
sqlplus / as sysdba @//usr/local/eas-ts/global/usg123/upgrade_scripts/cleanup_sys_sdo.sql
sqlplus / as sysdba @//usr/local/eas-ts/global/usg123/upgrade_scripts/create_mdsys
sqlplus / as sysdba @?/md/admin/mdprivs.sql
sqlplus / as sysdba @?/md/admin/mdinst.sql
sqlplus / as sysdba @/usr/local/eas-ts/global/usg123/upgrade_scripts/verify_dba_registry.sql
sqlplus / as sysdba @//usr/local/eas-ts/global/usg123/upgrade_scripts/mdsys_lock
select ‘drop ‘ || object_type || ‘ ‘ || owner || ‘.’ || object_name || ‘; ‘ from dba_objects where status = ‘INVALID’ and owner = ‘SYS’ order by owner, object_name;
Hi Denise,
sorry for getting back to you so late. Is this still an issue?
Thanks,
Mike
thanks again for the great work, Mike
for the 19c section, have you forgotten to run drop_MDSYS.sql for both PDBs and CDB? I can see an empty row above the Leftovers? section.
Norm
Dear Mike,
I dropped Oracle Spatial on Oracle 19c under windows. When I run :
%ORACLE_HOME%/perl/bin/perl %ORACLE_HOME%/rdbms/admin/catcon.pl -n 1 -C ‘CDB$ROOT’ -e -b dropsyn_pdbs -d ”’.”’ dropsyn.sql
the result are:
catcon::set_log_file_base_path: ALL catcon-related output will be written to [C:\app\oracle\product\19.0.0.0\dbhome_1\rdbms\admin\dropsyn_pdbs_catcon_2872.lst]
catcon::set_log_file_base_path: catcon: See [C:\app\oracle\product\19.0.0.0\dbhome_1\rdbms\admin\dropsyn_pdbs*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [C:\app\oracle\product\19.0.0.0\dbhome_1\rdbms\admin\dropsyn_pdbs_*.lst] files for spool files, if any
catcon::valid_src_dir: Specified source file directory (”’.”’) does not exist or is not a directory
catcon::catconInit2: Unexpected error returned by valid_src_dir
Unexpected error encountered in catconInit2; exiting
is it normal?
Regards,
Teguh
This is expected – see the output:
Specified source file directory (”’.”’) does not exist or is not a directory
You can’t specify a dot to mark the current directory on win.
Use the path to the directory where the script is located in instead.
Cheers,
Mike
Thank you for this article it is very helpful.
The only problem for me in Oracle 19c i19.13 s that the public synonym are not drop even if it is on the script and it shows Synonym dropped on the command prompt.
Any idea?
PUBLIC SYNONYM SDO_GEORASTER_ARRAY
PUBLIC SYNONYM SDO_GEOR_HISTOGRAM_ARRAY
PUBLIC SYNONYM SDO_OLS
PUBLIC SYNONYM SDO_WFS_LOCK
PUBLIC SYNONYM SDO_NETWORK_MANAGER_T
….
I need more information please – which steps did you complete etc.
Are you doing this with a CDB environment or with a non-CDB?
Thanks,
Mike
Hi Mike,
i followed yesterday this guide to uninstall Spatial in 19.17. I did not have to execute drop_MDSYS.sql, syn.sql and dropsyn.sql. The below sql executed at PDB level showed that public synonyms of MDSYS belong to the CDB$ROOT.
SQL> select distinct origin_con_id
from dba_synonyms
where table_owner=’MDSYS’ and owner=’PUBLIC’;
ORIGIN_CON_ID
————-
1
I guess that these synonyms are automatically deleted once MDSYS is dropped at CDB level. I did not see any public synonyms owned by a normal PDB. Summarized executing mddins.sql and drop_MDDATA.sql (without cascade option) on all PDBs (incl. PDB$SEED and CDB$ROOT) was in my case enough.
Here the install and uninstall spatial procedure I tested a couple of times today:
— install on all pdbs (incl. CDB$ROOT and PDB$SEED)
echo “@\${ORACLE_HOME}/md/admin/mdinst.sql
select status from dba_registry where comp_id=’SDO’;
@utlrp.sql
exec sys.validate_sdo
select status from dba_registry where comp_id=’SDO’;” > /tmp/install_spatial.sql
${ORACLE_HOME}/perl/bin/perl ${ORACLE_HOME}/rdbms/admin/catcon.pl \
-n 1 \
-e \
-l /tmp \
-b ${ORACLE_SID}_spatial_inst \
/tmp/install_spatial.sql
— deinstall on all pdbs (incl. CDB$ROOT and PDB$SEED)
echo “select status from dba_registry where comp_id=’SDO’;
select username from dba_users where username in (‘MDSYS’,’MDDATA’);
@\$ORACLE_HOME/md/admin/mddins.sql
select username from dba_users where username in (‘MDSYS’,’MDDATA’);
drop user mddata;” > /tmp/uninstall_spatial.sql
${ORACLE_HOME}/perl/bin/perl ${ORACLE_HOME}/rdbms/admin/catcon.pl \
-n 1 \
-e \
-l /tmp \
-b ${ORACLE_SID}_spatial_deinst \
/tmp/uninstall_spatial.sql
Maybe it’s useful to others. Furthermore I found in the log file of the deinstallation ORA-39705.
SQL> EXECUTE dbms_registry.removed(‘SDO’);
BEGIN dbms_registry.removed(‘SDO’); END;
*
ERROR at line 1:
ORA-39705: component ‘SDO’ not found in registry
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.DBMS_REGISTRY”, line 991
ORA-06512: at line 1
In semremov.sql it’s written that there is no need to call dbms_registry.removed if the MDSYS is completely removed.
$ cd $ORACLE_HOME/md/admin
$ grep -i -B2 -n “registry is delete” semremov.sql
96— be called if there are no existing dependencies. If your removal script is going to —
97— drop the entire schema you do not need to call dbms_registry interfaces because the —
98:– entry in the component registry is deleted when the component schema is dropped. —
Maybe you can inform Oracle development to avoid the error in future releases.
Regards,
David
Hi David,
thanks for the hint. And if my time allows, I will refresh my procedures. The SDO team is not inline with my proposal for deinstalling SDO.
And maybe the error is in my manual procedure 🙁
Cheers,
Mike
Hi Mike,
You mention that Spatial Data Option depends on Multimedia however, in your post you state that when you drop the MDSYS user, you must remove ORDIM (Multimedia ) as well. This indicates a cross-dependancy. Is there no way to remove SDO and leave ORDIM in place?
Regards,
Steve
Hi Steve,
this MDSYS/ORDIM topic is still some sort of mystery to me – I will blog more about it later this week.
And yes, you can’t remove SDO and leave ORDIM in place. And since ORDIM is desupported and gets basically removed from the API perspective in 19c, there is not much reason to keep ORDIM.
Cheers,
Mike
Thank you for this post. My previous comment didn’t it make it through so posting again. You mention that Multimedia (ORADMIN) must be removed if Spatial SDO is removed. I wanted to clarify if this is accurate. I would think the inverse is true since Multimedia is dependent upon Spatial.
Thank you,
Steve
Hi Steve,
there are some strange things with the locator.
So this is how things depend on each other:
SDO depends on ORDIM
ORDIM depends on JAVAVM
This means:
You remove JAVAVM, you need to remove SDO at first, then ORDIM, then JAVAVM (and XDK since it depends on JAVAVM, too).
You remove ORDIM, then you must remove SDO as well.
You remove SDO only, nothing (in theory) needs to be removed.
BUT … there is the Locator – and since the Locator is not an independent component until 19c, there is some strange cross-dependency.
And this leads to problems as far as my research has been. But I have reserved time for later this week to dig deeper.
Cheers,
Mike
Thank you, Mike. Really appreciate your insight.
Regards,
Steve
About leftovers object OJVMSYS.JAVA$POLICY$SHARED$% in a 19c clean-up I’m doing in a non-CDB (aimed to plug it later into a CDB as PDB for its first time)….
What if I need JVM fine the same on database catalogue? Do I have to uninstall it and reinstall following note “Safe repair/reinstall of the JVM Component in 11.2 and up (Doc ID 2314363.1)”, right?
Hi Federica,
I think in this case you need to reinstall it in CDB$ROOT then.
But to be extra-safe, you may need to check with Support please.
THanks
Mike