Re: Query giving different results using server sqlplus and client sqlplus
From: Zabair Ahmed <roon987_at_yahoo.co.uk>
Date: Thu, 22 Jan 2015 10:37:34 +0000
Message-ID: <1959521238.5399045.1421923054282.JavaMail.yahoo_at_jws11148.mail.ir2.yahoo.com>
Sorry, I should have described the structure of the table.
SQL> select count(*) from report.job;
COUNT(*)---------- 19626224
SQL> And when I run the same queries within a client sqlplus I get the following results. SQL> show userUSER is "SYSTEM"SQL> select name from v$database; NAME---------DV1DW
SQL> select * from v$version;
SQL> select count(*) from report.job;
COUNT(*)---------- 19626224
SQL>
Why would i get different number of rows returned for the first query in each case? Here is the structure of the table and it's the same irrespective of where the sql is executed. SQL> select object_name,object_type,created,status from dba_objects where object_name = 'JOB' and owner = 'REPORT';
TIA
Date: Thu, 22 Jan 2015 10:37:34 +0000
Message-ID: <1959521238.5399045.1421923054282.JavaMail.yahoo_at_jws11148.mail.ir2.yahoo.com>
Sorry, I should have described the structure of the table.
SQL> desc report.job Name Null? Type ----------------------------------------- -------- ---------------------------- IDENTIFIER NOT NULL NUMBER(19) CREATED NOT NULL DATE LASTUPDATED DATE VERSION NOT NULL NUMBER(10) AUDIOLENGTHMS NUMBER(10) AUTHORID VARCHAR2(255) CONFIDENCE FLOAT(126) EFFECTIVEDATE DATE DICTATIONDATE DATE EDITEDDATE TIMESTAMP(6) EDITEDDOCUMENTID VARCHAR2(255) EDITINGDATE TIMESTAMP(6) ERRORRATE FLOAT(126) ERRORRATEFULL FLOAT(126) ERRORRATEWORDSONLY FLOAT(126) FINALIZEDDATE TIMESTAMP(6) HYPCHARACTERS NUMBER(10) INITIALDOCUMENTID VARCHAR2(255) ISTEST NUMBER(1) JOBEXTERNALID VARCHAR2(255) JOBFAILED NUMBER(1) JOBID NOT NULL VARCHAR2(255) LISTITEMSCORRECT NUMBER(10) LISTITEMSDELETED NUMBER(10) LISTITEMSINREF NUMBER(10) LISTITEMSINSERTED NUMBER(10) LISTSCORRECT NUMBER(10) LISTSDELETED NUMBER(10) LISTSINREF NUMBER(10) LISTSINSERTED NUMBER(10) MEDIAID VARCHAR2(255) NUMBOGUSEDITSESSIONS NUMBER(10) NUMEDITSESSIONS NUMBER(10) REFCHARACTERS NUMBER(10) REPORTINGFAILED NUMBER(1) REPORTINGFAILURECODE NUMBER(10) SCHEDULEDDATE DATE SECTIONSCORRECT NUMBER(10) SECTIONSDELETED NUMBER(10) SECTIONSINREF NUMBER(10) SECTIONSINSERTED NUMBER(10) SECTIONSSUBSTITUTED NUMBER(10) SUBSECTIONSCORRECT NUMBER(10) SUBSECTIONSDELETED NUMBER(10) SUBSECTIONSINREF NUMBER(10) SUBSECTIONSINSERTED NUMBER(10) SUBSECTIONSSUBSTITUTED NUMBER(10) TRANSCRIBEDDATE TIMESTAMP(6) TRANSCRIBEDDOCUMENTID VARCHAR2(255) TRANSCRIPTIONPROFILEID VARCHAR2(255) WASEDITED NUMBER(1) WASPICKEDUP NUMBER(1) WASCANCELLED NUMBER(1) WASTRANSCRIBED NUMBER(1) WASFINALIZED NUMBER(1) CUSTOMER NOT NULL VARCHAR2(255) DEPARTMENT VARCHAR2(255) MTSOCUSTOMER VARCHAR2(255) WORKTYPE VARCHAR2(255) AUTHORQSCORE FLOAT(126) ISSPLIT NUMBER(1) PEAKLOAD FLOAT(126) PREDICTEDQSCORE FLOAT(126) PRIORITY FLOAT(126) DOMAIN VARCHAR2(255) FROMLEGACYDB NUMBER(1) ENVIRONMENTID NOT NULL NUMBER(10) ISLIVE NUMBER(1) DICTATIONDURATIONMS NUMBER(19) DRAFTWAITTIMEMS NUMBER(19) DRAFTGENERATIONTIMEMS NUMBER(19) SPLITCHILD NUMBER(1) IGNORE NUMBER(1) FACILITYID VARCHAR2(255) UPDATEDATE TIMESTAMP(6) GROUPID VARCHAR2(255) SERVERDRAFTGENERATIONTIMEMS NUMBER(19) RECOGNIZERID VARCHAR2(255) TRANSCRIPTIONDURATION NUMBER(19) MEDIAQUALITYCODE VARCHAR2(255) FORMATTED_EFFECTIVEDATE DATE FORMATTED_EDITINGDATE DATE FORMATTED_TRANSCRIBEDDATE DATE FORMATTED_EDITEDDATE DATESQL> On Thursday, 22 January 2015, 10:18, Zabair Ahmed <roon987_at_yahoo.co.uk> wrote:
Hello All,
Can somebody help with this, I think am going mad.
Am running some simple select queries 'select count(*) from report.job where transcribeddate < '01-OCT-2014';' and 'select count(*) from report.job;'.
When I run these queries within sqlplus logged directly onto the server where the database is running, I get the following results. Am logged in as system in both cases:-
SQL> show userUSER is "SYSTEM"SQL> select name from v$database;
NAME---------DV1DW
SQL> select * from v$version;
BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionPL/SQL Release 11.2.0.2.0 - ProductionCORE 11.2.0.2.0 ProductionTNS for Linux: Version 11.2.0.2.0 - ProductionNLSRTL Version 11.2.0.2.0 - ProductionSQL> select count(*) from report.job where transcribeddate < '01-OCT-2014'; COUNT(*)---------- 6758430
SQL> select count(*) from report.job;
COUNT(*)---------- 19626224
SQL> And when I run the same queries within a client sqlplus I get the following results. SQL> show userUSER is "SYSTEM"SQL> select name from v$database; NAME---------DV1DW
SQL> select * from v$version;
BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionPL/SQL Release 11.2.0.2.0 - ProductionCORE 11.2.0.2.0 ProductionTNS for Linux: Version 11.2.0.2.0 - ProductionNLSRTL Version 11.2.0.2.0 - Production SQL> select count(*) from report.job where transcribeddate < '01-OCT-2014'; COUNT(*)---------- 18185097
SQL> select count(*) from report.job;
COUNT(*)---------- 19626224
SQL>
Why would i get different number of rows returned for the first query in each case? Here is the structure of the table and it's the same irrespective of where the sql is executed. SQL> select object_name,object_type,created,status from dba_objects where object_name = 'JOB' and owner = 'REPORT';
OBJECT_NAME OBJECT_TYPE CREATED STATUS------------------------------ ------------------- --------- -------JOB TABLE 11-JUL-13 VALIDJOB TABLE PARTITION 11-JUL-13 VALIDJOB TABLE PARTITION 11-JUL-13 VALIDJOB TABLE PARTITION 11-JUL-13 VALIDJOB TABLE PARTITION 11-JUL-13 VALIDJOB TABLE PARTITION 12-OCT-13 VALIDJOB TABLE PARTITION 13-JUN-14 VALIDJOB TABLE PARTITION 13-JUN-14 VALIDJOB TABLE PARTITION 20-NOV-14 VALIDJOB TABLE PARTITION 20-NOV-14 VALIDJOB TABLE PARTITION 20-NOV-14 VALID OBJECT_NAME OBJECT_TYPE CREATED STATUS------------------------------ ------------------- --------- -------JOB TABLE PARTITION 20-NOV-14 VALIDJOB TABLE PARTITION 20-NOV-14 VALIDJOB TABLE PARTITION 09-JAN-15 VALIDJOB TABLE PARTITION 10-JAN-15 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALID OBJECT_NAME OBJECT_TYPE CREATED STATUS------------------------------ ------------------- --------- -------JOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION 12-OCT-13 VALIDJOB TABLE SUBPARTITION 12-OCT-13 VALID OBJECT_NAME OBJECT_TYPE CREATED STATUS------------------------------ ------------------- --------- -------JOB TABLE SUBPARTITION 12-OCT-13 VALIDJOB TABLE SUBPARTITION 12-OCT-13 VALIDJOB TABLE SUBPARTITION 13-JUN-14 VALIDJOB TABLE SUBPARTITION 13-JUN-14 VALIDJOB TABLE SUBPARTITION 13-JUN-14 VALIDJOB TABLE SUBPARTITION 13-JUN-14 VALIDJOB TABLE SUBPARTITION 13-JUN-14 VALIDJOB TABLE SUBPARTITION 13-JUN-14 VALIDJOB TABLE SUBPARTITION 13-JUN-14 VALIDJOB TABLE SUBPARTITION 13-JUN-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALID OBJECT_NAME OBJECT_TYPE CREATED STATUS------------------------------ ------------------- --------- -------JOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALID OBJECT_NAME OBJECT_TYPE CREATED STATUS------------------------------ ------------------- --------- -------JOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION 09-JAN-15 VALIDJOB TABLE SUBPARTITION 09-JAN-15 VALIDJOB TABLE SUBPARTITION 09-JAN-15 VALID OBJECT_NAME OBJECT_TYPE CREATED STATUS------------------------------ ------------------- --------- -------JOB TABLE SUBPARTITION 09-JAN-15 VALIDJOB TABLE SUBPARTITION 10-JAN-15 VALIDJOB TABLE SUBPARTITION 10-JAN-15 VALIDJOB TABLE SUBPARTITION 10-JAN-15 VALIDJOB TABLE SUBPARTITION 10-JAN-15 VALID71 rows selected.
TIA
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 22 2015 - 11:37:34 CET