My Oracle Support Banner

Transportable Database Feature Does Not Initialize Spatial Indexes At Target Db (Doc ID 1916623.1)

Last updated on FEBRUARY 07, 2019

Applies to:

Oracle Spatial and Graph - Version 12.1.0.1 and later
Information in this document applies to any platform.

Symptoms

The Transportable database feature does not create a consistent spatial index in the TARGET Database.

When moving data from an 11.2.0.3 database to a 12.1 database using the new 12c transportable database feature,
everything transferred to the 12.1 target database except the spatial indexes are not complete because they are missing metadata from the ALL_SDO_INDEX_METADATA table and the index's underlying MDRT_$ table is missing.

1. user_sdo_index_info no rows selected
2. USER_SDO_INDEX_METADATA no rows selected
3. user_indexes shows spatial index as VALID
4. MDRT_12745$ table not created

This is from the TARGET database after the Full Import

sqlplus sdobug/sdobug

col segment_name format a28
col tablespace_name format a28
select segment_name, tablespace_name from dba_segments where owner='SDOBUG';

/*
SEGMENT_NAME TABLESPACE_NAME
---------------------------- ----------------------------
SYS_LOB0000096821C00010$$ TT_SPATIAL
SYS_LOB0000096821C00009$$ TT_SPATIAL
SYS_C0014795 TT_SPATIAL
SYS_IL0000096821C00010$$ TT_SPATIAL
SYS_IL0000096821C00009$$ TT_SPATIAL
COLA_MARKETS TT_SPATIAL
*/

-- 6 rows selected, in SOURCE there were 9 rows including MDRT_12745$

/*

Output from the SOURCE

SEGMENT_NAME TABLESPACE_NAME
---------------------------- ----------------------------
SYS_LOB0000075590C00003$$ TT_SPATIAL
SYS_LOB0000075583C00010$$ TT_SPATIAL
SYS_LOB0000075583C00009$$ TT_SPATIAL
SYS_IL0000075590C00003$$ TT_SPATIAL
SYS_C0010988 TT_SPATIAL
SYS_IL0000075583C00010$$ TT_SPATIAL
SYS_IL0000075583C00009$$ TT_SPATIAL
MDRT_12745$ TT_SPATIAL
COLA_MARKETS TT_SPATIAL

9 rows selected.

*/

set pagesize 9000
col index_name format a20
col table_owner format a14
col table_name format a20
col column_name format a14
col sdo_index_type format a12
col sdo_index_table format a14
col sdo_index_status format a14

select table_name, index_name, status, domidx_status, domidx_opstatus
from user_indexes
where ityp_name='SPATIAL_INDEX';

/*
TABLE_NAME INDEX_NAME STATUS DOMIDX_STATU DOMIDX
-------------------- -------------------- -------- ------------ ------
COLA_MARKETS COLA_SPATIAL_IDX VALID VALID VALID
*/


select * from user_sdo_index_info;   --TARGET database

-- no rows selected <<<<<<<<<<<<<<<< PROBLEM

-- In the source database:
select * from all_sdo_index_info;

/*
SDO_INDEX_OWNER INDEX_NAME TABLE_OWNER
-------------------------------- -------------------- --------------
TABLE_NAME COLUMN_NAME SDO_INDEX_TY SDO_INDEX_TABL SDO_INDEX_STAT
-------------------- -------------- ------------ -------------- --------------
SDOBUG COLA_SPATIAL_IDX SDOBUG
COLA_MARKETS SHAPE RTREE MDRT_12745$ VALID
*/


select * from USER_SDO_INDEX_METADATA;
-- no rows selected


 

Changes

 

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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.