Re: Transportable Tablespaces and Deferred segment creation "bug"

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 26 Nov 2012 15:44:58 +0000
Message-ID: <CABe10sb0qey-wxJB4WcXzZUguiwTUEbtNnQr-mNLEQmq3z8XUQ_at_mail.gmail.com>



I ranted about this sort of thing at
http://orawin.info/blog/2010/04/25/new-features-new-defaults-new-side-effects/and on this OTN thread
https://forums.oracle.com/forums/message.jspa?messageID=4254100#4254100 a while back. Its not a bug its a feature! There's a workaround I haven't tried at the end of the OTN thread..

On Mon, Nov 26, 2012 at 10:54 AM, Norman Dunbar <oracle_at_dunbar-it.co.uk>wrote:

> Morning all,
>
> Oracle versions are 11.2.0.3 Enterprise and Standard on Linux x86-64.
> May affect others.
>
> I'm fighting/discussing with Oracle Support at the moment on a potential
> bug in the use of Transportable Tablespaces exporting from an Enterprise
> Edition database and importing into a Standard Edition.
>
> I need to do this because I (yes, me!) inadvertently assigned the wrong
> Oracle Home while creating a 6.5 Tb database and built it with
> Enterprise rather than Standard edition (it was a naming convention that
> is, ahem, useless, that caused this error).
>
> Anyway, Enterprise comes with deferred_segment_creation defaulted to
> true, so creating tables and/or indexes doesn't create a segment until
> the first row is added to the table.
>
> Standard Edition also comes with the default set to true, but Standard
> Edition ignores the parameter and all segments get allocated on initial
> creation of the table/index.
>
> Doing a Transportable Tablespace export works fine, doing the import
> results in a number of IMP-00017 errors caused by ORA-01647 "Tablespace
> is read only, cannot allocate space in it" errors. It's not the most
> helpful error message in the world, all the tablespaces being imported
> are read only because they don't actually exists until the import if
> done - so I couldn't make them read write even if I wanted to!
>
> All the tables throwing this error on the import are empty in the source
> (Enterprise) database and don't have a segment allocated for them in
> DBA_SEGMENTS due to the default setting.
>
> I've written it up here if anyone suffers something similar.
>
>
> http://qdosmsq.dunbar-it.co.uk/blog/2012/11/oracles-deferred-segment-allocation-breaks-transportable-tablespace-imports/
>
> There is nothing in Oracle's docs to say that this will happen, even the
> engineer on the call at MOS was unable to find any. I've requested that
> this be logged as a bug, but I don't hold out much hope!
>
>
> Cheers,
> Norm.
>
> --
> Norman Dunbar
> Dunbar IT Consultants Ltd
>
> Registered address:
> Thorpe House
> 61 Richardshaw Lane
> Pudsey
> West Yorkshire
> United Kingdom
> LS28 7EL
>
> Company Number: 05132767
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 26 2012 - 16:44:58 CET

Original text of this message