oracle 11g deferred_segment_created

11g 부터 table 생성시 data가 들어오기 전까지 segment를 만들지 않게 설정 할 수 있습니다.
SYS, SYSTEM, PUBLIC, OUTLN, XDB schema는 제외 됩니다.

SQL> ALTER SYSTEM SET deferred_segment_creation=TRUE;     -< default 로 설정되 어있음
SQL> conn hyun/hyun

SQL> create table deferred_table11(id number(8) CONSTRAINT id_pk_11 primary key);
 
Table created.
 
SQL> select segment_name, segment_type
  2  from user_segments
  3  where segment_name in ('DEFERRED_TABLE11','ID_PK_11'); 
 
no rows selected

SQL> select segment_name, extent_id, bytes
  2  from user_extents
  3  where segment_name in ('DEFERRED_TABLE11','ID_PK_11');
 no rows selected

-- user_tables view를 통해 segment가 만들어 지지 않았음을 확인 할 수 있습니다.

SQL> select table_name, segment_created from user_tables
  2  where table_name='DEFERRED_TABLE11';
 
TABLE_NAME                        SEG
------------------------------      ---
DEFERRED_TABLE11               NO
 
SQL> select index_name, segment_created from user_indexes
   2  where index_name='ID_PK_11';
 
INDEX_NAME                     SEG
------------------------------ ---
ID_PK_11                           NO

 

SQL> alter system set deferred_segment_creation=false;
 
System altered.

SQL> create table deferred_table11(id number(8) CONSTRAINT id_pk_11 primary key);
 
Table created.
 
SQL> select segment_name, segment_type
  2  from user_segments
  3  where segment_name in ('DEFERRED_TABLE11','ID_PK_11');

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
DEFERRED_TABLE11     TABLE
ID_PK_11                     INDEX

SQL> select segment_name, extent_id, bytes
  2  from user_extents
  3  where segment_name in ('DEFERRED_TABLE11','ID_PK_11');
 
SEGMENT_NAME           EXTENT_ID      BYTES
--------------------          ----------     ----------
DEFERRED_TABLE11            0             65536
ID_PK_11                            0             65536

 SQL>  select table_name, segment_created from user_tables
  2  where table_name='DEFERRED_TABLE11';
 
TABLE_NAME                         SEG
------------------------------      ---
DEFERRED_TABLE11               YES

SQL> select index_name, segment_created from user_indexes
  2  where index_name='ID_PK_11';
 
INDEX_NAME                      SEG
------------------------------   ---
ID_PK_11                           YES


        create table ......   segment creation immediate
또는   create table .....   segment creation deferred
옵션을 주어 segment를 생성 하거나 생성하지 않을 수 있습니다.

sys schema 의 경우 강제적으로 segment creation deferred 옵션을 줄 경우 다음과 같은 error를 보입니다.

create table deferred_table11(id number(8) CONSTRAINT id_pk_11 primary key)
segment creation deferred;

ERROR at line 1:
ORA-14223: Deferred segment creation is not supported for this table

create table deferred_table11(id number(8) CONSTRAINT id_pk_11 primary key)
segment creation immediate