Oracle Scratchpad

December 26, 2013

Current row

Filed under: Oracle,Partitioning — Jonathan Lewis @ 9:52 pm BST Dec 26,2013

Here’s a question that I’ve had on my todo (and draft posts) list for a few years – so I’m presenting it as a task for anyone who can demonstrate the answer.

If you’ve got a pl/sql cursor open and you’re using the “update current of” syntax, what happens if you update the same row twice but the row comes from a partitioned table and moves to a new partition on the first update ?

If you have a demo that you want to include in the comments then start with “sourcecode” and end with “/sourcecode” – in square brackets, without the quotation marks – to get a fixed font format and space preservation. If you have a good demonstration or reference article that you can link to, a simple URL will do nicely.#

9 Comments »

  1. :-) ORA-08006: specified row no longer exists

    create table test33 (val number) partition by list (val) (partition p1 values (1), partition p2 values (2)) enable row movement;
    
    insert into test33 values (1);
    commit;
    
    declare
      cursor cur is select * from test33 for update;
    begin
      for rec in cur loop
        update test33 set val = 2 where current of cur;
        update test33 set val = 1 where current of cur;
      end loop;
      commit;
    end;
    /

    Comment by Valentin Nikotin — December 26, 2013 @ 10:41 pm BST Dec 26,2013 | Reply

    • Funny, but with 10046 enabled I don’t get any error. Just “WHERE ROWID = :B1″ is used and second update update 0 rows.

      Comment by Valentin Nikotin — December 26, 2013 @ 10:50 pm BST Dec 26,2013 | Reply

      • Here is how it looks from 10928:

        00108: INSTC2      GF[16], DS[0]+448, FP+200
        00118: BIND        #3, 0, SQLT_RDD(104), #0, <IN:SCL:FMD:COC>
        00130: EXECC       #3, 0x20
           Exception handler: SPECIAL Level 1 Line 4-4.  PC 60-141.
        00142: CLOSC       #0, FALSE, 0

        Comment by Valentin Nikotin — December 27, 2013 @ 9:57 pm BST Dec 27,2013 | Reply

  2. I hope that I did not misunderstand the task, but this is what I get (sorry for the german error messages):

    -- the list partitioned table with row movement enabled
    drop table t;
    
    create table t (
        id number
      , grp number
    )
    partition by list (grp) (
        partition part1 values(1)
      , partition part2 values(2)
      , partition part3 values(3)
      , partition part4 values(4)
      , partition part5 values(5)
    )
    enable row movement;
    
    insert into t
    select rownum id
         , mod(rownum, 4) + 1 grp
      from dual
    connect by level <= 10000;
    
    commit;
    
    -- and now a small pl/sql block changing the partition key twice
    declare
    
    cursor tcursor is
    select * from t
    for update;
    
    begin
    
    for trecord in tcursor loop
    
    update t set grp = grp + 1
    where current of tcursor;
    
    update t set grp = grp - 1
    where current of tcursor;
    
    end loop;
    
    end;
    /
    
    -- and the result
    FEHLER in Zeile 1:
    ORA-08006: Die angegebene Zeile ist nicht mehr vorhanden
    ORA-06512: in Zeile 14
    

    Reminds me of http://jonathanlewis.wordpress.com/2013/11/08/row-movement/.

    Comment by Martin Preiss — December 26, 2013 @ 11:17 pm BST Dec 26,2013 | Reply

  3. Thanks Martin and Valentin.

    These things only take a few minutes – until they start to take a couple of hours because you decide to run the test again with tracing enabled (or some other feature).

    I love (satirical use of word) the way that the traced version and the untraced versions behave differently: do I have the time to investigate the 10928 and combo 10046, with possibly side effects of delayed segment creation to see if I can figure out what’s gone wrong / right ? (Answer – NO).

    Comment by Jonathan Lewis — December 31, 2013 @ 12:17 pm BST Dec 31,2013 | Reply

    • Valentin,

      I don’t know which versions you’ve tested, but I’ve now run your script (with and without 10046) on 10.2.0.5, 11.1.0.7, 11.2.0.4 and 12.1.0.1 and it does the same thing across all four versions – viz: ORA-08006 when not traced, success with only the first update when tracing enabled.

      Comment by Jonathan Lewis — December 31, 2013 @ 5:47 pm BST Dec 31,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,089 other followers