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 from SYS to normal user from where we can use data pump.

Source table is owned by sys is - systable, contains 10000 records and have primary key on a ID column. (1-10000)

Crate partition table (only structure, no data) middleman to act as the partion exchange partner for source sys table to destination normal table.

 SQL> create table middleman partition by range (ID) (partition part1 values less than (20000)) as select * from systable where rownum <=0;
 Table created.  
 Elapsed: 00:00:00.03  
 SQL> select count(*) from middleman;  
  COUNT(*)  
 ----------  
      0  
 Elapsed: 00:00:00.01  

Create destination table owned by user dbame.dest_table (owned by schema user dbame), again no data structure only.

 SQL> create table dbame.dest_Table as select * from systable where rownum <=0;  
 Table created.  
 Elapsed: 00:00:00.04  

Now perform the exchange,
        Step 1 - sys.systable to sys.middleman
        Step 2 - sys.middleman to dbame.dest_tabl

 SQL> alter table middleman exchange partition part1 with table systable;  
 Table altered.  
 Elapsed: 00:00:00.02  
 SQL> select count(*) from systable;  
  COUNT(*)  
 ----------  
      0  
 Elapsed: 00:00:00.00  
 SQL> select count(*) from middleman;  
  COUNT(*)  
 ----------  
    10000  
 Elapsed: 00:00:00.00  
 SQL> begin  
   dbms_stats.gather_table_stats  
   ( ownname  => user,  
    tabname  => 'MIDDLEMAN',  
    cascade  => TRUE );  
 end;  
 / 2  3  4  5  6  7  
 PL/SQL procedure successfully completed.  
 Elapsed: 00:00:00.13  
 SQL> select TABLE_NAME, PARTITION_NAME, NUM_ROWS from dba_Tab_partitions where table_name='MIDDLEMAN';  
 TABLE_NAME           PARTITION_NAME          NUM_ROWS  
 ------------------------------ ------------------------------ ----------  
 MIDDLEMAN           PART1                10000  
 Elapsed: 00:00:00.05  
 SQL> alter table middleman exchange partition part1 with table dbame.dest_Table;  
 Table altered.  
 Elapsed: 00:00:00.04  
 SQL> select count(*) from middleman;  
  COUNT(*)  
 ----------  
      0  
 Elapsed: 00:00:00.00  
 SQL> select count (*) from dbame.dest_Table;  
  COUNT(*)  
 ----------  
    10000  
 Elapsed: 00:00:00.00  

As simple as that. We moved table from SYS to dbame schema without any inserts or CTAS in no-time. Now data is owned by schema user and we can perform the datapump.
Not only in situation this method is nice workaround to move table between schemas.