关于11g导不出空表的问题

最新推荐文章于 2021-11-16 17:09:43 发布

写给初学者的几封信

最新推荐文章于 2021-11-16 17:09:43 发布

阅读量676

点赞数

CC 4.0 BY-SA版权

分类专栏： 11g导不出空表的问题文章标签： oracle 11g

本文链接：https://blog.youkuaiyun.com/HackAge4619/article/details/73443950

11g导不出空表的问题专栏收录该内容

1 篇文章

订阅专栏

站在巨人的肩膀上，让文章更加完善！

ORACLE 11g用exp命令导出库文件备份时，发现只能导出来一部分表而且不提示错误，之前找不到解决方案只能把没导出来的表重新建建立。后来发现是所有的空表都没有导出来。于是想好好查查,因为在11g以前的版本中没有这样的问题。

查资料发现Oracle 11g中有个新特性：新增了一个参数“deferred_segment_creation”含义是段延迟创建，默认是true。

设置deferred_segment_creation参数：

设置deferred_segment_creation 参数为FALSE来禁用"段推迟创建"(也就是直接创建segment)，无论是空表还是非空表，都分配segment。

具体是什么意思呢？

如果这个参数设置为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、最原始最笨的办法(不推荐):insert一行，再rollback或者删除就产生segment了。

该方法是在在空表中插入数据，再删除，则产生segment。导出时则可导出空表。

2、设置deferred_segment_creation参数：

设置deferred_segment_creation参数为FALSE来禁用"段推迟创建"(也就是直接创建segment)，无论是空表还是非空表，都分配segment。

在sqlplus中，执行如下命令：

SQL>alter system set deferred_segment_creation=false;

查看：

SQL>show parameter deferred_segment_creation;

注意：该值设置后只对后面新增的表产生作用，对之前建立的空表(已经存在的)不起作用，仍不能导出。

并且要重新启动数据库，让参数生效。

我们在建表和导表之前先将oracle11g的配置作如下修改;

alter system set deferred_segment_creation=false;

3、使用ALLOCATE EXTENT，可以导出之前已经存在的空表。

使用ALLOCATE EXTENT可以为数据库对象的每一张表分配Extent(注意针对每一张表，就是说一张表需要一条SQL代码)：

其语法如下：

-----------

ALLOCATE EXTENT { SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer }

-----------

可以针对数据表、索引、物化视图等手工分配Extent。

ALLOCATE EXTENT使用样例:

ALLOCATE EXTENT

ALLOCATE EXTENT(SIZE integer [K | M])

ALLOCATE EXTENT(DATAFILE 'filename')

ALLOCATE EXTENT(INSTANCE integer) www.2cto.com

ALLOCATE EXTENT(SIZE integer [K | M] DATAFILE 'filename')

ALLOCATE EXTENT(SIZE integer [K | M] INSTANCE integer)

针对数据表操作的完整语法如下：

-----------

ALTERT ABLE [schema.] table_name ALLOCATE EXTENT [({ SIZE integer [K | M] | DATAFILE'filename' | INSTANCE integer})]

-----------

故，需要构建如下样子简单的SQL命令：

-----------

alter table TableName allocate extent

-----------

但要是每一张表写一条语句的话太过麻烦，为了方便我们使用SQL命令拼写出每一张表的alter语句。

构建对空表分配空间的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:\sql_script.sql文件。内容如下：

set heading off;

set echo off;

set feedback off;

set termout on;

spool E:\sql_allocate.sql;

Select'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;

spooloff;

这个脚本的作用就是创建一个E:\sql_allocate.sql脚本文件，将Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0or num_rows is null的执行结果（就是给每张表生成segment的SQL代码）批量输出，存储到一个E:\sql_allocate.sql的脚本文件中。

2.执行E:\sql_script.sql文件来生成“分配表空间的SQL代码”的脚本文件sql_allocate.sql。

命令如下：

SQL>@E:\sql_script.sql; (也可写一个批处理文件，命令如下：sqlplus用户名/密码@数据库 @E:\sql_script.sql,这时你打开你的这个文件看里面是不是有内容,如果没有也没关系,就直接讲以上SQL>@E:\sql_script.sql执行后产生的sql语句直接拿到PLSQL的sql窗口中执行即可,这样即可忽略此处到步骤3结束了!)