| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Wrong Results Bug in Oracle 8.1.7.1
I recall a similar situation with Oracle 7.3 and parallel index creations
where the row source and output would get reversed. Index scans would return
now rows (The developer called with 'I just created and index and now the
data is all gone'). If a FTS works, but not the index, that tells me that
the contents of the index are toast. If a rebuild does not work, sounds like
a bug...
Daniel W. Fink
Sr. Oracle DBA
MICROMEDEX
303.486.6456
-----Original Message-----
Sent: Monday, June 10, 2002 1:39 PM
To: Multiple recipients of list ORACLE-L
These types of problems can sometimes be resolved with an index rebuild, btw.
hth,
Paul
--- www.pythian.com -- vallee_at_pythian.com -- 877-PYTHIAN Smarter than adding another team member, Pythian has new services for supplementing DBAs: get our help with monitoring, 24x7 on-call, daily verifications, storage management, performance and more. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, June 10, 2002 2:43 PM We had this same problem last year. The answer was to upgrade to 8.1.7.2 which came with its own set of bugs which required we go to 8.1.7.3. We had run for months in production before we ran into the bug. Once we hit it, there was no way to avoid it. The only option was to upgrade. I don't recall all of the specifics any more, but if it is important, I can check with the developer. In our environment, the same query run with SQL Navigator and run through a batch job returned a different result set. --- "Aponte, Tony" <AponteT_at_hsn.net> wrote:Received on Mon Jun 10 2002 - 15:03:36 CDT
> We had a similar issue, although it always resulted
> in an ORA-600. It was with descending indexes as
> defined in the Peopletools repository. We had to
> set _IGNORE_DESC_IN_INDEX=TRUE (and
> _DB_FILE_NONCONTIG_MBLOCK_READ_COUNT=1 to comply
> with the Certification Requirements) and recreate
> the development databases. For Production, PS
> Support gave the OK to recreate the indexes without
> having to redo the upgrade.
>
> HTH
> Tony Aponte
>
> -----Original Message-----
> Sent: Monday, June 10, 2002 1:48 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Accounts payable. But this is not a Peoplesoft
> problem, but an Oracle one. I tried the same query
> against another Peoplesoft instance, and it ran
> fine. So there's something more than the view
> involved here. It's in Oracle Support's court now.
>
> I must be living right. It's failing in development
> and working in production.
>
> Ian
>
> -----Original Message-----
> Sent: Sunday, June 09, 2002 7:13 PM
> To: Multiple recipients of list ORACLE-L
>
>
> what modules, if you dont mind me asking, i'm at a
> site where we're
> going to implement HR, Financials And EPM soon.
>
> thanks, joe
>
>
> MacGregor, Ian A. wrote:
>
> >Yep sure is.
> >
> >Ian
> >
> >
> >-----Original Message-----
> >Sent: Sunday, June 09, 2002 5:43 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >Ian, is that peopleslop?
> >
> >joe
> >
> >
> >MacGregor, Ian A. wrote:
> >
> >>SQL> SELECT VOUCHER_ID FROM PS_VCHR_MM_VW WHERE
> ROWNUM < 10;
> >>
> >>VOUCHER_
> >>--------
> >>00003394
> >>00003395
> >>00003396
> >>00003397
> >>00003398
> >>00003399
> >>00003400
> >>00003401
> >>00003402
> >>
>
>>------------------------------------------------------------------------
> >>set feedback on
> >>
> >> 1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW
> >> 2* WHERE VOUCHER_ID = '00003394'
> >>SQL> /
> >>VOUCHER_
> >>--------
> >>
> >>
> >>1 row selected.
>
>>--------------------------------------------------------------------------
--------
> >>Zounds !!! Select dump(voucher_id) shows that
> Oracle is returning a null here.
> >>
> >>Here a function is used to force the query to do
> full tablescans
> >>
> >>1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW
> >> 2* WHERE CONCAT(VOUCHER_ID,'A') =
> CONCAT('00003394', 'A')
> >>SQL> /
> >>
> >>VOUCHER_
> >>--------
> >>00003394
>
>>--------------------------------------------------------------------------
-----------
> >>I get the expected results if I force full table
> scans.
> >>
> >>I looked at the explain plan for the failing query
> and rebuilt the ps_voucher index. This
> >>did not change its erroneous results. I rebuilt
> the view itself again to no avail. A check on
> Metalink revealed bug 1852163. Although this bug's
> conditions were different from the one above, their
> were some similarities. One workaround for bug
> 1852163
> >>
> >>alter session set "_complex_view_merging" = true;
> >>
> >>I tried this and the original query still gave
> improper results.
>
>>--------------------------------------------------------------------------
---------------------
> >>All queries against the component tables of the
> view work fine.
>
>>--------------------------------------------------------------------------
----------------
> >>The view text is
> >>
> >>CREATE VIEW SYSADM.PS_VCHR_MM_VW
> >>AS
> >>SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID,
> A.INVOICE_ID,
> >>A.INVOICE_DT, A.PROCESS_INSTANCE,
> A.ENTRY_STATUS, A.POST_STATUS_AP,
> >>A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG,
> A.ORIGIN FROM
> >>SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C
> WHERE A.BUSINESS_UNIT =
> >>C.BUSINESS_UNIT_AP AND A.VOUCHER_ID =
> C.VOUCHER_ID AND
> >>A.MATCH_ACTION IN ('Y', 'E')
>
>>--------------------------------------------------------------------------
-----------
> >>If I run the select statement outside of the view
> and tack on the 'voucher_id = ' clause
> >>SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID,
> A.INVOICE_ID,
> >>A.INVOICE_DT, A.PROCESS_INSTANCE,
> A.ENTRY_STATUS, A.POST_STATUS_AP,
> >>A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG,
> A.ORIGIN FROM
> >>SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C
> WHERE A.BUSINESS_UNIT =
> >>C.BUSINESS_UNIT_AP AND A.VOUCHER_ID =
> C.VOUCHER_ID AND
> >>A.MATCH_ACTION IN ('Y', 'E')
> >>and a.voucher_id = '00003394'
> >>/
> >>
> >>I get the expected results. The query plan
> matches the one for the failing statement.
>
>>--------------------------------------------------------------------------
--------------------
> >>If I select more than voucher_id from the view
> with the 'voucher_id = ' predicate
> >>the other fields are projected correctly, but
> returns voucher_id as null.
>
>>==========================================================================
=================
> >>
> >>Ian MacGregor
> >>Stanford Linear Accelerator Center
> >>ian_at_slac.stanford.edu
> >>
> >>
> >>
> >
> >
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Joe Testa
> INET: jtesta_at_dmc-it.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
=== message truncated === ===== Pete Barnett Lead Database Administrator The Regence Group pnbarne_at_regence.com __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Barnett INET: regdba_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vallee INET: dbalist_at_pythian.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: Dan.Fink_at_mdx.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |