My Oracle Support Banner

ORA-13203: failed to read USER_SDO_GEOM_METADATA view When Creating An Spatial Index Using Workspace Manager in 19c (Doc ID 2761252.1)

Last updated on APRIL 12, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 19.8.0.0.0 and later
Information in this document applies to any platform.

Symptoms

On : 19.8.0.0.0 version, RDBMS

When attempting to run

SQL> EXEC DBMS_WM.BEGINDDL('DSHW_SECTIE2');

PL/SQL procedure successfully completed.

SQL> CREATE INDEX SI_SECT2_GEOMETRY ON DSHW_SECTIE2_LTS (SECT_GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Index created.

SQL> EXEC DBMS_WM.COMMITDDL('DSHW_SECTIE2');  


The following error occurs when EXEC DBMS_WM.COMMITDDL('DSHW_SECTIE2');   is executed

ERROR
-----------------------
BEGIN DBMS_WM.COMMITDDL('DSHW_SECTIE2'); END;

*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13203: failed to read USER_SDO_GEOM_METADATA view
ORA-13203: failed to read USER_SDO_GEOM_METADATA view

Complete testcase is below:

--testcase
CREATE USER ltdemo
IDENTIFIED BY ltdemo
DEFAULT TABLESPACE SYSEXT
QUOTA 20M ON SYSEXT
TEMPORARY TABLESPACE temp;

GRANT create session TO ltdemo;
GRANT create table TO ltdemo;
GRANT create sequence TO ltdemo;
GRANT wm_admin_role TO ltdemo;
GRANT DBA TO ltdemo;

conn ltdemo/ltdemo

CREATE TABLE DSHW_SECTIE2
(
  SECT_ID           NUMBER                      NOT NULL,
  SECT_GEOMETRY     MDSYS.SDO_GEOMETRY          NOT NULL
);

ALTER TABLE DSHW_SECTIE2
ADD CONSTRAINT pk_sect2
PRIMARY KEY (SECT_ID)
USING INDEX;

exec dbms_wm.enableVersioning('DSHW_SECTIE2', 'VIEW_WO_OVERWRITE');

insert into mdsys.sdo_geom_metadata_table
select
    'LTDEMO',
    tab_cols.table_name,
    tab_cols.column_name,
    mdsys.sdo_dim_array(mdsys.sdo_dim_element('X', -2147483648, 2147483647,
0.0001),
         mdsys.sdo_dim_element('Y', -2147483648, 2147483647, 0.0001)),
    null
    from dba_tab_cols tab_cols
    where tab_cols.data_type = 'SDO_GEOMETRY'
    and owner='LTDEMO'
    and (table_name not like '%_BASE' AND table_name not like '%_DIFF' AND
table_name not like '%_CONS')
    and not exists (
      select 1 from user_sdo_geom_metadata
      where table_name = tab_cols.table_name
      and column_name = tab_cols.column_name
      )
    ;

COMMIT;

EXEC DBMS_WM.BEGINDDL('DSHW_SECTIE2');
    select * from user_sdo_geom_metadata where table_name like
'DSHW_SECTIE%';
    CREATE INDEX SI_SECT2_GEOMETRY ON DSHW_SECTIE2_LTS (SECT_GEOMETRY)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
EXEC DBMS_WM.COMMITDDL('DSHW_SECTIE2');  
EXEC DBMS_WM.COMMITDDL('DSHW_SECTIE2',TRUE); -- is not needed if the first
commitddl is executed without errors

select * from user_sdo_geom_metadata where table_name like 'DSHW_SECTIE%';

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.