【parameter】deferred_segment_creation 参数说明与测试

11gr2中的新特性，延迟段创建，在建立新表未插入数据时，将延迟分配段空间。只有在插入第一条数据时才分配段空间，
该参数可用alter system 或 alter session 进行修改。 

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter def

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> 


SQL> conn scott/tiger
Connected.

SQL> create table t_deferred (x int,y int,z int);

Table created.

SQL> select  table_name,segment_created from dba_tables where table_name='T_DEFERRED' and owner='SCOTT';

TABLE_NAME                     SEG
------------------------------ ---
T_DEFERRED                     NO


SQL> select dbms_metadata.get_ddl('TABLE','T_DEFERRED','SCOTT') from dual;


DBMS_METADATA.GET_DDL('TABLE','T_DEFERRED','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T_DEFERRED"
   (    "X" NUMBER(*,0),
        "Y" NUMBER(*,0),
        "Z" NUMBER(*,0)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE “USERSS"


SQL> insert into t_deferred values (1,1,1);

1 row created.

SQL> select dbms_metadata.get_ddl('TABLE','T_DEFERRED','SCOTT') from dual;


DBMS_METADATA.GET_DDL('TABLE','T_DEFERRED','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T_DEFERRED"
   (    "X" NUMBER(*,0),
        "Y" NUMBER(*,0),
        "Z" NUMBER(*,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE “USERSS"



可以通过 alter table  t_deferred  allocate extent；  或者   insert into  t_deferred  values (1,1,1);  来创建segment ，看下面例子

例1:

SQL> drop table t_deferred;

Table dropped.

SQL> create table t_deferred (x int,y int,z int);

Table created.

SQL> select table_name,segment_created from user_tables where table_name='T_DEFERRED';

TABLE_NAME                     SEG
------------------------------ ---
T_DEFERRED                     NO


SQL> alter table t_deferred allocate extent (size 1m);

Table altered.

SQL> select table_name,segment_created from user_tables where table_name='T_DEFERRED';

TABLE_NAME                     SEG
------------------------------ ---
T_DEFERRED                     YES


＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

例2:

SQL> drop table t_deferred;

Table dropped.

SQL> create table t_deferred (x int,y int,z int);

Table created.

SQL> select table_name,segment_created from user_tables where table_name='T_DEFERRED';

TABLE_NAME                     SEG
------------------------------ ---
T_DEFERRED                     NO

SQL> insert into t_deferred values (1,1,1);

1 row created.

SQL> select table_name,segment_created from user_tables where table_name='T_DEFERRED';

TABLE_NAME                     SEG
------------------------------ ---
T_DEFERRED                     YES


＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

exp 导出空表失败，无法导入的问题

[oracle@test ~]$ exp scott/tiger file=t_deferred.dmp tables=t_deferred

Export: Release 11.2.0.1.0 - Production on Tue Dec 9 16:06:15 2014

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
EXP-00011: SCOTT.T_DEFERRED does not exist
Export terminated successfully with warnings.


SQL> alter table t_deferred rename to t_d;

Table altered.

SQL> select table_name,segment_created from user_tables where table_name='T_DEFERRED';

no rows selected


[oracle@test ~]$ imp scott/tiger file=t_deferred.dmp full=y

Import: Release 11.2.0.1.0 - Production on Tue Dec 9 16:18:01 2014

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
Import terminated successfully without warnings.

SQL> select table_name,segment_created from user_tables where table_name='T_DEFERRED';

no rows selected

结论：根据以上测试，在表未分配segment时，使用exp导出表时会提示EXP-00011: SCOTT.T_DEFERRED does not exist，所以无法将空表导出并导入。将表段分配后，可以正常exp/imp对空表操作。

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

转载于:http://blog.itpub.net/26148431/viewspace-1362986/