topic Arcsoc.exe executing sql statement in oracle in Geodatabase Questions

Arcsoc.exe executing sql statement in oracle

Mehretab — Thu, 10 Jan 2019 09:34:00 GMT

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 12.1.0.2.0

Thanks!

Mehretab

Re: Arcsoc.exe executing sql statement in oracle

George_Thompson — Thu, 10 Jan 2019 12:46:53 GMT

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?

Geodatabase‌

Re: Arcsoc.exe executing sql statement in oracle

Mehretab — Thu, 07 Feb 2019 12:52:37 GMT

Thanks.

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.

cheers

Re: Arcsoc.exe executing sql statement in oracle

Mehretab — Thu, 02 May 2019 08:32:07 GMT

The fix for the above mentioned BUG is released and we are able to use our Oracle user with read only permission, but the core problem remains the same.

Re: Arcsoc.exe executing sql statement in oracle

VolkanTopaloğlu — Mon, 16 May 2022 07:31:06 GMT

We are having the same problem. Anyone know the solution?

AWR report sql text :

BEGIN /* ArcSDE plsql */ :rval:= dbms_utility.get_parameter_value(:nval, :ival, :sval);:sql_code := SDE.sde_util.SE_SUCCESS; EXCEPTION WHEN OTHERS THEN :sql_code := SQLCODE; :error_string := SQLERRM; END;

Thaks...