oracle deferred_segment_creation 段延迟分配的一个隐患

背景
 oracle 段延迟分配是oracle 11g的一个新的特性，当你创建表的时候，数据库不分配空间，只有你插入记录的时候才真正分配空间，我觉得这个特性就和net app存储的存储分配特性很相似，net app存储就是划分空间的时候可以划分很大，但是，如果没有使用是不分配空间的
oracle 出发点是好的，这样可以占用更少的空间，当使用的时候才分配，但是，一个新的比较严重的问题引发了。
现象
 客户进行异构平台数据迁移，由于数据量很小，客户没有使用expdp，而是使用了exp，当迁移完数据后，应用无法正常使用，通过排查，迁移过来的数据少了一些表。到oracle support知识库，发现oracle 11g增加了一个新特性段延迟分配，引起了次问题


分析过程


  1.查看oracle的参数


 SQL> show parameters segment


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
deferred_segment_creation            boolean     TRUE
rollback_segments                    string
transactions_per_rollback_segment    integer     5
上边的deferred_segment_creation设置默认为true，就是说明段延迟分配


 2. 创建一个表


SQL> create table test(id number);


Table created.




SQL> select segment_name from user_segments where segment_type='TABLE';




QYL4
QYL
QYL1
QYL2
查看不到test表
SQL> select table_name from user_tables;


TABLE_NAME
------------------------------
QYL
QYL1
QYL2
QYL4
TEST
查看有test表
3 .exp导出数据


C:\Documents and Settings\oracle>exp test/test file=f:\test.dmp


Export: Release 11.2.0.1.0 - Production on Thu Mar 31 08:44:09 2016


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 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set


About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table                            QYL          1 rows exported
. . exporting table                           QYL1          1 rows exported
. . exporting table                           QYL2          0 rows exported
. . exporting table                           QYL4          3 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.


大家看到导出没有test表，这样我们导出就丢失了没有数据的表


4.解决办法


  1，更改参数，但是更改参数只是影响后边创建的表，原来的表还是需要重新创建一下


    sql>alter system set deferred_segment_creation =false scope=both;


    创建表
    sql>create table test(id number);
    查看段和表
    SQL> select segment_name from user_segments where segment_type='TABLE';
 
QYL4
QYL
QYL1
QYL2
TEST
SQL> select table_name from user_tables;


TABLE_NAME
------------------------------
QYL
QYL1
QYL2
QYL4
TEST 
结果显示一样了


导出数据
 
C:\Documents and Settings\oracle>exp test/test file=f:\test.dmp


Export: Release 11.2.0.1.0 - Production on Thu Mar 31 09:02:23 2016


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 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set


About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table                            QYL          1 rows exported
. . exporting table                           QYL1          1 rows exported
. . exporting table                           QYL2          0 rows exported
. . exporting table                           QYL4          3 rows exported
. . exporting table                           TEST          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
  上边导出已经包含了test表


  2，使用expdp导出


     过程如下


SQL> alter system set deferred_segment_creation=true;


System altered.


SQL> connect test/test
Connected.


SQL> create table test(id number);


Table created.


SQL> select segment_name from user_segments where segment_type='TABLE';


SEGMENT_NAME
--------------------------------------------------------------------------------


QYL4
QYL
QYL1
QYL2


SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options


查看没有test表
 expdp导出数据


C:\Documents and Settings\oracle>expdp system/sys dumpfile=test%U.dmp schemas=te
st directory=imp_data


Export: Release 11.2.0.1.0 - Production on Thu Mar 31 09:08:44 2016


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 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  system/******** dumpfile=test%U.dmp s
chemas=test directory=imp_data
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMA
P/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."QYL"                                5.007 KB       1 rows
. . exported "TEST"."QYL1"                               5.007 KB       1 rows
. . exported "TEST"."QYL4"                               5.820 KB       3 rows
. . exported "TEST"."QYL2"                                   0 KB       0 rows
. . exported "TEST"."TEST"                                   0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
  F:\TEST01.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 09:09:34


上边的test表导了出来




3，往表中插入一条记录


sql>insert into test values(1);
  commit;
 查看表和段


SQL> insert into test values(1);


1 row created.


SQL> commit;


Commit complete.


SQL> select segment_name from user_segments where segment_type='TABLE';


SEGMENT_NAME
--------------------------------------------------------------------------------


QYL4
QYL
QYL1
QYL2
TEST


5 rows selected.


SQL> select table_name from user_tables;


TABLE_NAME
------------------------------
QYL
QYL1
QYL2
QYL4
TEST
两个结果一样