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

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