Re: Transportable Tablespaces and Deferred segment creation "bug"

From: pier paolo Bruno <pbrunoster_at_gmail.com>
Date: Tue, 27 Nov 2012 00:11:20 +0100
Message-ID: <CA+dM1yPGY5HkyWYtwUpnvSiOnRTJXs-g44q6d4WkMSys9qB6Og_at_mail.gmail.com>



Pherhaps it seems stupid .
I imagine that at the end of the procedure to move your instance you have to make an import for loading all not segment objects (view, packages and so on) and i usually make it with imp/exp or datapump from a no rows full export .
 Can you let impdp with CONTENT=METADATA_ONLY recreate empty and missing table for you from a dump created with content=metadata_only (you can not use exp with rows=no for deferred_segment_creation ) bye
Pier Paolo

2012/11/26 Norman Dunbar <oracle_at_dunbar-it.co.uk>

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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 27 2012 - 00:11:20 CET

Original text of this message