Re: Direct Path Inserts and Partition Splits

Which version of Oracle ? 8.1 or 9.2 ?

It could make a difference because of
the 'free split' change in code that appeared in 9.2.

I don't recall the error I got - but some time back I was working on a partition exchange strategy and testing the effects of tablespace corruption in mid-exchange. One of the scenarios I built managed to leave me with a partition in a non-existent tablespace, but the partition could not be dropped because it didn't exist. Clearly some form of dictionary corruption on partition maintenance - so there are some oddities to be cleaned.

It might also be an idea to check of the OCI program manages to hold a cursor open
that should have been invalidated on the split. Is it possible that when suspended, the
program then pushed its current data set into a partition/data segment that simply did not exist in the data dictionary ?

Can you add a little detail about the structure of the PT and its missing elements. Do you have multiple partitions per day, with some giving 8103 when queried by name; or is it one partition per day, but queries which extend into the gap give the 8103 ?

Have you tried running with a level 4 on 10046 yet to see the actual dictionary accesses that Oracle attempts when looking for the critical data (probably have to bounce the database to get the best effects here); of checked the contents of the obj$, and related tables around the object numbers and data object numbers of the damaged bits to see if there is any data there that does not fit the pattern ?

I tend to disagree with your comment about splitting on the fly being silly, by the way - as soon as Oracle introduced MAXVALUE, they introduced an implicit requirement either for an unbreakable mechanism for doing a split in real-time, or for a mechanism for shunting data above the current high_value into a holding table. (Requiring end-user code to handle rogue data would otherwise introduce a significant overhead on processing times).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23

The CO-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 17 December 2002 23:14

A program inserts via OCI DirectPath telemetry data into a time-based partition every 30 seconds, each insert acquiring an exclusive lock on the table. The users decided to further split the partition receiving the inserts by time. Each of the new partitions is stale, by that I mean their high values are all previous to the present data. The split was done on December 4th carved out partitions for November 26, 27, 28, 29, and 30. While the split is going on, the program buffered the next inserts. Once the split is done the program continues its inserts into the original partition. (During the split Oracle acquires locks which prevent data manipulation)

I realize that splitting a partition on the fly like this is a silly thing to do. It was never going to be part of the production system. However I was not sure if doing so would work. We tried it and the result may have been data dictionary corruption; i.e., ORA-8103 errors. Data in the November 26, November 27, about 22.5 hours of November 28, November 29, November 30 and the input partition remain visible. It's only the 1/5 hours of the November 28th partition which is inaccessible. In a way it's very much like a block corruption problem. Some additional information: all objects in the database are valid, all indexes are usable, besides there were no indexes involved in the split operation. DBV reports no problem with any of the database files.

Oracle support also blames it on the split activity. I am anxious because there is a big push to put this system into production. I need to be sure the corruption was caused by the split. We won't be that silly again. Unfortunately the corruption was not discovered immediately afterwards. The split as the cause is really just a guess. Has anyone else seen unexplained 8103 errors after partition management?

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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_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).