Import data from partitioned table

Posted on 2007-10-08
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

    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

    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)

    LVL 5

    Expert Comment

    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

    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

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    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.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now