RE: Transportable Tablespace, SCNs and ORA-1555s.

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Thu, 18 Mar 2004 16:39:27 +1100
Message-ID: <007a01c40cab$612ebb50$ae8735cb@ixora.com.au>

Hi Dan,

The problem you are contemplating cannot happen, because when you plug in a set of tablespaces, the SCN is advanced if necessary to ensure that it is higher than that of the source database. So the SCN of your database2 would be bumped up to 100001.

Oracle also records the SCN at the time of the plug in operation in SPARE1 and SPARE2 of TS$. Queries with an older snapshot SCN will not see the new segments. Queries with a later snapshot SCN do not attempt to determine the status of uncommitted transactions unless the block SCN is higher than the plug-in SCN, which it will never be unless the tablespace has been read/write in the new database and the block has been changed, in which case the block will have been completely cleaned out.

There is a similar shortcut for consistent reads against read only tablespaces. The read-only SCN is kept in SCNWRP and SCNBAS of TS$. If the snapshot SCN for a consistent read is more recent than that, nothing needs to be done because all transactions are known to have been committed at that point. Queries with an older snapshot SCN do consistent reads in the normal way.

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

-----Original Message-----
From: Daniel Fink
Sent: Thursday, 18 March 2004 9:38 AM
To: oracle-l_at_freelists.org
Subject: Transportable Tablespace, SCNs and ORA-1555s.

This is not in response to an actual problem, it is just the result of a brain that goes off on tangents way to often...

How does a transaction know NOT to build a consistent version of data that has been transported?

Sequence of events
1) Insert records into table1. A Commit SCN of 100000 is assigned. Due to the size of the insert, there are blocks in the table that still have an Uncommitted state. 2) Alter tablespace1 (containing table1) to read only and transport it to database2.
3) Start a query in database2 (with an SCN of 1000) that accesses the newly transported table1.

Will the query get a 1555?

Yes - It cannot create a read consistent view to SCN 1000 No - It knows that this data came from another database, so it does not attempt to build a read-consistent version

Thoughts on which is correct? I know, I know...build a damn testcase and see for myself. I'm working on it, but I wanted to see what you all thought first.

Daniel

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Mar 17 2004 - 23:36:05 CST