Oracle导出工具不会创建某些表和序列 [英] Oracle export tool does not create some tables and sequences

问题描述

我使用oracle 11，并使用exp/imp工具在数据库之间迁移数据.

I use oracle 11, and use exp/imp tools to migrate data between databases.

如果，所有空表和序列均已在目标数据库中创建，则效果很好.

It works very fine IF all empty tables and sequences are already created in target database.

但是，如果目标数据库中不存在表，则会发生一些不良情况；

But If tables dont exists in target DB than a few bad things happen;

• 它仍然会创建表，但只能创建带有数据的表，我找不到一种方法来强制它在目标数据库中创建空表.
• 它不会创建序列.

这是我输入我的值以导出工具的方式；

This is how I enter my values to export tool;

Users or Tables -> Tables
Export table data -> yes
Compress -> yes
Table or Partition to be exported -> I enter table names here one by one,

但是它确实接受不带数据的表名.它说表不存在，因此不要奇怪，以后也不会导入它们.

But it does accept table names without data..It says table does not exist, so no surprize they are not imported later.

Import Data only > no

Import File > Full path to Dump file.

List contents of import file > no

Ignore create error > no

import grants > yes

import table data > yes

import entire export > yes

推荐答案

序列不会以表格模式导出. 文档列出了每种模式下导出的对象 ，这表明序列仅在用户和完整数据库模式下导出.

Sequences are not exported in table mode. The documentation lists the objects exported in each mode, and that shows that sequences are only exported in user and full database modes.

不建议使用11g导出，因为该文档还指出:

Export is deprecated in 11g, as the documentation also states:

从Oracle Database 11g开始，不支持

Original Export用于一般用途.在Oracle Database 11g中唯一支持使用原始Export的方法是将XMLType数据向后迁移到Oracle Database 10g第2版(10.2)或更早版本.因此，Oracle建议您使用新的数据泵导出和导入实用程序

Original Export is desupported for general use as of Oracle Database 11g. The only supported use of original Export in Oracle Database 11g is backward migration of XMLType data to Oracle Database 10g release 2 (10.2) or earlier. Therefore, Oracle recommends that you use the new Data Pump Export and Import utilities

如果您延迟了段的创建，则不会导出空表. 此AskTom文章指的是，并且在文档中也提到了 :

The empty tables are not being exported if you have deferred segment creation. This AskTom articles refers to it, and it's also mentioned in the documentation:

原始的导出实用程序不会导出已创建的任何表 创建了延迟的细分受众群，但尚未为该细分受众群创建 它.

The original Export utility does not export any table that was created with deferred segment creation and has not had a segment created for it.

您可以使用dbms_metadata.get_ddl()获取所有表的表创建语句，也可以仅获取空表，然后从中手动构建它们.或强制分配范围(如文档中所述)；或使用支持的最新数据泵导出和导入.根据先前的问题，只有在客户拒绝处理数据泵文件的情况下，才应使用exp/imp，而我对此真的没有很好的理由.

You can either use dbms_metadata.get_ddl() to get the table creation statements for all the tables, or just the empty ones, and build them manually from that; or force an extent to be allocated (as mentioned in the docs too); or use the supported and current data pump export and import. Based on previous questions you should only be using exp/imp if your customer refuses to handle data pump files, and I can't really think of a good justification for that.

这篇关于Oracle导出工具不会创建某些表和序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！