segment createion deferred

问题来源于一次导出

C:UsersAdministrator>exp acms/acms tables=acms_light_time file=aaa.dmp

Export: Release 11.2.0.1.0 - Production on 星期五 5月 28 13:44:06 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
EXP-00011: ACMS.ACMS_LIGHT_TIME 不存在
导出成功终止, 但出现警告。

C:UsersAdministrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 5月 28 13:44:27 2010

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

SQL> conn acms/acms
已连接。
SQL> select table_name from user_tables where table_name='ACMS_LIGHT_TIME';

TABLE_NAME
------------------------------
ACMS_LIGHT_TIME

SQL> show parameter deferred

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE

表存在，但无法通过DMP导出。这在11g之前是不存在的。

[@more@]

deferred_segment_creation

Use this clause to determine when the database should create the segment(s) for this table:

SEGMENT CREATION DEFERRED: This clause defers creation of the table segment — as well as segments for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table — until the first row of data is inserted into the table. At that time, the segments for the table, LOB columns and indexes, and explicitly created indexes are all materialized and inherit any storage properties specified in this CREATE TABLE statement or, in the case of explicitly created indexes, the CREATE INDEX statement. These segments are created regardless whether the initial insert operation is uncommitted or rolled back. This is the default value.

Caution:

When creating many tables with deferred segment creation, ensure that you allocate enough space for your database so that when the first rows are inserted, there is enough space for all the new segments.
SEGMENT CREATION IMMEDIATE: The table segment is created as part of this CREATE TABLE statement.

Immediate segment creation is useful, for example, if your application depends upon the object appearing in the DBA_, USER_, and ALL_SEGMENTS data dictionary views, because the object will not appear in those views until the segment is created. This clause overrides the setting of the DEFERRED_SEGMENT_CREATION initialization parameter.

To determine whether a segment has been created for an existing table or its LOB columns or indexes, query the SEGMENT_CREATED column of USER_TABLES, USER_INDEXES, or USER_LOBS.

Notes on Tables Without Segments The following rules apply to a table whose segment has not yet been materialized:

If you create this table with CREATE TABLE ... AS subquery, then if the source table has no rows, segment creation of the new table is deferred. If the source table has rows, then segment creation of the new table is not deferred.

If you specify ALTER TABLE ... ALLOCATE EXTENT before the segment is materialized, then the segment is materialized and then an extent is allocated. However the ALLOCATE EXTENT clause in a DDL statement on any indexes of the table will return an error.

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.

When you issue an ALTER TABLE ... MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement.

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

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

Parallel DML operations on tables with no segments are disabled.

Restrictions on Deferred Segment Creation This clause is subject to the following restrictions:

You cannot defer segment creation for the following types of tables: partitioned 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 not supported for partitioned indexes, bitmap join indexes, and domain indexes.

Deferred segment creation is not supported for objects on dictionary-managed tablespaces.

See Also:

Oracle Database Concepts for general information on segment allocation and Oracle Database Reference for more information about the DEFERRED_SEGMENT_CREATION initialization parameter
查看文档才知道，这是11G的一个新特性，默认情况下，在11G中建立一个空表后，11G是不会马上产生对应的SEGMENT。而是当插入记录后才会产生。
SQL> select segment_name from dba_segments where segment_name='ACMS_LIGHT_TIME';

未选定行
SQL> insert into acms_light_time values ('PEK','23:00','06:00',to_date('2010-01-
01','YYYY-MM-DD'),null,73,sysdate,73,sysdate);
已创建 1 行。
SQL> select segment_name from dba_segments where segment_name='ACMS_LIGHT_TIME';

SEGMENT_NAME
--------------------------------------------------------------------------------
ACMS_LIGHT_TIME
当然，也可以修改初始化参数，或在建表时指定segment creation immediate
SQL> create table t segment creation immediate as select * from acms_light_time
where 1=0;
表已创建。
SQL> select * from t;
未选定行
SQL> select segment_name from dba_segments where segment_name='T';
SEGMENT_NAME
--------------------------------------------------------------------------------
T

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19423/viewspace-1033992/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19423/viewspace-1033992/