[Oracle]oracle exp-11.2.0.1导出空表的临时解决方案

1、如果是新建表，可以在新建表之前，关闭oracle11g的延迟创建段功能

11:19:05 SYS@ boston> show parameter deferred
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
11:19:11 SYS@ boston> alter system set deferred_segment_creation=false;
System altered.
Elapsed: 00:00:00.06
11:19:20 SYS@ boston> show parameter deferred
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE

创建新表默认分配segment

11:20:32 TE@ boston> create table test4 (id int);
Table created.
Elapsed: 00:00:00.20
11:21:38 TE@ boston> select table_name,segment_created from user_tables where table_name='TEST4';
TABLE_NAME                     SEG
------------------------------ ---
TEST4                          YES

2、如果是在deferred_segment_creation为true的情况下创建的表，那么只能手动给表添加segment

11:22:12 TE@ boston> select table_name,segment_created from user_tables where segment_created='NO';
TABLE_NAME                     SEG
------------------------------ ---
TEST1                          NO
在sql查询语句中直接拼接上allocate extent语句
11:25:32 TE@ boston> select 'alter table '||table_name||' allocate extent;' from user_tables where segment_created='NO';
'ALTERTABLE'||TABLE_NAME||'ALLOCATEEXTENT;'
-----------------------------------------------------------
alter table TEST1 allocate extent;
Elapsed: 00:00:00.14

然后手动执行拼接后的sql，如果语句较多可以编辑到文档中

11:25:58 TE@ boston> alter table TEST1 allocate extent;
Table altered.
Elapsed: 00:00:00.10

执行完成之后，查看空表已经被分配了新的段

11:26:13 TE@ boston> select table_name,segment_created from user_tables;

TABLE_NAME                     SEG
------------------------------ ---
TEST1                          YES
TEST4                          YES