Thursday, July 19, 2012

ORA-00439: feature not enabled: Deferred Segment Creation on datapump import

Short version:
If you get this error on a data pump import:
ORA-39083: Object type TABLE:"SCOTT"."EMP" failed to create with error:
ORA-00439: feature not enabled: Deferred Segment Creation

then add "version=10.2" or "version=11.1" depending on your version of database. This will make IMPDP ignore the "Deferred Segment Creation" feature in 11.2 that causes this issue.

IMPDP system/password DIRECTORY=data_pump_dir DUMPFILE=emp.dmp schemas=emp version=10.2


Long version:
In Oracle version 11.2 there was a new feature called "Deferred Segment Creation". It is a space saver for tables that has no rows since the segments for the table will not be created until the tables actually has rows in it. You can create tables with storage parameters so it will use this feature.
CREATE TABLE emp 
            (emp_id NUMBER, 
             name VARCHAR2(4000)) 
SEGMENT CREATION DEFERRED;


The default way to handle segment creation looks like this:
CREATE TABLE emp 
            (emp_id NUMBER, 
             name VARCHAR2(4000)) 
SEGMENT CREATION IMMEDIATE;  


Default, after 11.2, this feature is turned on with the parameter DEFERRED_SEGMENT_CREATION set too TRUE.

This can cause problems with datapump and might cause this error on import:
ORA-39083: Object type TABLE:"SCOTT"."EMP" failed to create with error:
ORA-00439: feature not enabled: Deferred Segment Creation

This is because the database that is imported to does not have this feature, say like Oracle 10.2 or 11.1. Exactly what the error says.

This is why I was baffled when I got this error whn I exported data from a 11.2.0.1 database into another 11.2.0.1 database. Both databases had DEFERRED_SEGMENT_CREATION set to TRUE. So both should have that feature? Right?
Apparently not, since I got the error.
The only difference was that source database was Enterprise Edition and target database was Standard Edition. This was the cause of the it all even though I can't find any information about this feature being EE only. But it probably is.

Solution:
The solution, or work around, for this it is to force the tables that uses this feature to be created, with IMPDP, without this feature. This is done by adding "version=10.2" or a version lower than 11.2 to your IMP command. In my case it was enough to add "version=11.1" to my import command line:

IMPDP system/password DIRECTORY=data_pump_dir DUMPFILE=emp.dmp schemas=emp version=11.1

This will "fix" this in the sense that the import will work but it will change the property of the table. But if you are importing to a database without this feature you don't really have any choice in the matter.

24 comments:

  1. you saved me like DAYS of work...
    and like 98 errors

    thank you so much

    ReplyDelete
  2. i bow to you, sir. thank you very much.

    ReplyDelete
  3. Thanks, this post saved me some time.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. You saved me also ! thanks

    ReplyDelete
  6. Yup thank you very much

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. You said, "This was the cause of the it all even though I can't find any information about this feature being EE only. But it probably is."

    Here is the proof that you are right.

    http://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC116

    ReplyDelete
  9. Thank you, very much.

    ReplyDelete
  10. Thank you......This really made my life easier today while being under a lot of pressure. You probably saved my deadline...

    ReplyDelete
  11. Many thanks for this very useful post, you saved me days of work too ...:-)

    ReplyDelete
  12. Thank you so much for your advise (version=11.1)!

    This advice has helped me to fix the problem
    and restore my dump file.

    ReplyDelete
  13. Somewhere in Kenya :) Thank you very much!!!!!

    ReplyDelete
  14. Here is a reference to the documentation that specify the features not included in XE:

    https://docs.oracle.com/cd/E17781_01/license.112/e18068/toc.htm#XELIC116

    ReplyDelete
  15. Gracias un gran aporte para la solución, en mi caso tuve que alterar el export tambien y adicionar version =10.2

    ReplyDelete