Hi there,
We've got a an ArcGIS Server 10.7.1 environment and an Oracle database version 19.6.0.0.0 for spatial and alphanumeric data. This database is enabled as a geodatabase using ST_GEOMETRY. There we store a materialized view (partitioned and with a spatial index) and an auxiliary table with the same configuration and attributes as the aforementioned view. Neither of them are registered in the geodatabase. In order to update the auxiliary table (with spatial index as well) we managed to develop a SQL procedure as it follows:
DROP INDEX USER.VM_SIDX_AUX;
TRUNCATE TABLE USER.VM_AUX;
INSERT INTO USER.VM_AUX
SELECT
CAST(1 AS NUMBER(1)) AS part_id,
tx.TX_NOMBRE AS text,
pt.SHAPE
FROM
USER.AUX_POINTS pt,
USER.AUX_TXT tx
WHERE
pt.TXID = tx.OBJECTID;
COMMIT;
CREATE INDEX USER.VM_SIDX_AUX ON USER.VM_AUX ("SHAPE") INDEXTYPE IS sde.st_spatial_index PARAMETERS('st_grids=1 st_srid=4326');
ALTER TABLE USER.VM EXCHANGE PARTITION P1 WITH TABLE USER.VM_AUX INCLUDING INDEXES WITHOUT VALIDATION;
Executing the previous code on a SQL Developer session works flawlessly. We'd like to automate the execution via a job scheduler, so we prepared the following procedure:
PROCEDURE refresh_VM IS
v_exchange_stmt varchar2(32767);
BEGIN
execute immediate('DROP INDEX "USER"."VM_SIDX_AUX"');
execute immediate('TRUNCATE TABLE "USER"."VM_AUX"');
execute immediate('ALTER SESSION ENABLE PARALLEL DML');
INSERT INTO "USER"."VM_AUX"
SELECT
CAST(1 AS NUMBER(1)) AS part_id,
tx.TX_NOMBRE AS text,
pt.SHAPE
FROM
USER.AUX_POINTS pt,
USER.AUX_TXT tx
WHERE
pt.TXID = tx.OBJECTID
COMMIT;
execute immediate('CREATE INDEX "USER"."VM_SIDX_AUX" ON "USER"."VM_AUX" ("SHAPE") INDEXTYPE IS sde.st_spatial_index PARAMETERS(''st_grids=1 st_srid=4326'')');
dbms_stats.gather_table_stats(ownname=> 'USER', tabname=> 'VM_AUX');
v_exchange_stmt := 'ALTER TABLE "USER"."VM" '||
'EXCHANGE PARTITION P1 '||
'WITH TABLE "USER"."VM_AUX" '||
'INCLUDING INDEXES WITHOUT VALIDATION';
execute immediate (v_exchange_stmt);
END refresh_VM;
The procedure works well too on an active SQL Developer session executing manually the job on the console, i.e.:
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => '"USER"."DBMS_JOB$_2"', USE_CURRENT_SESSION => TRUE);
END;
The problem arises when running the procedure directly from the job scheduler without being on an active SQL Developer session. The exact output is:
ERROR: ERROR IN REFRESH_VM: In exchange partition of USER.VM_AUX by USER.VM.P1 ORA-29955: an error occurred during execution of routine ODCIINDEXEXCHANGEPARTITION
ORA-20083: Parameter ST_SRID does not exist in ST_SPATIAL_REFERENCES table.
ORA-06512: in "SDE.SPX_UTIL", line 1232
ORA-06512: in "SDE.ST_DOMAIN_METHODS", line 2822
ORA-06512: in line 2
But this only happens when trying to create the spatial index. If you use the procedure to create an index on any other attribute or field it perfectly works from the job scheduler. Additionally, we set proper permissions over any SDE object for the user which executes the job (i.e. DBA_JOB_USER) and the "USER" which owns the table:
select distinct 'grant all on "SDE"."'||object_name||'" to "{DBA_JOB_USER|USER}" ;'
from dba_objects
where owner = 'SDE'
and object_type in ('PACKAGE BODY',
'TYPE BODY',
'INDEXTYPE',
'PACKAGE',
'FUNCTION',
'LIBRARY',
'SEQUENCE',
'TYPE',
'OPERATOR',
'TABLE',
'VIEW')
;
We would appreciate if someone could shed some ligth on this particular issue. Thanks,
Kepa
