Oracle Scratchpad

November 29, 2016

Delete/Insert #2

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 4:33 pm GMT Nov 29,2016

In the previous post I threw out a couple of options for addressing the requirement to transfer data from one table to another (“cut and paste” rather than just “copy”) without running into odd inconsistency errors. This triggered of a wonderful comment trail of alternatives based on how large the volume might be, how relaxed the concurrency requirements might be, and so on.

A comment by SydOracle1 picked up on my failure to get Oracle working with the “as of SCN” syntax because it kept reporting ORA-08187 and suggested a straightforward use of the VERSIONS strategy. I thought it was quite surprising that this could work given that “as of SCN” didn’t, so I whipped up a quick test to check it – adding a couple of little refinements to the supplied sample – and it worked.

create table t1
as
select	object_id, object_name, owner
from	all_objects
;

create table t2
as
select	* from t1
where	rownum = 0
;

alter table t1 add constraint t1_pk primary key(object_id);

execute dbms_stats.gather_table_stats(user,'t1')

execute dbms_lock.sleep(5)

That dbms_lock.sleep() is very important for the purposes of this demonstration; it has to be just a few seconds otherwise the references back to earlier SCNs could report error: “ORA-01466: unable to read data – table definition has changed”. This is probably a side effect due to the 3 second interval in the capture that Oracle uses in the table smon_scn_time.

So now we do the following:

  • find the current SCN,
  • start a transaction,
  • get the transaction id,
  • delete the data from the source table,
  • find the current SCN again,
  • insert into the target table the data that was deleted by our transaction in the interval spanned by the two SCNs.

 


rem
rem     insert_delete_2.sql
rem     Jonathan Lewis
rem     Nov 2016
rem

column current_scn new_value m_start_scn 
select to_char(current_scn,'FM999999999999999999999') current_scn from v$database;

variable m_tx_id varchar2(20)
exec :m_tx_id := dbms_transaction.local_transaction_id(true)

column xid new_value m_xid
select xid from v$transaction where xidusn || '.' || xidslot || '.' || xidsqn = :m_tx_id;

delete from t1 where owner = 'SYSTEM';
commit;

column current_scn new_value m_end_scn format 999999999999999999
select to_char(current_scn,'FM999999999999999999999') current_scn from v$database;
 
insert	into t2
select	r.*
from	t1  versions between scn &m_start_scn and &m_end_scn r
where 
	versions_operation = 'D'
and	versions_xid = '&m_xid'
and	owner = 'SYSTEM'
;

commit;

There are a couple of variants on getting the transaction ID – I decided to use a function call to start a transaction without doing any work rather than doing the delete and then finding the transaction id that the delete initiated (I could have linked v$session for my SID to v$transaction after the delete). Because of the choice I made I have to do a little bit of messing around in the subsequent code – the function call returns the transaction ID in the form 31.16.19111 (that’s undo segment, slot number, sequence) but the VERSIONS mechanism wants a transaction ID in its HEX form which, for the example shown, would be ‘1F001000A74A0000’. I could have converted the three part form to the other using a messy bit of to_char(,’XXXXXXXX’) code, but I was feeling a little lazy.

To my surprise I didn’t see any ORA-08187 errors – which made me look back at the notes I had jotted down on the couple of tests I’d initially tried to find out what I had been doing wrong. My first attempt did the insert first then tried to do the delete “as of SCN” and failed, so my second attempt tried to do the delete first just in case the problem related to using “as of SCN” in the middle of a transaction:


column current_scn new_value m_scn

prompt  ============
prompt  Insert first
prompt  ============

select to_char(current_scn,'FM999999999999999999') current_scn from V$database;

insert into t2
select  *
from    t1 as of scn &m_scn r
where
        owner = 'SQLTXADMIN'
;

delete from t1 as of scn &m_scn r
where owner = 'SQLTXADMIN'
;

-- ORA-08171: snapshot expression not allowed here

rollback;

prompt  ============
prompt  Delete first
prompt  ============

select to_char(current_scn,'FM999999999999999999') current_scn from V$database;

delete from t1 as of scn &m_scn r
where owner = 'SQLTXADMIN'
;

-- ORA-08171: snapshot expression not allowed here

insert into t2
select  *
from    t1 as of scn &m_scn r
where
        owner = 'SQLTXADMIN'
;

rollback;

Clearly “delete as of scn” is illegal.

Of course, if I’d gone a little further with this idea I might have tried starting with a delete that didn’t use “as of SCN”, and then the code would have succeeded. In fact, though, this wouldn’t be a perfect solution because it would allow a window for error: some other session might delete a relevant row between my call for SCN and my delete, which means my insert would insert a row deleted by another user.

The code could be modified though in its choice of SCN. Provided I started my transaction with the delete I could then query v$transaction for the start SCN for the transaction, and use that as the “as of” SCN for the insert:


delete from t1
where owner = 'SQLTXADMIN'
;

select
        to_char(
                start_scnw * power(2,32) + start_scnb,
                'FM999999999999999999'
        )       current_scn
from
        v$transaction
where   ses_addr = (
                select  saddr
                from    v$session
                where   sid = (
                        select  sid
                        from    V$mystat
                        where   rownum = 1
                )
        )
;

insert into t2
select  *
from    t1 as of scn &m_scn r
where
        owner = 'SQLTXADMIN'
;

By using the delete to initiate the transaction and set the SCN I think we block any window of inconsistency and, apart from the messy little bit of code that finds the transaction entry, we have an even simpler piece of code than the example give by SydOracle.

Collaboration or, kicking ideas around, is a wonderful way to learn.

 

7 Comments »

  1. Hi Jonathan,

    I think there’s some risk if you commit the delete before inserting into t2, as is shown in lines 16 and 17:

    delete from t1 where owner = ‘SYSTEM’;
    commit;

    The risk is that if the, now released, undo is overwritten by other sessions, you’d get ORA-01555 when issuing a flashback query. I’m assuming data is read exclusively from UNDO, meaning there’s no Flashback Data Archive configured for table t1. If that happens, there’s no possibility to get the deleted rows to table t2 (again, not considering other techniques like Log Miner to reconstruct the deleted data or some other form of recovery).

    I guess it would be better to commit only after inserting into t2.

    Regards,
    Jure Bratina

    Comment by Jure Bratina — November 30, 2016 @ 11:35 am GMT Nov 30,2016 | Reply

    • Jure,

      Very good point, and you’re right. A reminder that the design is not complete until you’ve thought about all the ways in which things could go wrong.

      Funnily enough I didn’t even think about putting the delete into the final example – only when I was experimenting with the original suggestion.

      Comment by Jonathan Lewis — November 30, 2016 @ 11:45 am GMT Nov 30,2016 | Reply

  2. You can use bind variables with the AS OF SCN clause. Your shared pool will thank you. :)

    Comment by Jason Bucata — November 30, 2016 @ 3:20 pm GMT Nov 30,2016 | Reply

    • Jason,

      You can, but it won’t ;) (At least in 11.2.0.4)
      Every execution of a statement (even with the same value for the bind variable) will produce a new child cursor.

      In fact if you do this very frequently and at high concurrency then it might even be better better to use literals for the SCN so that the different versions of the “same” statement are spread across all the available library cache latches.

      Comment by Jonathan Lewis — November 30, 2016 @ 3:37 pm GMT Nov 30,2016 | Reply

  3. Is there a benefit to querying V$MYSTAT instead of SYS_CONTEXT(‘USERENV’,’SID’) to find the sid?

    Comment by Jason Bucata — November 30, 2016 @ 3:27 pm GMT Nov 30,2016 | Reply

    • Jason,

      Probably not (though it might be implemented into a select from dual internally), I just keep forgetting that it’s possible.

      Comment by Jonathan Lewis — November 30, 2016 @ 3:30 pm GMT Nov 30,2016 | Reply

  4. This approach appears to work, at least at first glance:
    DELETE FROM t1 WHERE … RETURNING max(ora_rowscn) INTO :my_scn;

    But I’m trying to convince myself that it works correctly if T1 didn’t have ROWDEPENDENCIES enabled. I think it could, but…

    Comment by Jason Bucata — November 30, 2016 @ 3:44 pm GMT Nov 30,2016 | 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

Blog at WordPress.com.