Importing a dmp with partitioned tables into Oracle XE

Question

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.

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?

Or can I create the exp somehow without partitions?

Answer 1

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.

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.

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

With either of those, you'd need to edit the stamements to remove the partitioning clauses before running them in 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;

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.

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

Answer 2

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.

Answer 3

Here is what I'm doing with expdp/impdp

1. Use impdp SQLFILE=Create_Non_Partitioned_Tables.sql EXCLUDE=STATISTICS
2. Copy that file to Create_Indexes_Constraints.sql
3. Edit the first file to remove all references to indexes and constraints and partitions
4. Edit the second file to remove all refernces to partitoned tables.
5. Run the first file to create non-partitoned tables.
6. Impdp CONTENT=DATA_ONLY
7. Run the second file to create the indexes and constraints.