ORA-39166 During SYS expdp/impdp datapump
Ever come across a situation where you need to perform data pump operation on object owned by SYS?. This is not allowed in data pump and your operation should fail with ORA-39166.The old exp/imp was able to perform operation on SYS owned objects. Ideally you should not be in an situation to data pump on SYS. but in our case it was a data migration from Oracle 8i to 11g, and there was an intermediate state where some data modification to be performed. So when importing 8i data using old imp it was by mistake moved to SYS. And these were some huge tables with some data modification already done. We had the option of using old imp/exp but was preferring parallel data pump due to the performance. So what are options to quickly change ownership SYS owned table without recreating the tables. Enter - Partition exchange for rescue. This method is a quick workaround to change ownership of Oracle table without time consuming recreates. Below is an example where we change table owner ship ...