Re: Weird Issue : 01013 - User requested cancel, without ctl-c.

Hi Veera,

Do you have any SQL timeout (in seconds or ms etc...) set at application side? In such cases , I have seen this error... In some case it reports "socket read timeout" for Java based apps.

BR,
Rakesh RA

On Mon, Jun 10, 2019 at 6:00 AM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> H Veera!
>
> The ORA-01013 error means that the executing process has received the
> SIGURG signal. I know that ALTER SYSTEM CANCEL SQL is not supported in
> Oracle 11.2.0.4, but was de facto supported since Oracle 8i. The kill -URG
> <pid> or kill -23 <pid> sent to the server process executing the SQL would
> result in cancelling SQL in pretty much the same way as the 18c command.
>
> I don't know much about HP-UX, but other than someone deliberately sending
> the signal, I can see the following possible causes:
>
> - Resource profile caused by putting the executing user in the profile
> that limits logical reads per session or logical reads per call.
> - The analogous limit imposed by DBMS_RESOURCE_MANAGER instead of
> "CREATE PROFILE" statement.
> - Hitting the address space limit in the Oracle process. Oracle
> 11.2.0.4 will more often than not use direct reads to execute a full table
> scan of the kind you're asking it to do. If there is an OS imposed address
> space limit and your process is hitting it, it may be that.
>
> You are attaching to the wrong process. SQL*Plus is not where you should
> be looking, it's the server process which you can derive from v$process.
> That is all I can give you for now.
>
> Regards
>
>
> On 6/9/19 8:03 PM, Veerabasaiah Ballarimath wrote:
>
> Hi All,
>
> I have ran into a weird issue of ora-01013 for one of our database.
>
> Environment:
> OS: HP-UX 11.31
> Oracle Version: 11.2.0.4
>
> Issue:
> Whenever we run query which usually runs longer fails with the error 1013,
> there is no fixed time of abort, it just cancels the query and session is
> still active.
>
> There is no limit set on the profile and not using any resource managers.
> This doesn't happen when I run the query as sysdba. Raised a ticket with
> Oracle, not much help at the moment.
> I even created a new user and ran the query same thing.
>
> What is done so far:
> ran 10046 trace on the session, no error message reported except when it
> breaks sql*net break.
> And also done the strace on the process, no ctl-c reported there as well.
>
> $ sqlplus test_query
> SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 9 22:54:59 2019
> Copyright (c) 1982, 2013, Oracle. All rights reserved.
> Enter password:
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
> Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
> SQL> select count(*) from siebel.s_asset;
> select count(*) from siebel.s_asset
> *
> ERROR at line 1:
> ORA-01013: user requested cancel of current operation
>
> SQL>
>
> # cat /var/tmp/tusc.out
> ( Attached to process 19220 ("sqlplus test_query") [64-bit] )
> [19220] read(0, 0x6000000000228258, 8192)
> ..................................... [sleeping]
> [19220] write(9, "01% \0\006\0\0\0\0\003^ 15\0\080".., 293)
> ................... = 293
> [19220] read(10, 0x6000000000196926, 8208)
> .................................... [sleeping]
> [19220] read(10, "\0\v\0\0\f\0\0\001\002", 8208)
> .............................. = 11
> [19220] write(9, "\0\v\0\0\f\0\0\001\002", 11)
> ................................ = 11
> [19220] read(10, "\0c9\0\006\0\0\0\0\004\0\0\001\0".., 8208)
> .................. = 201
> [19220] write(1, "s e l e c t c o u n t ( * ) ".., 36)
> .................... = 36
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, " ", 1)
> ..................................................... = 1
> [19220] write(1, "* \n", 2)
> ................................................... = 2
> [19220] lseek(4, 5120, SEEK_SET)
> .............................................. = 5120
> [19220] read(4, "\0\r\0e9\0\0\0V \0ea\0\0\090\0eb".., 512)
> .................... = 512
> [19220] write(1, "E R R O R a t l i n e 1 : ".., 17)
> .................... = 17
> [19220] write(1, "O R A - 0 1 0 1 3 : u s e r ".., 54)
> .................... = 54
> [19220] write(1, "\n", 1)
> ..................................................... = 1
> [19220] write(1, "\n", 1)
> ..................................................... = 1
> [19220] write(1, "S Q L > ", 5)
> ............................................. = 5
> [19220] read(0, 0x6000000000228258, 8192)
> ..................................... [sleeping]
>
> Is there any other diagnostics I can run on the process to identify where
> the cancel is coming from.
>
> Regards
> Veera
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

--
http://www.freelists.org/webpage/oracle-l