• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1375
  • Last Modified:

Import data from partitioned table

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)?

1 Solution
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
Naveen KumarProduction Manager / Application Support ManagerCommented:
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)

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.
luyanAuthor Commented:
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now