Transportable Tablespaces and Deferred segment creation "bug"

  • From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 26 Nov 2012 10:54:59 +0000

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
--
//www.freelists.org/webpage/oracle-l


Other related posts: