ORACLE 11g
用exp命令导出库文件备份时,发现只能导出来一部分表而且不提示错误,之前找不到解决方案只能把没导出来的表重新建建立。后来发现是所有的空表都没有导出来。于是想好好查查,因为在以前的10g版本中没有这样的问题。
查资料发现Oracle
11g中有个新特性:新增了一个参数“deferred_segment_creation”含义是段延迟创建,默认是true。
具体是什么意思呢?
如果这个参数设置为true,你新建了一个表Table1,并且没有向其中插入数据,那么这个表不会立即分配extent,也就是不占数据空间,即表也不分配
segment
以节省空间,所以这些表也没能导出来。在系统表user_tables中也可以看到segment_treated的字段里是“NO”或者“YES”说明了某张表是否分配了segment。说白了是为了可以节省少量的空间。
用下面的SQL语句查询,可以发现没有导出的表其 segment_created 字段值都是
'NO'。
Select segment_created,table_name from user_tables where
segment_created = 'NO';
解决方法:
1、设置deferred_segment_creation 参数:
设置deferred_segment_creation
参数为FALSE来禁用"段推迟创建"(也就是直接创建segment),无论是空表还是非空表,都分配segment。
在sqlplus中,执行如下命令:
SQL>alter system set
deferred_segment_creation=false;
查看:
SQL>show parameter
deferred_segment_creation;
注意:该值设置后只对后面新增的表产生作用,对之前建立的空表(已经存在的)不起作用,仍不能导出。
并且要重新启动数据库,让参数生效。
2、使用ALLOCATE EXTENT,可以导出之前已经存在的空表。
构建对空表分配空间的SQL命令。
查询当前用户下的所有空表(一个用户最好对应一个默认表空间)。命令如下:
SQL>select table_name from user_tables where
NUM_ROWS=0;
根据上述查询,可以构建针对空表分配空间的命令语句,如下:
SQL>Select 'alter table '||table_name||' allocate
extent;' from user_tables where
num_rows=0 or num_rows is
null(注意:很多教程没有这里,这里是有可能位空的)
上述代码可产生批量的修改表extent的SQL语句(有多少张空表就产生多少条),我们只需要将其生成的所有sql代码全部执行,就可以给每一张已经存在的表来分配segment,就OK了。
最后:这时再用exp导出就没有问题了。但是:数据库本身的deferred_segment_creation属性还是TRUE,也是就是说如果再创建新表的话,默认还是不分配segment的。所以还是需要更改deferred_segment_creation的参数,以便以后创建的新表自动分配segment。
总结:
如果你的数据库还没有创建任何数据表,那么直接修改deferred_segment_creation属性,以后创建的表无论是不是为空都会自动分配segment,就不会出现导不出空表的情况。然而如果你的数据库中已经有很多空表,并且需要导出来,那么光修改deferred_segment_creation属性则没有用的,因为它只对之后创建的表有作用。你需要给已存在的空表分配segment以便可以导出存在的空表,就用到上面讲的allocate
extent方法,但此方法只针对已经存在的表的segment属性,所以最好就是:先给已存在的空表分配segment,方便其可以直接导出,然后设定deferred_segment_creation参数以便以后每张表无论是否为空都自动分配segment。
附录:有关第二种方法给已经存在的空表分配segment,下面介绍一种生成脚本来执行sql的方法。
SQL>Select 'alter table '||table_name||'
allocate extent;' from user_tables where
num_rows=0 or num_rows is
null;
批量输出上述生成的SQL语句并写入到一个.sql的脚本文件中。
如:
1. 创建执行脚本文件:我创建一个E:\12.sql文件。内容如下:
set heading
off;
set echo
off;
set feedback
off;
set termout
on;
spool
E:\1.sql;
Select 'alter table
'||table_name||' allocate extent;' from user_tables where
num_rows=0 or num_rows is
null;
spool off;
这个脚本的作用就是创建一个E:\12.sql脚本文件,将Select 'alter table '||table_name||'
allocate extent;' from user_tables where num_rows=0or
num_rows is
null的执行结果(就是给每张表生成segment的SQL代码)批量输出,存储到一个E:\1.sql的脚本文件中。
2. 执行E:\12.sql文件来生成“分配表空间的SQL代码”的脚本文件1.sql。
命令如下:
SQL>@
E:\12.sql; (也可写一个批处理文件,命令如下:sqlplus 用户名/密码@数据库
@E:\12.sql)
执行完毕后,得到E:\1.sql脚本文件(里面是给所有空表分配segment的SQL代码)。
打开该文件会看到,已经得到对所有空表分配空间的SQL语句。
3. 执行E:\1.sql文件来对表分配空间。
命令如下:SQL>@ E:\1.sql
执行完毕,表已更改。之前存在的空表已分配segment空间!
大功告成,此时执行exp命令,即可把包括空表在内的所有表,正常导出。