My Oracle Support Banner

Different Dimension Names (SDO_DIMNAME) in Spatial Metadata (USER_SDO_GEOM_METADATA) after Import (Doc ID 2051295.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 10.2.0.5 to 11.2.0.4 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

On 10.2.0.5 (Source DB) -

1) Create a Table and a Spatial index

drop user scott cascade;

create user scott identified by scott default tablespace users
temporary tablespace temp quota unlimited on users;
grant dba to scott;

connect scott/scott

-- Create a Table

CREATE TABLE T_POI(
POI_CODE             NUMBER   NOT NULL,
LOCATION        MDSYS.SDO_GEOMETRY not null,
CONSTRAINT PK_POI_1 PRIMARY KEY ("POI_CODE")
);


-- Insert the metadatafor Spatial Index

INSERT INTO USER_SDO_GEOM_METADATA VALUES
('T_POI','LOCATION',
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('Longitude', -180, 180, .00000005),
                SDO_DIM_ELEMENT('Latitude', -90, 90, .00000005)
                ), 8301
);


select * from USER_SDO_GEOM_METADATA;

    TABLE_NAME
    ------------------------------------------------------------------------------------------------
    COLUMN_NAME
    ----------------------------------------------------------------------------------------------------
    DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
    ----------------------------------------------------------------------------------------------------
          SRID
    ----------
    T_POI
    LOCATION
    SDO_DIM_ARRAY(SDO_DIM_ELEMENT('Longitude', -180, 180, .00000005), SDO_DIM_ELEMENT('Latitude', -90, 90, .00000005))
          8301
          

-- Create a Spatial index

CREATE INDEX IDX_POI_LOCATION ON T_POI(LOCATION)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('LAYER_GTYPE=POINT sdo_dml_batch_size=1' );


2) Export the schema

create or replace directory scott_dir as '/home/oracle/work';
exit

expdp scott/scott directory=scott_dir schemas=scott dumpfile=scott.dmp


3) Check the USER_SDO_GEOM_METADATA from the dump file

impdp system/oracle directory=scott_dir dumpfile=scott.dmp sqlfile=scott_sql.out

view /home/oracle/work/scott_sql.out

    --------------
    mdsys.sdo_meta.change_all_sdo_geom_metadata(schema_name, 'T_POI','LOCATION',
                                                mdsys.SDO_dim_array(MDSYS.SDO_DIM_ELEMENT('X','-180','180','.00000005'),
                                                                    MDSYS.SDO_DIM_ELEMENT('X','-90','90','.00000005')),'8301')
                                                                                          ***
    INSERT INTO USER_SDO_GEOM_METADATA values
       ('T_POI','LOCATION',mdsys.SDO_dim_array(MDSYS.SDO_DIM_ELEMENT('X','-180','180','.00000005'),
                                               MDSYS.SDO_DIM_ELEMENT('X','-90','90','.00000005')),'8301') ;
                                                                     ***
    --------------
    ==>
    The 1st parameter of SDO_DIM_ELEMENT is different from source db.


On 12.1.0.1 (Target DB) -

4) Import the schema

sqlplus / as sysdba
create or replace directory scott_dir as '/home/oracle/work';
exit

impdp system/oracle directory=scott_dir dumpfile=scott.dmp

sqlplus scott/scott

select * from USER_SDO_GEOM_METADATA;

    TABLE_NAME
    ------------------------------------------------------------------------------------------------
    COLUMN_NAME
    ----------------------------------------------------------------------------------------------------
    DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)                                                        SRID
    ---------------------------------------------------------------------------------------------------- ----------
    T_POI
    LOCATION
    SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, .00000005), SDO_DIM_ELEMENT('X', -90, 90, .00000005))        8301
                                  ***                                         ***
    ==>
    The 1st parameter of SDO_DIM_ELEMENT is different from source db.


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
Cause
Solution


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.