4 Replies Latest reply on Feb 3, 2012 10:07 PM by 611900

    ORA-02327 while rebuilding index after IMPDP

    611900
      Hello,

      I am on 11.1.0.7 and using datapump to import data.

      Since this schema is big and consist of columns with datatype - LOB and XMLTYPE, impdp is taking a very long time. Requirement is to refresh one schema from another with different tablespace. We are using DATAPUMP for moving/refreshing this target schema.

      While doing IMPDP, we have seen that the data import is done in 1 Hr but the Index creation took 3 Hrs or so. Since there are other depending objects which demands both Source and Target to have same data before start App in the Source, we can not start the Production App till this 4 Hrs import is done.

      Due to it, we want to import only data so the the Table Structure and Table Data can be same and then we can start the App. We though of rebuliding Indexes later on the Target side. So, steps involved -

      1. Take expdp of the Source
      2. Disable all the Constraints on Target
      3. Disable all the Indexes on Target
      4. Truncate all the tables of Target
      5. Use impdp to import with the following parameters - CONTENT=DATA_ONLY EXCLUDE=INDEX
      6. Once data import is complete, Rebuild Indexes on Target
      7. Enable Constraints on Target

      But while doing step# 6, I got "ORA-02327: cannot create index on expression with datatype LOB" as there are LOB segments and looks like Oralce doesn't like that.

      Questions -

      1. What should be done to make step# 6 work or
      2. How can we copy over data quickly from one schema to another where we have parameters like REMAP_SCHEMA and REMAP_TABLESPACE

      Thanks for your time and inputs!