Datapump Gotcha's


Whilst datapump - both impdp and expdp - are usually fast and efficient, there are a number of gotcha's to be aware of.


  • When performing expdp with the tablespaces clause, ensure that all associated objects - i.e. tables AND indexes reside in the same tablespace otherwise an error ORA-31655 will result. This is the case even if all of the tablespaces in which the associated objects reside have been listed.
  • For example, if TABLE1 resides in TSP1 and its index INDEX1 resides in TSP2, then if either TABLESPACES=TSP1, or TABLESPACES=TSP2, or TABLESPACES=TSP1, TSP2 is used, then an ORA-31655 will result. In order to perform such a datapump export, then, and against most classical principles of DBA training, either TABLE1 should be moved to TSP2 or INDEX1 should be moved to TSP1 followed by an appropriate tablespace datapump exported.


  • Despite documentation in the manuals to the contrary, any attempts to datapump export objects owned by the user SYS will fail with an error ORA-39165.
  • As prescribed by MOS - article 553402.1, in order to perform exports of SYS owned objects, the traditional exp needs to be used.
© copyright 2001-2014 | all rights reserved