将带有分区表的 dmp 导入 Oracle XE

Importing a dmp with partitioned tables into Oracle XE

提问by The Archetypal Paul

I've got a schema containing partitioned tables. I can use exp to create a .dmp, but when I import that into Oracle XE, I get errors because Oracle XE doesn't support partitioned tables.

我有一个包含分区表的架构。我可以使用 exp 创建 .dmp，但是当我将其导入 Oracle XE 时，我收到错误消息，因为 Oracle XE 不支持分区表。

How do I get the .dmp imported into Oracle XE? I think pre-creating the tables might do it, but how do I extract the DDL in some automated way to do this?

如何将 .dmp 导入 Oracle XE？我认为预先创建表可能会做到这一点，但我如何以某种自动方式提取 DDL 来做到这一点？

Or can I create the exp somehow without partitions?

或者我可以在没有分区的情况下以某种方式创建 exp 吗？

回答by Gary Myers

If you try the import, with ROWS=N, you'll get an error reporting "ORA-00439: feature not enabled: Partitioning", but you will also get a dump of the statement(s) it failed on.

如果您尝试导入，使用 ROWS=N，您将收到错误报告“ORA-00439：功能未启用：分区”，但您也会得到它失败的语句的转储。

It is wrapped at 75 characters with double quotes, but that is something you should be able to handle with a text editor (I like PSPad which has a column select mode that will easily get rid of the quotes at the start and end of each line, plus a JOIN LINES function to glue it all together.

它用双引号包裹在 75 个字符中，但这是您应该能够使用文本编辑器处理的内容（我喜欢 PSPad，它具有列选择模式，可以轻松摆脱每行开头和结尾的引号，加上一个 JOIN LINES 函数将它们粘合在一起。

You could also use dbms_metadata.get_ddl to pull the DDL from the source.

您还可以使用 dbms_metadata.get_ddl 从源中提取 DDL。

With either of those, you'd need to edit the stamements to remove the partitioning clauses before running them in XE.

对于其中任何一个，您都需要在 XE 中运行它们之前编辑语句以删除分区子句。

If you have lots of partitioned tables, then see if the DBA can set you up with a temporary user in the source environment with privileges to do a CREATE TABLE abc AS SELECT * FROM realuser.abc WHERE 1=2;

如果您有很多分区表，那么看看 DBA 是否可以在源环境中为您设置一个临时用户，该用户有权执行 CREATE TABLE abc AS SELECT * FROM realuser.abc WHERE 1=2;

You'll have a bunch of empty tables with the appropriate structure but no partitions and can do an export of them, import into XE and then do another import if you want the rows.

您将拥有一堆具有适当结构但没有分区的空表，并且可以将它们导出，导入到 XE 中，然后如果您需要这些行，则再进行一次导入。

You may have similar problems with partitioned indexes. I'd go for DBMS_METADATA.GET_DDL for them and hack the results.

对于分区索引，您可能会遇到类似的问题。我会为他们寻找 DBMS_METADATA.GET_DDL 并破解结果。

回答by George Kopf

Here is what I'm doing with expdp/impdp

这是我正在用 expdp/impdp 做的事情

1. Use impdp SQLFILE=Create_Non_Partitioned_Tables.sql EXCLUDE=STATISTICS
2. Copy that file to Create_Indexes_Constraints.sql
3. Edit Create_Non_Partitioned_Tables.sqlto remove all references to indexes and constraints and partitions, leaving just the CREATEqueries.
4. Edit Create_Indexes_Constraints.sqlto remove all references to partitioned tables and the CREATEqueries.
5. Run Create_Non_Partitioned_Tables.sqlto create non-partitioned tables.
6. Run impdpwith the option CONTENT=DATA_ONLY
7. Run Create_Indexes_Constraints.sqlto create the indexes and constraints.

1. 使用impdp SQLFILE=Create_Non_Partitioned_Tables.sql EXCLUDE=STATISTICS
2. 将该文件复制到 Create_Indexes_Constraints.sql
3. 编辑Create_Non_Partitioned_Tables.sql以删除对索引、约束和分区的所有引用，只留下CREATE查询。
4. 编辑Create_Indexes_Constraints.sql以删除对分区表和CREATE查询的所有引用。
5. 运行Create_Non_Partitioned_Tables.sql以创建非分区表。
6. impdp使用选项运行CONTENT=DATA_ONLY
7. 运行Create_Indexes_Constraints.sql以创建索引和约束。

回答by SUJIT MAHAPATRA

Oracle 11g onwards supports exporting tables by merging partitions in EXPDP. Please refer to the option of PARTITION_OPTIONSwhen exporting using the expdp utility.

Oracle 11g 以后支持通过合并 EXPDP 中的分区来导出表。请参阅PARTITION_OPTIONS使用 expdp 实用程序导出时的选项。

More at Oracle Data Pump 10g.

更多信息参见 Oracle Data Pump 10g。

回答by MichaelN

Use the "indexfile" option to get a table and index creation file of the tables you are importing. Modify that file to give you the table create statement without partitions. Run the creation commands to pre-create the tables in the db you are importing into. Then imp using "ignore=y" and it should import into the precreate table and not error out due to the create statement.

使用“indexfile”选项获取您正在导入的表的表和索引创建文件。修改该文件，为您提供不带分区的 table create 语句。运行创建命令以在要导入的数据库中预先创建表。然后imp使用“ignore=y”，它应该导入到precreate表中，并且不会因为create语句而出错。