Deferred Segment Creation feature not enabled (ORA-00439)

Question

I have .sql script file with DDL for more than 60 tables. I am trying to copy-paste the script into SQL Developer, connected to a database which is "Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production".

Sample DDL Script:

CREATE TABLE UserName."Table_Name" 
   (    "Col1" NUMBER(*,0), 
    "Col2" VARCHAR2(50 BYTE), 
    "Col3" VARCHAR2(50 BYTE)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

Error report -
SQL Error: ORA-00439: feature not enabled: Deferred Segment Creation
00439. 00000 -  "feature not enabled: %s"
*Cause:    The specified feature is not enabled.
*Action:   Do not attempt to use this feature.

If I remove SEGMENT CREATION DEFERRED in the DDL Script:

CREATE TABLE UserName."Table_Name" 
   (    "Col1" NUMBER(*,0), 
    "Col2" VARCHAR2(50 BYTE), 
    "Col3" VARCHAR2(50 BYTE)
   ) 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

This works. But I can't manually remove that in each and every table script.

If I have .dmp dump file then the following syntax will also solve the issue; on the source instance:

EXPDP user/pwd dumpfile=somename.dmp directory=DATA_DUMP_DIR nologfile=Y version=10.2

and on the target instance

IMPDP user/pwd dumpfile=somename.dmp directory=DATA_DUMP_DIR nologfile=Y version=10.2

But I don't have a .dmp file, I only have a .sql file.

Which is the best way of doing this?

Answer 1

The deferred segment creation option is not available in Oracle 11g Express Edition (XE), which is what you are using. It's only available in Enterprise Edition (EE).

If you don't want to do an export/import and can only use the supplied script file you already have, your only option is to find and remove all instances of the SEGMENT CREATION DEFERRED clause.

Any text edit can do that of course, and SQL Developer has its own find/replace in the SQL Worksheet window.

Answer 2

Just use SEGMENT CREATION IMMEDIATE in place of SEGMENT CREATION DEFERRED