Re: split partition causes ORA-8103 Object no longer exists

From: Adam Musch <ahmusch_at_gmail.com>
Date: Thu, 7 Jan 2010 08:32:30 -0600
Message-ID: <516d05a1001070632s37958134pbed4b4d934a9d386_at_mail.gmail.com>

Operations during a split partition operation have been tricky since the days of Oracle 9. After all, Oracle might create a new partition as you've named to put rows into, or it might create a new MAXVALUE partition to move the rows that violate the new partition's high_value. Further, it's doing local index maintenance on those partitions, so it's creating temporary segments which magically become indexes at the end of the operation.

I suspect the plan and execution of "select count(*) from SAITAB" is using an paritition-by-partition index scan against an index with a not-null column, and the index partitions / temporary segments being referenced during parse/optimization aren't the same as those during execution and rows are being moved between segments during execution of the cursor. So the "object no longer exists" is probably correct; it's simply incomplete -- it's the temporary segment that no longer exists, which is what Oracle isn't telling you. Heck, telling you that temporary segment 154.12254 no longer exists wouldn't do you any good in the first place, because you don't know what it turned into!

That's wouldn't be an issue for an ADD PARTITION operation, because the partition didn't exist, so any rows in that partition can safely be ignored. Oracle will roll back the blocks in all the existing partitions to the appropriate SCN to get consistent data, and it wouldn't have included the new partition in the parse/optimize/execute phases.

The only really safe way I've seen to do this is to prefix each partition split operation with an LOCK TABLE x PARITION p_maxvalue IN EXCLUSIVE MODE WAIT which will get the spin until it can lock the partition -- which there shouldn't be any activity against in the first place -- then perform the split, which will release the lock when done. All the other sessions will have to wait, but I'd rather they waited than bomb out with an error.

It's not uncommon to use some sort of scheduler to parse out the high-value of a partition (or the high-value excluding the MAXVALUE partition) and ensure that there are partitions that exist anywhere from several days to a month ahead of "right now". I know I've written PL/SQL to do it myself. Interval partitioning is supposed to solve the "smart partitioning" issue, but if it and reference partitioning are incompatible, then you either have to write it yourself or give up reference partitioning.

On Wed, Jan 6, 2010 at 5:23 AM, Andre van Winssen <dreveewee_at_gmail.com> wrote:
> Hi list,
>
> topic: range partitioning, MAXVALUE, split partition, select COUNT(*) and
> ORA-8103
>
> here's a scenario where using MAXVALUE literal, the virtual infinite value
> that can be used as non-inclusive upper bound value, for the highest
> partition does not contribute to application availability. (see also
> discussion on "added value" of MAXVALUE to availability in the CDOS thread
> http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e40a874d38a7cd36?pli=1
> , the reason I include Daniel Morgan in this email). Interval partitioning
> cannot be used since we are also using reference partitioning for one of the
> child tables of our SAITAB table used below and interval partitioning on a
> parent table and reference partitioning on child tables do not travel
> together.
>
> My observation: alter table split partition causes "ORA-08103 object no
> longer exists" in other sessions referencing the same table using COUNT(*)
>
> $ oerr ora 8103:
> 08103, 00000, "object no longer exists"
> // *Cause:  The object has been deleted by another user since the operation
> //          began, or a prior incomplete recovery restored the database to
> //          a point in time during the deletion of the object.
> // *Action: Delete the object if this is the result of an incomplete
> //          recovery.
>
> following was executed in 11gR2:
>
> at time 1:
> ==========
> session 1
> -----------
> SQL> select count(*) from saitab ;
>
>   COUNT(*)
> ----------
>   219780445
>
> at time 2:
> ==========
> session 2
> ----------
>   adds 50 partitions to saitab with statements like following :
>     ALTER TABLE SAITAB SPLIT PARTITION SAI_P_MAX AT
> (TO_DATE('20100308','yyyymmdd'))
>     INTO (PARTITION SAI_P_20100307 , PARTITION SAI_P_MAX) UPDATE GLOBAL
> INDEXES;
>
> session 1 (while session's 2 split partitions is still running) runs:
> ----------
> SQL> select count(*) from saitab;
> select count(*) from saitab
>                      *
> ERROR at line 1:
> ORA-08103: object no longer exists
>
> at time 3:
> ==========
> session 1
> ----------
> has waited until session 2's split partitions of table saitab is finished
> and again does:
>
> SQL> select count(*) from saitab;
>
>   COUNT(*)
> ----------
>   219780445
>
> successful again!
>
> the ORA08103 does not occur when the partition with MAXVALUE is dropped and
> we use:
> ALTER TABLE SAITAB add PARTITION SAI_20100404 values less than
> (to_date(20100405,'yyyymmdd')).
>
> what remains is a smart procedure to add new partitions before oracle needs
> them ;-)
>
> regards,
> Andre
>

-- 
Adam Musch
ahmusch_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 07 2010 - 08:32:30 CST