模拟exp导出时表丢失

在 Oracle11.2.0.1版本下，exp/imp以用户模式导出和导入的时候，如果表中没有数据，是一个空表，由于oracle的段的延时分配，导致导出的时候不包含空表。

[oracle@oracle1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 9 09:18:47 2019

Copyright © 1982, 2009, Oracle. All rights reserved.

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

SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID

---

DEPT TABLE
EMP TABLE
SALGRADE TABLE

SQL> col SEGMENT_NAME for a20
SQL> select OWNER,SEGMENT_NAME,BYTES from dba_segments where owner=‘SCOTT’;

OWNER SEGMENT_NAME BYTES

---

SCOTT DEPT 65536
SCOTT EMP 65536
SCOTT SALGRADE 65536
SCOTT PK_DEPT 65536
SCOTT PK_EMP 65536

----创建一张空表T
SQL> create table t(id number,info varchar2(20));

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

---

DEPT TABLE
EMP TABLE
SALGRADE TABLE
T TABLE

SQL> select OWNER,SEGMENT_NAME,BYTES from dba_segments where owner=‘SCOTT’;

OWNER SEGMENT_NAME BYTES

---

SCOTT DEPT 65536
SCOTT EMP 65536
SCOTT SALGRADE 65536
SCOTT PK_DEPT 65536
SCOTT PK_EMP 65536

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle1 ~]$

[oracle@oracle1 ~]$ cat exp.par

userid=scott/tiger
owner=scott
file=/home/oracle/expscott.dmp
buffer=100000
feedback=5000
log=/home/oracle/expscott.log

[oracle@oracle1 ~]$ exp parfile=exp.par

Export: Release 11.2.0.1.0 - Production on Thu May 9 09:22:08 2019

Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 ZHS16GBK character set (possible charset conversion)

About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT’s tables via Conventional Path …
. . exporting table DEPT
4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP
14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE
5 rows exported
EXP-00091: Exporting questionable statistics.
. 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 with warnings.
[oracle@oracle1 ~]$
-----导出的时候，没有包含新建的空表T

[oracle@oracle1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 9 09:22:44 2019

Copyright © 1982, 2009, Oracle. All rights reserved.

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

SQL> drop user scott cascade;

User dropped.

SQL> create user scott identified by tiger;

User created.

SQL> grant dba to scott;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle1 ~]$

[oracle@oracle1 ~]$ cat imp.par

userid=scott/tiger
full=y
file=/home/oracle/expscott.dmp
buffer=100000
feedback=5000
log=/home/oracle/impscott.log
[oracle@oracle1 ~]$

[oracle@oracle1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 9 09:22:44 2019

Copyright © 1982, 2009, Oracle. All rights reserved.

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

SQL> drop user scott cascade;

User dropped.

SQL> create user scott identified by tiger;

User created.

SQL> grant dba to scott;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle1 ~]$
[oracle@oracle1 ~]$ imp parfile=imp.par

Import: Release 11.2.0.1.0 - Production on Thu May 9 09:24:14 2019

Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 ZHS16GBK character set (possible charset conversion)
. importing SCOTT’s objects into SCOTT
. . importing table “DEPT”
4 rows imported
. . importing table “EMP”
14 rows imported
. . importing table “SALGRADE”
5 rows imported
About to enable constraints…
Import terminated successfully without warnings.
[oracle@oracle1 ~]$
-----导出的时候，没有包含新建的空表T

原因：
11gR2之前的版本中，当创建一张表时，会自动分配段空间，这样做有几个弊端：

1. 初始创建表时就需要分配空间，自然会占用一些时间，如果初始化多张表，这种影响就被放大。
2. 如果很多表开始的一段时间都不需要，那么就会浪费这些空间。

为此，从11gR2开始，有一种新特性，叫延迟段，即延迟分配段空间。简单讲，默认将表(以及索引、LOB)的物理空间分配推迟到第一条记录插入到表中时。即有实际的数据插入表中时，再为每个对象初始化空间分配。其中11.2.0.1不支持分区表 、bitmap join indexes和domain indexes。11.2.0.2版本开始支持分区表。
注：
1.使用特性的前提是COMPATIBLE参数是11.2.0及以上。

2.IOTs and other special tables like clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tablesare not supported. Tables owned by SYS,SYSTEM, PUBLIC, OUTLN, and XDB are also excluded.
SYS的表是不能使用延迟段的，因此创建时还是立即分配段空间。

3.禁用延迟段：
a）可以禁用延迟段，是否使用延迟段是由DEFERRED_SEGMENT_CREATION参数定义的，该参数可以在会话级别修改，如果想彻底删除延迟段，可以在spfile中修改，本次以及下次启动后就会一直生效了；
b）可以在建表之后通过alter table 表名 allocate extent;来马上手工分配；
c）可以使用SEGMENT CREATION在创建表时指定是否使用延迟段；
SQL> create table tbl_seg(
2 reg_id number,
3 reg_name varchar2(200))
4 segment creation immediate;
Table created.
------立即为段和区分配了空间。如果使用SEGMENT CREATION DEFERRED则会使用延迟段的功能。

-----验证
[oracle@oracle1 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 9 09:24:24 2019

Copyright © 1982, 2009, Oracle. All rights reserved.

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

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

---

DEPT TABLE
EMP TABLE
SALGRADE TABLE

SQL>

SQL> create table t(id number,info varchar2(20));

Table created.

SQL> insert into t values(1,‘a’);

1 row created.

SQL> col SEGMENT_NAME for a20
SQL> select OWNER,SEGMENT_NAME,BYTES from dba_segments where owner=‘SCOTT’;

OWNER SEGMENT_NAME BYTES

---

SCOTT DEPT 65536
SCOTT EMP 65536
SCOTT SALGRADE 65536
SCOTT T 65536
SCOTT PK_DEPT 65536
SCOTT PK_EMP 65536

6 rows selected.

SQL> truncate table t;

Table truncated.

SQL> select OWNER,SEGMENT_NAME,BYTES from dba_segments where owner=‘SCOTT’;

OWNER SEGMENT_NAME BYTES

---

SCOTT DEPT 65536
SCOTT EMP 65536
SCOTT SALGRADE 65536
SCOTT T 65536
SCOTT PK_DEPT 65536
SCOTT PK_EMP 65536

6 rows selected.

SQL>

[oracle@oracle1 ~]$ exp parfile=exp.par

Export: Release 11.2.0.1.0 - Production on Thu May 9 09:27:10 2019

Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 ZHS16GBK character set (possible charset conversion)

About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT’s tables via Conventional Path …
. . exporting table DEPT
4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP
14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE
5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T
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 with warnings.
[oracle@oracle1 ~]$