We help IT Professionals succeed at work.

Import data from partitioned table

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

Watch Question

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
Production Manager / Application Support Manager
Unlock this solution and get a sample of our free trial.
(No credit card required)
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.


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?

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.