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

Last updated on FEBRUARY 08, 2017

Applies to:

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

Symptoms

NOTE: The Transportable Tablespace works fine, the problem is with the Transportable Database mode.

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

Moving data from an 11.2.0.3 database to a 12.1 database using the new 12c transportable database feature.
Got everything transferred to the 12.1 target database except that the spatial indexes are not complete because they are missing metadata from the ALL_SDO_INDEX_METADATA table and the MDRT_$ table is missing.

In Summary: 11.2.0.3 to 12.1.0.1

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 we had 9 <<<<<<<<<<<<< PROBLEM HERE MDRT_12745$ does not exist

/*

Here is the 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;

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

-- In the source we had:
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

Note: The problem reproduces slightly different but still having the problem for 11.2.0.4 and 12.1.0.1 as sources. See Bug 19386381 for details

 

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms