split partition causes ORA-8103 Object no longer exists

  • From: Andre van Winssen <dreveewee@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 6 Jan 2010 12:23:30 +0100

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

Other related posts: