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:
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.