Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA- errors EXECUTE IMMEDIATE 'ALTER INDEX <Spatial_Index> REBUILD'

662372Sep 29 2008 — edited Sep 29 2008
Does anyone know why EXECUTE IMMEDIATE 'ALTER INDEX <Spatial_Index> REBUILD' always get error?

SQL> create table TEST_SDO(GEOM MDSYS.SDO_GEOMETRY);

Table created.

SQL>
SQL> INSERT INTO mdsys.user_sdo_geom_metadata (TABLE_NAME,COLUMN_NAME,DIMINFO,SR
ID) VALUES(
2 'TEST_SDO','GEOM',
3 mdsys.SDO_DIM_ARRAY(
4 mdsys.SDO_DIM_ELEMENT('X',-100000,5000000,0.005),
5 mdsys.SDO_DIM_ELEMENT('Y',-100000,5000000,0.005)
6 ),NULL );

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> CREATE INDEX TEST_SDO_Rtree_idx ON TEST_SDO(GEOM) INDEXTYPE IS MDSYS.SPATIA
L_INDEX;

Index created.

SQL> ALTER INDEX TEST_SDO_Rtree_idx REBUILD;

Index altered.

SQL> CREATE or REPLACE PROCEDURE TEST_SDO_INDEX
2 is
3 BEGIN
4 EXECUTE IMMEDIATE 'ALTER INDEX TEST_SDO_Rtree_idx REBUILD';
5 COMMIT;
6 END;
7 /

Procedure created.

SQL> exec TEST_SDO_INDEX;
BEGIN TEST_SDO_INDEX; END;

*
ERROR at line 1:
ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-29400: data cartridge error
ORA-01031: insufficient privileges
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MDRT_4394A6$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE TABLE GISDATA.MDRT_4394A6$ (NODE_ID
NUMBER, NODE_LEVEL NUMBER, INFO BLOB) LOB (INFO) STORE AS (CACHE) NOLOGGING
PCTFREE 2
ORA-29400: data cartridge error
ORA-01031: insufficient privileges
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 266
ORA-06512: at "GISDATA.TEST_SDO_INDEX", line 4
ORA-06512: at line 1

The Oracle is 10.2.0.2.0
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 27 2008
Added on Sep 29 2008
5 comments
1,295 views