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