Re: split partition causes ORA-8103 Object no longer exists
- From: Adam Musch <ahmusch@xxxxxxxxx>
- To: dreveewee@xxxxxxxxx
- Date: Thu, 7 Jan 2010 08:32:30 -0600
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@xxxxxxxxx> 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@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
