Some time a go we have noticed that the following sql statement was executed frequently in our Oracle database.
BEGIN /* ArcSDE plsql */ execute immediate 'create table tst_tab(id integer)';execute immediate 'drop table tst_tab';:sql_code := 0; EXCEPTION WHEN OTHERS THEN :sql_code := SQLCODE; :error_string
:= SQLERRM; END;
The statement is executed frequently and it leads to a higher load in our database (exhausts resources - CPU and Memory). It also doesn't seem right because our service do not have edit or such capabilities to write or execute a statement to begin with.
The common point of reference is the use of ArcGISServer 10.5.1 services. We Identified that the statement is executed by an ArcSOC.exe.
Back then the workaround was to give the Oracle user a read only right and that solved the problem, but now we upgraded one of our ArcGIS Server to 10.6.1. As mentioned in this BUG an Oracle user with only read permission is not supported in 10.6.1 yet.
The question is then why the above mentioned statement is executed in our Oracle database in the first place and what can we do to stop it from been executed if we are forced to give our Oracle user a read and wright permission again?
Our environment is:
ArcGIS for Server 10.5.1
Oracle Database 12c Enterprise Edition Release 126.96.36.199.0
I would create a support case for this issue and work with a Geodata specialist.
Looking at the defect that you reference, it has been fixed in ArcGIS Pro 2.2.3. I am not sure when (or if) the fix will updated in ArcMap.
Do you see the same statement in the trace with the ArcMap or ArcGIS Pro client?
As of yet the problem is not addressed in ArcMap or ArcGIS Server 10.6.1. The only options seems to give the oracle user a full permission. The existing Oracle user (read only user) has been used in many different application like Java applications and we do not want to elevate the permission on this user. The question is can we change the connection parameters more precisely the Oracle user of an already published services? I am aware how to update the password of a connection in ArcGIS for Server but I didn't find a clue to change the Oracle user.