ORA-14223: Deferred segment creation is not supported for this table

While creating table, we got error as below.

SQL> create table test3(id number, name varchar2(200)) SEGMENT CREATION DEFERRED;
create table test3(id number, name varchar2(200)) SEGMENT CREATION DEFERRED
*
ERROR at line 1:
ORA-14223: Deferred segment creation is not supported for this table

Reason:You are trying to create table in SYSTEM tablespace

Eg:

SQL> sho parameter DEFERRED_SEGMENT_CREATION;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

deferred_segment_creation            boolean     TRUE

SQL> sho user;

USER is "SYS"

SQL> create table test1(id number, name varchar2(200)) SEGMENT CREATION DEFERRED;

create table test1(id number, name varchar2(200)) SEGMENT CREATION DEFERRED

*

ERROR at line 1:

ORA-14223: Deferred segment creation is not supported for this table

table create with deferred segment failed because it's being created in system tablespace. Now we will create as below.

SQL> create table test1(id number, name varchar2(200));

Table created.

SQL> select segment_created from dba_tables where table_name='TEST1';

SEG

---

YES

SQL> select count(*) from user_segments where segment_name='TEST1';

  COUNT(*)

----------

        1

Now table got created and also initial segment got allocated though deferred_segment_creation is set to TRUE(Default)

Now Let us try to create table table in another user who belongs USERS tablespace.

SQL> conn a/a

Connected.

SQL>  create table test2(id number, name varchar2(200));

Table created.

SQL> select count(*) from user_segments where segment_name='TEST2';

  COUNT(*)

----------

         0

SQL> select segment_created from user_tables where table_name='TEST2';

SEG

---

NO

SQL> select count(*) from user_extents where segment_name='TEST2';

  COUNT(*)

----------

         0

SQL>

So table got created with deferred segment.

Restrictions on Deferred Segment Creation:

You cannot defer segment creation for the following types of tables: index-organized tables, clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables, and tables owned by SYS, SYSTEM, PUBLIC, OUTLN, or XDB.

Deferred segment creation is supported on partitions and subpartitions beginning with Oracle Database 11g Release 2 (11.2.0.2).

Deferred segment creation is not supported for bitmap join indexes and domain indexes.

Deferred segment creation is not supported in dictionary-managed tablespaces.

Deferred segment creation is not supported in the SYSTEM tablespace.

Serializable transactions do not work with deferred segment creation. Trying to insert data into an empty table with no segment created causes an error.

;