| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: LOBs, PCTVERSION, and ORA-22924: snapshot too old
A copy of this was sent to boulke_at_globalnet.co.uk (keith boulton)
(if that email address didn't require changing)
On Sun, 28 Mar 1999 13:35:29 GMT, you wrote:
>On Sat, 27 Mar 1999 17:33:13 -0000, "Jonathan Lewis"
><jonathan_at_jlcomp.demon.co.uk> wrote:
>
>>Dan Halbert has a process that is deleting his rows,
>>not changing the LOBS which are part of the row.
>>Perhaps it is the attempt to roll back the row header
>>deletion that is causing the snapshot problem rather
>>than access to the blob per se.
>
>I did wonder about that, but I didn't know enough about blobs or have
>time to experiment with it.
>
>>Now I have a choice - do I experiment with blobs
>>or do I have to find out the anomaly with row migration
>>in Partition tables ?
>
>Sorry about that, but (until 8i?) rows are not automatically migrated
>between partitions when the partition key is changed. This is
>something I would have expected in particular because the last time I
>looked at it I would have liked a two partition table in which one
>partion was current active items and the other was a much larger
>historical partition of processed items.
Even then its not 'automatic' (eg: its not the default but an option). the row will not 'migrate' in the sense that rows migrate in Oracle today (when a row migrates due to not fitting on a block -- it retains its rowid and all). A migrated row in Oracle8.0 and below appears no different to an application then any other row.
In Oracle8i, release 8.1, the row can MOVE -- this is different then migrate and may affect some applications (eg: any app that uses rowids and perhaps others). When the partition key is modified to make the row move from one partition to another, it is deleted from the original partition and inserted into the new (although only an UPDATE trigger will be fired) -- it is not migrated, its moved. The rowid *changes*. This side effect might have repercusions in other parts of the system -- just something to be aware of.
Here is an example:
SQL> CREATE TABLE partitioned
2 ( x int,
3 y int,
4 z DATE
5 )
6 PARTITION BY RANGE (z)
7 (
8 PARTITION part_1 VALUES LESS THAN(to_date('01-jan-1995','dd-mon-yyyy')),
9 PARTITION part_2 VALUES LESS THAN(to_date('01-jan-1996','dd-mon-yyyy'))
10 )
11 enable row movement;
Table created.
SQL> insert into partitioned values
2 ( 1, 1, to_date('01-jan-1994','dd-mon-yyyy') );
1 row created.
SQL> insert into partitioned values
2 ( 2, 1, to_date('01-mar-1995','dd-mon-yyyy') );
1 row created.
SQL> commit;
Commit complete.
SQL> select rowid, a.* from partitioned partition(part_1) a;
ROWID X Y Z ------------------ ---------- ---------- --------- AAADIJAACAAABb7AAA 1 1 01-JAN-94
SQL> select rowid, a.* from partitioned partition(part_2) a;
ROWID X Y Z ------------------ ---------- ---------- --------- AAADIKAACAAABcAAAA 2 1 01-MAR-95
SQL> select rowid, a.* from partitioned a;
ROWID X Y Z ------------------ ---------- ---------- --------- AAADIJAACAAABb7AAA 1 1 01-JAN-94 AAADIKAACAAABcAAAA 2 1 01-MAR-95
SQL>
SQL> update partitioned set
2 z = decode( x, 1, to_date('01-mar-1995','dd-mon-yyyy'),
3 2, to_date('01-jan-1994','dd-mon-yyyy') )
4 /
2 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL> select rowid, a.* from partitioned a;
ROWID X Y Z ------------------ ---------- ---------- --------- AAADIJAACAAABb7AAB 2 1 01-JAN-94 AAADIKAACAAABcAAAB 1 1 01-MAR-95
SQL> select rowid, a.* from partitioned partition(part_1) a;
ROWID X Y Z ------------------ ---------- ---------- --------- AAADIJAACAAABb7AAB 2 1 01-JAN-94
SQL> select rowid, a.* from partitioned partition(part_2) a;
ROWID X Y Z ------------------ ---------- ---------- --------- AAADIKAACAAABcAAAB 1 1 01-MAR-95
SQL> spoo off
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |