"Count" from Toad table menu return 0 records, bug

damir.vadas_250 · September 28, 2016, 1:31am

Hi,
Toad 12.9.0.71 x64
Oracle client 11.2.0.3.0 x64
Oracle DB 12.1.0.2.0 - x64

Situation real (from SQLPlus)

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 28 10:23:09 2016

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

10:23:10 >
10:23:10 >SELECT /*+ PARALLEL(4) / count() from RTX_STAGING;

COUNT(*)

64437857

Elapsed: 00:00:19.47
10:23:29 >

And from the Toad, shows 0 records (have checked,refreshed several times...)

SQL spool shows:

Session: Q383211[A1_FAS]@FMS
Timestamp: 10:33:27.926
select count(*) from "A1_FAS"."RTX_STAGING"
Error: ORA-08103: object no longer exists

What i know is that another session in background are dropping partitions (a lot and very often!) and this somehow reflects on Toad execution.
But, Oracle SqlPlus* works OK!

Hope this helps,
Damir

JohnDorlon · September 28, 2016, 6:28am

I just did a google search on that error code and found that it’s not uncommon to get an ORA-08103 when SELECTing from a table that is undergoing partition maintenance. I don’t know of any way to avoid that error in Toad. Remember, it’s Oracle that runs the SQL and returns the error. Toad is just the messenger.

I noticed that in SQLPlus you included the parallel hint. If you run Toad’s query in SQLPlus, do you get the error in SQL*Plus? What about when you the query with the parallel hint in Toad’s Editor?

JohnDorlon · September 28, 2016, 6:29am

One thing I will add though - is that if Toad is getting the error, it should show it instead of “0 records”. I’ll take a look at that.

damir.vadas_250 · September 28, 2016, 6:44am

Hi John,

Sorry was on some meeting and later test this case more properly…

Problem is persistent (not TOad related!) if while query runs other session drop partition which is in plan (and invalidate index) then oracle has report an error even in SQLPlus.

*** 2016-09-28 13:30:32.933
WAIT #140663254640624: nam=‘SQL*Net message from client’ ela= 1042227026 driver id=1413697536 #bytes=1 p3=0 obj#=32030271 tim=68145287707
CLOSE #140663254640624:c=0,e=8,dep=0,type=0,tim=68145287818

PARSING IN CURSOR #140663254640624 len=55 dep=0 uid=70 oct=42 lid=70 tim=68145288065 hv=2655499671 ad=‘0’ sqlid=‘0kjg1c2g4gdcr’
ALTER SESSION SET EVENTS ‘10046 trace name context off’
END OF STMT
PARSE #140663254640624:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=68145288064
EXEC #140663254640624:c=1000,e=247,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=68145288355
ORA-8103 - objd: 32032891 objn: 32032623 tsn: 219 rdba: 0x3e44308a

But as you say, Toad should return error not 0 rows…

damir.vadas_250 · September 28, 2016, 6:49am

Hi John,

Sorry was on some meeting and later test this case more properly…

Problem is persistent (not TOad related!) if while query runs other session drop partition which is in plan (and invalidate index) then oracle has report an error even in SQLPlus.

*** 2016-09-28 13:30:32.933

WAIT #140663254640624: nam=‘SQL*Net message from client’ ela= 1042227026 driver id=1413697536 #bytes=1 p3=0 obj#=32030271 tim=68145287707

CLOSE #140663254640624:c=0,e=8,dep=0,type=0,tim=68145287818

=====================

PARSING IN CURSOR #140663254640624 len=55 dep=0 uid=70 oct=42 lid=70 tim=68145288065 hv=2655499671 ad=‘0’ sqlid=‘0kjg1c2g4gdcr’

ALTER SESSION SET EVENTS ‘10046 trace name context off’

END OF STMT

PARSE #140663254640624:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=68145288064

EXEC #140663254640624:c=1000,e=247,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=68145288355

ORA-8103 - objd: 32032891 objn: 32032623 tsn: 219 rdba: 0x3e44308a

But as you said, Toad should return error not 0 rows… but ORA error!

JohnDorlon · September 28, 2016, 11:57am

I just tried this:

in SQL*PLus: create table X as select * from dba_tables;
in Toad: go to table X in the schema browser, go to the data tab.
in SQL*Plus: drop table X purge;
in Toad, right-click in the grid and choose “record count”.
It gives me the “table or view does not exist” (which is a different error than yours, but it should lead to the same result for record count), THEN I get the “record count: 0” dialog.

I have made a change so that the “Record count: 0” dialog will not be shown in this case. Did you ever see the error message?

damir.vadas_250 · September 28, 2016, 12:21pm

Posted 2 times today and still toad forum delete all mine posts…crazy…

Let us try 3rd time:

It is partitoned table and other sessions are dropping partitions a lot in background. So when oracle start to count using PK, it get an error. here is part of the trace:

*** 2016-09-28 13:30:32.933

WAIT #140663254640624: nam=‘SQL*Net message from client’ ela= 1042227026 driver id=1413697536 #bytes=1 p3=0 obj#=32030271 tim=68145287707

CLOSE #140663254640624:c=0,e=8,dep=0,type=0,tim=68145287818

=====================

PARSING IN CURSOR #140663254640624 len=55 dep=0 uid=70 oct=42 lid=70 tim=68145288065 hv=2655499671 ad=‘0’ sqlid=‘0kjg1c2g4gdcr’

ALTER SESSION SET EVENTS ‘10046 trace name context off’

END OF STMT

PARSE #140663254640624:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=68145288064

EXEC #140663254640624:c=1000,e=247,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=68145288355

ORA-8103 - objd: 32032891 objn: 32032623 tsn: 219 rdba: 0x3e44308a

But as you say, Toad should return error not 0 rows…

Hope this helps now and forum will leave mine post now…

damir.vadas_250 · September 28, 2016, 12:23pm

John,
maybe you didn’t noticed from mine first post:

Toad “SQL spool” shows this:

Session: Q383211[A1_FAS]@FMS
Timestamp: 10:33:27.926
select count(*) from “A1_FAS”.“RTX_STAGING”
Error: ORA-08103: object no longer exists

And thi serror is in the trace as well … but this is partition level …not the whole table.

JohnDorlon · September 28, 2016, 12:24pm

I did get that message earlier by email. I don’t know why it didn’t stick in the forum.

Please see my last reply: I saw error message followed by “record count: 0”. Did you ever see the error message in Toad? Next beta will only show error message.

JohnDorlon · September 28, 2016, 12:26pm

I did see that. You should see an error message no matter which one it is. I dropped the table just because it’s easier.

damir.vadas_250 · September 28, 2016, 12:33pm

No error as shown on the screen shot

Damir Vadas (via mobile phone)

On Sep 28, 2016 21:30, "John Dorlon" bounce-jdorlon@toadworld.com wrote:

RE: "Count" from Toad table menu return 0 records, bug

Reply by John Dorlon
I did get that message earlier by email. I don't know why it didn't stick in the forum.

Please see my last reply: I saw error message followed by "record count: 0". Did you ever see the error message in Toad? Next beta will only show error message.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.