[Update: this is not anymore valid for Oracle 19c. To install Oracle Locator on Oracle 19c, check this post]
Oracle Locator is the free subset of Oracle Spatial functions. It is installed in MDSYS schemas. Some applications that use geographical data need Oracle Locator in order to compile.
There are two ways to install Oracle Locator, an easy and a complex.
When doing on single-tenant databases and Oracle Locator is only need for one pluggable database, please use the simple way.
Simple way to install Oracle Locator
In this case we install Locator directly on the pluggable database.
SQL> alter session set container=XPTO;
SQL> create user mdsys identified by "XXXXX" default tablespace sysaux;
@?/md/admin/mdprivs.sql
@?/md/admin/catmdloc.sql
SQL> alter user mdsys password expire;
SQL> alter user mdsys account lock;
SQL> col comp_name for a50
SQL> select comp_id,comp_name,status from dba_registry;
COMP_ID COMP_NAME STATUS
------------------------------ -------------------------------------------------- -----------
CATALOG Oracle Database Catalog Views VALID
CATPROC Oracle Database Packages and Types VALID
XDB Oracle XML Database VALID
OWM Oracle Workspace Manager VALID
Complex way to install Oracle Locator
When you have a multitnenant environment and you want that Locator is available on all future PDBs that you create, the following method is to use. Unfortunately there are a few bugs (on 12.2 at least) when I tried this.
We need to install Oracle Spatial first and then remove Oracle Spatial and leaving Locator only objects.
I start with a fairly empty CDB, without JAVAVM. This must first be installed. I’ve already downloaded the scripts from the note described below, so I don’t go much in detail.
set lines 200
select comp_id,version, status from dba_registry;
COMP_ID VERSION STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG 12.2.0.1.0 VALID
CATPROC 12.2.0.1.0 VALID
XDB 12.2.0.1.0 VALID
OWM 12.2.0.1.0 VALID
-- Install JAVAVM, XDB, XML - Repairing/Reinstalling JVM To A Multitenant Database (CDB and all PDBs) (Doc ID 2262919.1):
==========================================================================================================================
INSTALL_DIR=/temp/rdms122_java
cp $INSTALL_DIR/reinitjvm.sql $ORACLE_HOME/javavm/install
sqlplus / as sysdba << EOF
alter profile default limit password_verify_function null;
@$INSTALL_DIR/fulljvminstall.sql
shutdown immediate;
EOF
-- Oracle restart starts back the database automatically
sqlplus / as sysdba << EOF
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME/rdbms/admin utlrp.sql
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l $INSTALL_DIR -n 1 -b post_jvm_installation_info_ $INSTALL_DIR/jvm_info.sql;
set lines 200
select comp_id,version, status from dba_registry;
EOF
...
COMP_ID VERSION STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG 12.2.0.1.0 VALID
CATPROC 12.2.0.1.0 VALID
XML 12.2.0.1.0 VALID
JAVAVM 12.2.0.1.0 VALID
CATJAVA 12.2.0.1.0 VALID
XDB 12.2.0.1.0 VALID
OWM 12.2.0.1.0 VALID
Next step is to install Oracle Multimedia, which is also required by Spatial:
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l ${ORACLE_BASE}/admin/${ORACLE_SID}/create/logs -d $ORACLE_HOME/md/admin -b mdinst mdinst.sql
SQL> select comp_id,version, status from dba_registry;
COMP_ID VERSION STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG 12.2.0.1.0 VALID
CATPROC 12.2.0.1.0 VALID
XML 12.2.0.1.0 VALID
JAVAVM 12.2.0.1.0 VALID
CATJAVA 12.2.0.1.0 VALID
XDB 12.2.0.1.0 VALID
OWM 12.2.0.1.0 VALID
ORDIM 12.2.0.1.0 VALID
SDO 12.2.0.1.0 VALID
And finally we install Spatial and remove it again, leaving Locator
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l ${ORACLE_BASE}/admin/${ORACLE_SID}/create/logs -d $ORACLE_HOME/md/admin -b mddins mddins.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME/rdbms/admin utlrp.sql
SQL> select comp_id,version, status from dba_registry;
COMP_ID VERSION STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG 12.2.0.1.0 VALID
CATPROC 12.2.0.1.0 VALID
XML 12.2.0.1.0 VALID
JAVAVM 12.2.0.1.0 VALID
CATJAVA 12.2.0.1.0 VALID
XDB 12.2.0.1.0 VALID
OWM 12.2.0.1.0 VALID
ORDIM 12.2.0.1.0 VALID
SDO 12.2.0.1.0 REMOVED
There were some objects invalid, which match the Oracle note: “Invalid MDSYS objects after running mddins.sql to keep Locator option on 12.2.0.1 (Doc ID 2307454.1)”
vi /tmp/bug2307454.sql
drop function mdsys.NDM_RESET_ID_GENERATOR;
@$ORACLE_HOME/md/admin/semremov.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l ${ORACLE_BASE}/admin/${ORACLE_SID}/create/logs -d /tmp -b bug2307454 bug2307454.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME/rdbms/admin utlrp.sql
There were still some invalid public synonyms left. I opened an SR, and Oracle said they can be removed:
SQL> set lines 200
SQL> set pages 40
SQL> col owner for a20
SQL> col object_name for a50
SQL> col object_type for a30
SQL> select owner,object_name,object_type,con_id,sharing,status from cdb_objects where status!='VALID' and owner!='MLOG' order by 1,2;
OWNER OBJECT_NAME OBJECT_TYPE CON_ID SHARING STATUS
-------------------- -------------------------------------------------- ------------------------------ ---------- ------------------ -------
PUBLIC ALL_SDO_GEOR_SYSDATA SYNONYM 3 METADATA LINK INVALID
...
21 rows selected.
alter session set "_ORACLE_SCRIPT" = true;
BEGIN
FOR cmdline IN (select 'drop public synonym '|| object_name cmd from dba_objects where status!='VALID' and owner='PUBLIC') LOOP
dbms_output.put_line(cmdline.cmd);
execute immediate(cmdline.cmd);
END LOOP;
END;
/
alter session set "_ORACLE_SCRIPT" = true;
And that’s it. A complex way to install Locator…
