In order to downgrade an 188.8.131.52 Enterprise Edition database to Standard Edition, I had to use a Transportable Tablespace export/import. Because the default setting for
TRUE, the tablespace import barfed with numerous “IMP-00017: following statements failed with ORACLE error 1647:” errors. Want to know why?
The ORA-01649 error is “Tablespace is read only, cannnot allocate space in it” which is interesting as I’m imprting a Transportable Tablespace dump file and all the tablespaces are read only after being created, until I manually make then read write.
In the source database, the “broken” tables all have zero rows in them, and have no entry in DBA_SEGMENTS which means that the Deferred Segment Allocation feature has indeed done its stuff, and deferred allocating a segment until the first row of data is entered (but not necessarily committed!) into the table.
DEFERRED_SEGMENT_CREATIONis also defaulted to
TRUEin Standard Edition databases, but the parameter has no effect in these, as the following shows:Connected to: Oracle Database 11g Release 184.108.40.206.0 - 64bit Production SQL> show parameter deferred_seg NAME TYPE VALUE -------------------------- -------- ------------- deferred_segment_creation boolean TRUE SQL> create table test(a number); Table created. SQL> select table_name, segment_created 2 from user_tables 3 where table_name = 'TEST'; TABLE_NAME SEG -------------------- --- TEST YES
So, even with an empty table, in Standard Edition, the deferred segment allocation does not take place. This is exactly why I’m having problems running a transportable tablespace import from an Enterprise Edition database to a Standard Edition one. It seems that Standard expects everything to have at least one allocated segment.
If the importing database is Enterprise Edition, this problem doesn’t occur.
The quick workaround is to run a table based export, using
expdp, of the affected tables, and import that at the receiving database using
The longer term workaround is to make sure that
DEFERRED_SEGMENT_CREATION is set to
FALSE in the spfile.
In the meantime, I’m logging a bug with Oracle as the
DBMS_TTS.TRANSPORT_SET_CHECK procedure should identify these tables and warn about them, or, the import should correctly import them anyway.