Import data from partitioned table

Posted on 2007-10-08
Medium Priority
Last Modified: 2008-01-09
I have one partitioned table with partitioned index. Exported data was ok without any error. But when I imported data using the following command and got error message "IMP-00058: ORACLE error 904 encountered".
imp  myschema/password@xxx buffer=16384 ignore=y indexes=n grants=n file=myexp.dmp log=imp.log commit=y feedback=1000000 full=n tables= tablename

I created one new table which would get the imported data. The new table has one difference. It was changed from  PARTITION BY RANGE(another_column)   to  PARTITION BY HASH(one_column)              

Any options need to be set up when importing data from the partitioned table (partitioned by range) to the partitioned table (partitioned by hash)?

Question by:luyan
LVL 18

Expert Comment

ID: 20037538
Why do not you make the tables identical and do the import first. Then modiy the table definition and copy/manipulate the data in the target database. Normally you'll get these errors when the source table def  does not match with the target
LVL 28

Accepted Solution

Naveen Kumar earned 2000 total points
ID: 20037543
I have not tried in the way you are saying but i tried something like the below :

1) i created a table with the same definition as in export file ( table with partition by range ) and ran the import. so it got the table without any errors.
2) created a  dummy table with same definition but with partition by hash (..)
3) insert records into dummy table from table in step 1 and then commit;
4) finally drop the table in step 1) and rename dummy table to original table name in step 1)


Expert Comment

ID: 20039106
not sure if it's possible to be done with dbms_redefinition, but it's worth to check it , this way downtime will be less. First import it in a table as suggested by others- with same definition and then use dbms_redifinition package to modify it.

Author Comment

ID: 20051151
Thanks for your information!
My steps:
-export the table with partition by range
-create new table with new partition by hash, and create the indexes for the table
-import data into the new table
And I got the following error message:
     IMP-00019: row rejected due to ORACLE error 2291
     IMP-00003: ORACLE error 2291 encountered
     ORA-02291: integrity constraint (tablespacename.FK_xxx) violated - parent key not found

Anything wrong?
Is it right to create indexes before import data?

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question