How to install Oracle 19c Spatial

This article will guide you through the installation of Oracle 19c Spatial. It is structured as follows:

Goal

Prerequisites

  Check installed Oracle components

  Oracle System User MDSYS
    Check if user MDSYS exists
    Create the user MDSYS
    Assign the Privileges to the user MDSYS

Oracle Spatial Installation
  Install Oracle Spatial
  Verification of Oracle Spatial Installation
  Revalidate invalid SDO objects

Goal

Within Oracle Enterprise you can install Spatial using the Database Configuration Assistant (DBCA), instead of following the manual procedure described in this document. With Oracle Standard Edition Spatial flag cannot selected within DBCA, therefore manual workflow is the only way to add Spatial component.

If you created your database manually or want to install Spatial later, then follow these steps.

Prerequisites

Check installed Oracle components

Check if Spatial isn’t already installed

SQL>
set lines 300;
set pagesize 10000;
select comp_id,version,version_full,status from dba_registry;

Oracle System User MDSYS

Oracle system user MDSYS is required for Spatial package.

1. Check if user MDSYS exists

Check if the user MDSYS exists in your database

SQL> select username from dba_users where username like 'MD%';

2. Create the user MDSYS

If the MDSYS user does NOT exist! Create the user MDSYS by running following command:

SQL> create user MDSYS identified by <password> default tablespace SYSAUX account lock;

3. Assign the Privileges to the user MDSYS

Grant the required privileges to MDSYS by running:

SQL> @?/md/admin/mdprivs.sql

Oracle Spatial Installation

1. Install Oracle Spatial

Install Spatial by executing the steps shown below.

1. Connect to the DB as sysdba

2.

SQL> @?/md/admin/mdinst.sql

2. Verification of Oracle Spatial Installation

Execute the following steps to verify if Spatial is installed correctly:

1. Connect to the DB as sysdba

SQL>
select comp_id, control, schema, version, status, comp_name from dba_registry
where comp_id='SDO';
select object_name, object_type, status from dba_objects where owner='MDSYS' and
status <> 'VALID' order by object_name;

3. Revalidate invalid SDO objects

IF you get invalid SDO objects after the processing installation of Spatial component you should run
utlrp.sql to revalidate those objects.

1. Open a cmd.exe as administrator and set the ORACLE_HOME environment variable pointing to your
   Database Home (e.g. set ORACLE_HOME=c:\oracle\product\19.3.0\dbhome_1)
2. In the cmd.exe change the directory

   cd %ORACLE_HOME%

3. Connect to the database as sysdba and run the script
   1. Connect to the DB as sysdba

   2. SQL> @?/rdbms/admin/utlrp.sql
      SQL> exec sys.VALIDATE_SDO();

       

Check now if Spatial component is installed and valid.

SQL>
set lines 300;
set pagesize 10000;
select comp_id,version,version_full,status from dba_registry;

SQL>
select comp_id, control, schema, version, status, comp_name from dba_registry where comp_id='SDO';
select object_name, object_type, status from dba_objects where owner='MDSYS' and status <> 'VALID' order by
object_name;