Executing DDL commands explicitly or with EXECUTE IMMEDIATE statement usually require special privileges granted directly (not as a part of a ROLE). With creating tables and indexes, things are really simple, you just need to grant CREATE TABLE privilege and that’s it.
When you need to create a special index, such as SPATIAL index, it is not obvious which privileges need to be granted.
Symptoms of the problem:
SQL> BEGIN
2 EXECUTE IMMEDIATE 'CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX';
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
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_2942E$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE TABLE "SCOTT".MDRT_2942E$
(NODE_ID NUMBER, NODE_LEVEL NUMBER, INFO BLOB) LOB (INFO) STORE AS (NOCACHE)
PCTFREE 2
ORA-29400: data cartridge error
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
ORA-06512: at line 2
Elapsed: 00:00:00.07
Note, that even though the statement failed, Oracle created an object, so before retrying you need to drop it:
SQL> DROP INDEX idx_poi_geometry;
Index dropped.
From the error message, we can conclude that there is something wrong with the tablespace privileges.
And this is not something dynamic SQL specific as the plain DDL command fails with the same error:
SQL> CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
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_29430$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE TABLE "SCOTT".MDRT_29430$
(NODE_ID NUMBER, NODE_LEVEL NUMBER, INFO BLOB) LOB (INFO) STORE AS (NOCACHE)
PCTFREE 2
ORA-29400: data cartridge error
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
After granting UNLIMITED TABLESPACE privilege:
SQL> DROP INDEX idx_poi_geometry;
Index dropped.
Elapsed: 00:00:00.04
SQL> CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
Index created.
Elapsed: 00:00:05.98
Dynamic SQL also works:
SQL> DROP INDEX idx_poi_geometry;
Index dropped.
Elapsed: 00:00:00.36
SQL> BEGIN
2 EXECUTE IMMEDIATE 'CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX';
3 END;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.08
A tricky part comes when you try to execute this dynamic SQL wrapped into a packaged procedure call made by a different user:
SQL> BEGIN
2 EXECUTE IMMEDIATE 'BEGIN scott.pkg_indexes.create_spatial_index; END;';
3 END;
4 /
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
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_29480$] during R-tree creation
ORA-13249: Stmt-Execute
Failure: CREATE TABLE "SCOTT".MDRT_29480$ (NODE_ID NUMBER, NODE_LEVEL
NUMBER, INFO BLOB) LOB (INFO) STORE AS (NOCACHE) PCTFREE 2
ORA-29400:
data cartridge error
ORA-01031: in
PL/SQL procedure successfully completed.
Now it’s time to grant more privileges (as a SYSTEM USER):
SQL> GRANT CREATE TABLE, CREATE SEQUENCE TO scott
2 /
Grant succeeded.
Now let’s execute last dynamic PL/SQL again (the packaged procedure create_spatial_index includes drop index command, so we should not worry about it):
SQL> BEGIN
2 EXECUTE IMMEDIATE 'BEGIN scott.pkg_indexes.create_spatial_index; END;';
3 END;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:39.67
Summary:
To successfully create a spatial index in dynamic SQL (or dynamic PL/SQL), the owner of the index may need the following privileges granted explicitly:
- UNLIMITED TABLESPACE
- CREATE TABLE
- CREATE SEQUENCE