Oracle Database 11g 使用 exp 命令导出 DMP 文件不完整

发表于 Disqus：

在使用 Oracle 数据库时，发现导出的 DMP 文件，数据库结构不完整。缺少多个表。

前言

由于工作原因，使用了 Oracle 数据库，在用 exp 命令导出一个用户下的所有对象后，再做 imp 导入，发现表少了很多。在网上查了一下资料，发现是因为 11g 版本的新特性，当表中无数据时，不分配 segment(段：数据库对象使用的空间)，以节省空间。在使用 exp 命令导出时，没有 segment 的表不会被导出。

解决方法

从网上找到的资料来看，有三种解决方式。

  1. 使用 insert 语句插入一行数据，再使用 rollback 或 delete，清掉数据。此时就已经分配了 segment，即可正常导出。

  2. 使用 expdp 和 impdp 命令，在 Oracle Database 10g 及以后的版本中，都包含了这两个工具。（这个方法略有些麻烦。就不展开说明了）

  3. 使用 alter 语句对现有空表进行 segment 的分配

    • 第一句是查询当前用户下所有的空表表名。
    • 第二句是将查询出来的表名组成一条 alter 语句。
    • 将组合出来的语句，依次执行即可导出空表。

    可以使用数据库工具将组合出来的语句的结果保存为.sql之类的可以编辑的文件，然后使用编辑器，批量替换或删除多余语句。复制到 SQL 语句窗口或数据库命令行窗口执行即可。语句例如：alter table users allocate extent;

1
2
SELECT table_name FROM USER_TABLES WHERE NUM_ROWS=0;
SELECT 'alter table '||table_name||' allocate extent;' FROM USER_TABLES WHERE NUM_ROWS=0;

禁用 Oracle Database 11g 的空表不分配 segment 的功能，执行以下语句：

该功能禁用后，仅对禁用之后新建的表，或导入的表有用。

1
ALTER system SET deferred_segment_creation = false;

查看当前状态

1
show parameter deferred_segment_creation

参考资料