My Oracle Support Banner

Locator objects missing after database upgrade to 12.1.0.1 (Doc ID 1909927.1)

Last updated on JULY 22, 2014

Applies to:

Oracle Spatial and Graph - Version 12.1.0.1 to 12.1.0.1 [Release 12.1]
Information in this document applies to any platform.

Symptoms

MDSYS objects are missing after upgrading to 12cR1, if SDO was removed (status=REMOVED) while keeping Locator prior to the upgrade.

In the pre-upgrade db, we see:

SQL> select instance_name, version from v$instance;

INSTANCE_NAME    VERSION
---------------- -----------------
ORC112U          11.2.0.3.0

SQL>  SELECT substrb(Comp_ID,1,10)Comp_ID,
       substrb(Status,1,8)Status,
       substrb(Version,1,12)Version,
       substrb(Comp_Name,1,35)Comp_Name
FROM DBA_Registry
ORDER by 1,2;  2    3    4    5    6

COMP_ID    STATUS   VERSION      COMP_NAME
---------- -------- ------------ ----------------
...
SDO        REMOVED  11.2.0.3.0   Spatial
...

SQL> select OWNER,OBJECT_NAME, OBJECT_TYPE, status
 2  from dba_objects
 3  where OBJECT_NAME in
('SDO_GEOMETRY','SDO_POINT_TYPE','SDO_ELEM_INFO_ARRAY','SDO_CS')  order by
1,2,3;

OWNER      OBJECT_NAME                    OBJECT_TYPE         STATUS
---------- ------------------------------ ------------------- -------
MDSYS      SDO_CS                         PACKAGE             VALID
MDSYS      SDO_CS                         PACKAGE BODY        VALID
MDSYS      SDO_ELEM_INFO_ARRAY            TYPE                VALID
MDSYS      SDO_GEOMETRY                   TYPE                VALID
MDSYS      SDO_GEOMETRY                   TYPE BODY           VALID
MDSYS      SDO_POINT_TYPE                 TYPE                VALID
PUBLIC     SDO_CS                         SYNONYM             VALID
PUBLIC     SDO_ELEM_INFO_ARRAY            SYNONYM             VALID
PUBLIC     SDO_GEOMETRY                   SYNONYM             VALID
PUBLIC     SDO_POINT_TYPE                 SYNONYM             VALID

10 rows selected.


While in the post-upgrade database, we see:

SQL> select instance_name, version from v$instance;

INSTANCE_NAME    VERSION
---------------- -----------------
ORC112U          12.1.0.1.0

SQL>  SELECT substrb(Comp_ID,1,10)Comp_ID,
 2          substrb(Status,1,8)Status,
 3          substrb(Version,1,12)Version,
 4          substrb(Comp_Name,1,35)Comp_Name
 5   FROM DBA_Registry
 6   ORDER by 1,2;

COMP_ID    STATUS   VERSION      COMP_NAME
---------- -------- ------------ ----------------
...
SDO        REMOVED  11.2.0.3.0   Spatial
..

SQL> select OWNER,OBJECT_NAME, OBJECT_TYPE, STATUS
 2  from dba_objects
 3  where OBJECT_NAME in
('SDO_GEOMETRY','SDO_POINT_TYPE','SDO_ELEM_INFO_ARRAY','SDO_CS')
 4  order by 1,2,3;

OWNER      OBJECT_NAME                    OBJECT_TYPE             STATUS
---------- ------------------------------ ----------------------- -------
MDSYS      SDO_CS                         PACKAGE                 VALID
MDSYS      SDO_ELEM_INFO_ARRAY            TYPE                    VALID
MDSYS      SDO_GEOMETRY                   TYPE                    VALID
MDSYS      SDO_POINT_TYPE                 TYPE                    VALID


Error also occur when using Locator.  A simple example is below:

SQL> CREATE TABLE cola_markets_cs (
  2    mkt_id NUMBER PRIMARY KEY,
  3    name VARCHAR2(32),
  4    shape SDO_GEOMETRY);
  shape SDO_GEOMETRY)
        *
ERROR at line 4:
ORA-00902: invalid datatype

Changes

Database was upgraded to 12.1.0.1.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.