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.
you saved me like DAYS of work...
ReplyDeleteand like 98 errors
thank you so much
i bow to you, sir. thank you very much.
ReplyDeleteThank you
ReplyDeleteThanks, this post saved me some time.
ReplyDeleteThank You!!!!!
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteyou save mylife
ReplyDeleteThanks a lot
ReplyDeleteYou saved me also ! thanks
ReplyDeleteYup thank you very much
ReplyDeleteThx! Worked! :-)
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteYou 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."
ReplyDeleteHere is the proof that you are right.
http://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC116
Thank you, very much.
ReplyDeleteThank you......This really made my life easier today while being under a lot of pressure. You probably saved my deadline...
ReplyDeleteGreat!!
ReplyDeleteMany thanks for this very useful post, you saved me days of work too ...:-)
ReplyDeleteThanks a lot
ReplyDeleteYou're a hero!
ReplyDeleteThank you so much for your advise (version=11.1)!
ReplyDeleteThis advice has helped me to fix the problem
and restore my dump file.
Muito obrigado!
ReplyDeleteSomewhere in Kenya :) Thank you very much!!!!!
ReplyDeleteHere is a reference to the documentation that specify the features not included in XE:
ReplyDeletehttps://docs.oracle.com/cd/E17781_01/license.112/e18068/toc.htm#XELIC116
Gracias un gran aporte para la solución, en mi caso tuve que alterar el export tambien y adicionar version =10.2
ReplyDelete