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

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