Hi all. I'm working on a 10.2.0.3 / RHEL 5.3 data warehouse that has been acting quirky during nightly load tests. There are 8 tables that are each partitioned by a date range and a location field (6 locations in all). Each night all 6 facilities push text files to the server that are processed by perl scripts which output ext tables. I've isolated each location to have its own text files and own process to avoid clobbering files between locations.
During a nightly load test, 7 tables are successfully loaded from my ext load tables to my partitioned warehouse tables. One table in the middle has no rows in the morning. As I check my log, each location has successfully loaded thousands of rows into this table (using SQL%ROWCOUNT after my select into). I can also see the applicable partition file size and usage rise by the expected size. Still there are no rows visible in the table. The first time I experienced this, bouncing the instance made my rows magically appear. Now bouncing does nothing. After several manual reproductions of this fluke, I'm at a loss. Can anybody recommend a log file I might be missing or a patch that could address this?
But the nightly cron load jobs are different sessions. Are you saying that SQL%ROWCOUNT is reporting a row count before some error is encountered and not reported? There is no error reported in my logs and my load procedure completes successfully.
I always hate to have those bonehead "is it plugged in" problems but there was a copy/pasted brainfart deletion hidden in the bottom of my procedure. Though I did have the problem where a bounce showed missing rows - must have been an uncommitted transaction as you say.