Re: error ora-01466 and partitions

Hi Frédéric,

Oracle's read-committed transaction isolation model requires that changes
to the data since a transaction's snapshot SCN must not be visible to that
transaction. Similarly, changes to the metadata of referenced tables must
also not be visible. Otherwise, you could for example exchange partitions
and thus introduce data to the table that was committed before the snapshot
SCN, but that ought not to be read for the query because it was not part of
that table at the snapshot SCN. Similarly, you might have dropped partitions
that ought to have been read for the query. This error prevents such wrong
results from being returned. It has to be based on the SCNs alone, because
Oracle cannot tell in retrospect which partitions were affected by the metadata change(s), and whether they would have been needed for the query.
You know that, but Oracle does not (at least, not in a way that's useful).

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/          For DBAs
@   http://www.christianity.net.au/   For all

On 2003.06.14 06:51 Frederic Payant wrote:
> [ oracle 8.1.7.0 entreprise - Solaris 8 (sparc) ]
>
> Hello,
> At random times, I receive a few ORA-01466 errors from my database
> (one or two a week)
> Oradoc says : query parsed after tbl (or index) change, and
> execute w/old snapshot. My database is a production one, so
> table/index definitions are not changed and time of the server is not
> modified.
> Some DBA says me that it could be caused by partitioning routines.
> I use partitioned tables, 1 partition by day, and every night, 1
> partition is created and 1 is droped. But the new partition is create
> 10 days before beeing used, and dropped partition was used 90 days
> ago.
> Can creation/drop of partitions create such error (even if nothin is
> working in partitions that are to be dropped)
>
> Thanks for your help
>
>
> Amicalement
> Frédéric PAYANT
Received on Fri Jun 13 2003 - 20:57:18 CDT