Oracle 11g的延迟段创建功能

第一个session:

[oracle@rhel63single ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 21:45:16 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size		    2253584 bytes
Variable Size		 1476398320 bytes
Database Buffers	   83886080 bytes
Redo Buffers		    7471104 bytes
Database mounted.
Database opened.
SQL> show parameter compatible

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
compatible			     string	 11.2.0.0.0
SQL> conn scott/aaaaaa
ERROR:
ORA-28002: the password will expire within 7 days

Connected.

SQL> show parameter defer

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation	     boolean	 TRUE     ----------->>>默认值
SQL>
SQL> 
SQL> create table f_regs(reg_id number,reg_name varchar2(200));

Table created.

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

  COUNT(*)
----------
	 0

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

 COUNT(*)
----------
	 0

SQL>  insert into f_regs values(1,'BRDSTN');---->>>>插入一条记录.

1 row created.

SQL> ----------此时还没有commit,切换到另外一个session中,见下:

第二个session:

SQL>  select count(*) from dba_segments where segment_name='F_REGS';

  COUNT(*)
----------
	 1

SQL>  select count(*) from dba_extents where segment_name='F_REGS';


  COUNT(*)
----------
	 1

SQL> SQL> 
SQL> 

结论:

延迟segment创建的意思是说:segment的空间分配是在插入记录的时候进行(注意不是commit的时候),

在之前的数据库版本中,创建一个对象之后,会马上分配段和相应的extents

特性总结:
1. Deferred Segment Creation is only available in the Enterprise Edition versions of the database. This feature cannot be used in a Standard Edition type database.
来源于: Error ORA-439 When Importing Tables Created With Enabled Deferred Segment Into Oracle 11g Standard Edition (Doc ID 1087325.1)

2. 数据库初始化参数compatible需要为11.2.0.0.0或者以上版本,Deferred Segment Creation才可以使用.


3.在11.2.0.1中, DEFERRED_SEGMENT_CREATION 仅限于非分区表和非分区索引. 
  DEFERRED_SEGMENT_CREATION 不支持分区索引,bitmap join索引,域索引.


4.iot和其他表(cluter,global temporary,session-specific temporary tables,internal tables, typed tables, AQ tables, external tables)
  不支持DEFERRED_SEGMENT_CREATION .
   SYS, SYSTEM, PUBLIC, OUTLN, and XDB下的table 也不支持DEFERRED_SEGMENT_CREATION.


5.DEFERRED_SEGMENT_CREATION 不支持字典管理的表空间.在该表空间内建立table时,segment是立即建立的.


6.在本里管理的表空间中,建立table 时使用了 deferred segment creation选项,此时,是没有segments被建立的.
在之后的一个时间内,你迁移这个表空间到字典管理模式下,此时,任何create segments的尝试都会失败.在这种情况xia,你必须drop table and recreate it.


注意:
在11.2.0.1版本中, deferred segment creation不支持分区表,在11.2.0.2及其更新的版本中,该限制被解除.


关于table上没有segments的几个注意点:

1.如果你使用create table as select 方式建立的table,若是源表没有rows,新table的segments creation会被deferred.
  如果源表有rows,那么新table的segments creation 不会被deferred

2.如果在segments被实体化(materialized)之前,你使用ALTER TABLE ... ALLOCATE EXTENT,那么segments会被实体化,并且extent被分配.
但是:ALLOCATE EXTENT子句用在table上任何索引上会返回一个错误.

3.During an EXCHANGE of a partition or subpartition with a non-partitioned table without a segment, 
  segments are materialized for the non-partitioned table automatically before proceeding with the EXCHANGE.

4.When you issue an ALTER TABLE ... MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement. 
 ---alter table move中的存储参数优先于create table中的存储参数.

5.In a DDL statement on the table or its LOB columns or indexes, any specification of DEALLOCATE UNUSED is silently ignored. 

6. ONLINE operations on indexes of a table without a segment will silently be disabled; that is, they will proceed OFFLINE. 

7.Parallel DML operations on tables with no segments are disabled. 

Additional Automatic Functionality:-额外的自动功能

Additional enhancements in the Oracle Database 11gR2 (unrelated to the deferred segment creation) are implemented to save space: all UNUSABLE indexes and index partitions are created without a segment. This functionality is completely transparent for you. It is enabled by default with the COMPATIBILITY initialization parameter set to 11.2.0.0.

• Creating an index without a segment 

CREATE INDEX test_i1 ON seg_test(c) UNUSABLE; 

• Removing any allocated space for an index 

ALTER INDEX test_i UNUSABLE; 

• Creating the segment for an index: 

ALTER INDEX test_i REBUILD; 

The following slide shows some SQL commands which you might find useful for this new functionality: 

• You can create an index without segment with the CREATE INDEX … UNUSABLE clause. 
• You can remove any allocated space with the ALTER INDEX … UNUSABLE clause. 
• And finally, you can create a segment for an index with the ALTER INDEX … REBUILD 

SQL> select * from user_segments ; 

no rows selected 

SQL> create table test_table(no number); 

Table created. 

SQL> insert into test_table values(10); 

1 row created. 

SQL> commit; 

Commit complete. 

SQL> select segment_name from user_segments; 

SEGMENT_NAME 
-------------------------------------------------------------------------------- 
TEST_TABLE 

SQL> create index test_index on test_table(no) unusable; 

Index created. 

SQL> select segment_name from user_segments; 

SEGMENT_NAME 
-------------------------------------------------------------------------------- 
TEST_TABLE 

SQL> alter index test_index rebuild; 

Index altered. 

SQL> select segment_name from user_segments; 

SEGMENT_NAME 
-------------------------------------------------------------------------------- 
TEST_TABLE 
TEST_INDEX 

SQL> alter index test_index unusable; 

Index altered. 

SQL> select segment_name from user_segments; 

SEGMENT_NAME 
-------------------------------------------------------------------------------- 
TEST_TABLE 

SQL>

如上摘自: 11.2 Database New Feature Deferred Segment Creation [Video] (Doc ID 887962.1)