Oracle – Insufficient privilege error with DBMS_REDEFINITION.start_redef_table

dbms-redefinitionoracleoracle10gplsql

I am trying to partition existing tables without dropping and recreating it using Oracle.

DBMS_REDEFINITION package in my Oracle 10g supporting application

I have have given all the necessary permission to the user as per mentioned in the oracle document.

grant CREATE ANY TABLE to DDUSER;
grant ALTER ANY TABLE to DDUSER;
grant DROP ANY TABLE to DDUSER;
grant LOCK ANY TABLE to DDUSER;
grant SELECT ANY TABLE to DDUSER;
grant execute on dbms_redefinition to DDUSER;

I am able to execute below procedure

begin
Dbms_Redefinition.Can_Redef_Table('DDUSER', 'TABLE');
end;
This throws no error neither any result (Assuming this is as expected)

But when I am trying to run

BEGIN
  DBMS_REDEFINITION.start_redef_table(
    uname      => 'DDUSER',        
    orig_table => 'TABLE',
    int_table  => 'TABLE_1');
END;

I am getting below error:

Error report:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2
01031. 00000 – "insufficient privileges"

Could you please help me, what privilege I am missing here?
or if there is any idea about which operation is executed in line 50 at package DBMS_REDEFINITION?

Best Answer

Try this one:

grant DROP ANY INDEX to DDUSER;
grant CREATE ANY INDEX to DDUSER;

If the tabls contains an index (most probably that's the case) you have to create new indexes.

Related Topic