Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h0SMUmP19760 for ; Tue, 28 Jan 2003 16:30:48 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h0SMUlm19755 for ; Tue, 28 Jan 2003 16:30:47 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id LAA77593; Tue, 28 Jan 2003 11:14:15 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0053C61E; Tue, 28 Jan 2003 10:59:24 -0800 Message-ID: Date: Tue, 28 Jan 2003 10:59:24 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Fink, Dan" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Fink, Dan" Subject: RE: Case of the Missing Rows Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2C700.27C27CE0" ------_=_NextPart_001_01C2C700.27C27CE0 Content-Type: text/plain; charset="iso-8859-1" I'm thinking it is related to the execution plan. When you compute stats, you don't seem to have a problem. Run the same tests and check the execution plans in autotrace. -----Original Message----- Sent: Tuesday, January 28, 2003 10:09 AM To: Multiple recipients of list ORACLE-L Dan: Thanks, I think I got through it, here's what I've found (explanations would be greatly appreciated) 1) Created table as before from Designer/2000 scripts with indexes and constraints 2) SQLLDR to create initial data list 3) SELECT COUNT(*) FROM TB - yields 88640 rows 4) RENAME TB TO TB_HOLD (takes indexes along) 5) SELECT COUNT(*) FROM TB_HOLD - yields only 87257 rows (ahhh nuts) 6) TRUNCATE TABLE TB_HOLD 7) CREATE TB AS SELECT * FROM TB_HOLD - two empty tables 8) SQLLDR into TB 9) SELECT COUNT(*) FROM TB - yields 88640 rows 10) INSERT INTO TB_HOLD (SELECT * FROM TB) - inserts 88640 rows 11) ANALYZE both tables COMPUTE STATISTICS 12) SELECT COUNT(*) FROM TB - yields 88640 rows 13) SELECT COUNT(*) FROM TB - yields 88640 rows 14) EXP - both tables now export 88640 rows What in the world is going on?? Great puzzler for the group to mull over No jobs scheduled except for RMAN level 0 on Friday mornings@ 5:30 and other daily level 1 cumulatives and a weekly full DB export on Fridays @ 2:30 Rick Weiss Oracle DBA -----Original Message----- Sent: Tuesday, January 28, 2003 7:45 AM To: Multiple recipients of list ORACLE-L Rick, Try the following to see which rows are missing. It the same rows are missing each time, perhaps there is a common thread. If not, well.... After Step 2, do a create table as select or sql*plus copy. This will create a backup version. Do a count(*) from each to make sure the numbers agree. After Step 4, select * from table1 minus select * from backup_copy to locate the missing rows. Dan Fink -----Original Message----- Sent: Monday, January 27, 2003 4:54 PM To: Multiple recipients of list ORACLE-L I have a recurring, repeatable problem I was wondering about its cause. Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4 Step 1 - I do an SQLLDR process that loads 88640 rows to a table Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows No one else has access to the database. There are no unusual entries in the alert log. There is nothing I have found in the UDUMP or BDUMP directories that would help. Has anyone else experienced this?? Thanks Rick Weiss ------_=_NextPart_001_01C2C700.27C27CE0 Content-Type: text/html; charset="iso-8859-1" Message
I'm thinking it is related to the execution plan. When you compute stats, you don't seem to have a problem. Run the same tests and check the execution plans in autotrace.
-----Original Message-----
From: Weiss, Rick [mailto:rweiss@state.mt.us]
Sent: Tuesday, January 28, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Case of the Missing Rows

Dan:
 
Thanks, I think I got through it, here's what I've found (explanations would be greatly appreciated)
 
1) Created table as before from Designer/2000 scripts with indexes and constraints
2) SQLLDR to create initial data list
3) SELECT COUNT(*) FROM TB - yields 88640 rows
4) RENAME TB TO TB_HOLD (takes indexes along)
5) SELECT COUNT(*) FROM TB_HOLD - yields only 87257 rows (ahhh nuts)
6) TRUNCATE TABLE TB_HOLD
7) CREATE TB AS SELECT * FROM TB_HOLD - two empty tables
8) SQLLDR into TB
9) SELECT COUNT(*) FROM TB - yields 88640 rows
10) INSERT INTO TB_HOLD (SELECT * FROM TB) - inserts 88640 rows
11) ANALYZE both tables COMPUTE STATISTICS
12) SELECT COUNT(*) FROM TB - yields 88640 rows
13) SELECT COUNT(*) FROM TB - yields 88640 rows
14) EXP - both tables now export 88640 rows
 
What in the world is going on??
 
Great puzzler for the group to mull over
 
No jobs scheduled except for RMAN level 0 on Friday mornings@ 5:30 and other daily level 1 cumulatives and a weekly full DB export on Fridays @ 2:30
 
Rick Weiss
Oracle DBA
-----Original Message-----
From: Fink, Dan [mailto:Dan.Fink@mdx.com]
Sent: Tuesday, January 28, 2003 7:45 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Case of the Missing Rows

Rick,
    Try the following to see which rows are missing. It the same rows are missing each time, perhaps there is a common thread. If not, well....
 
    After Step 2, do a create table as select or sql*plus copy. This will create a backup version. Do a count(*) from each to make sure the numbers agree.
    After Step 4, select * from table1 minus select * from backup_copy to locate the missing rows.
 
Dan Fink
-----Original Message-----
From: Weiss, Rick [mailto:rweiss@state.mt.us]
Sent: Monday, January 27, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Case of the Missing Rows

I have a recurring, repeatable problem I was wondering about its cause.

Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4

Step 1 - I do an SQLLDR process that loads 88640 rows to a table
Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows
Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows
Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows

No one else has access to the database.  There are no unusual entries in the alert log.
There is nothing I have found in the UDUMP or BDUMP directories that would help.

Has anyone else experienced this??

Thanks

Rick Weiss

------_=_NextPart_001_01C2C700.27C27CE0-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan INET: Dan.Fink@mdx.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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).