Insufficient privilege error with DBMS_REDEFINITION.start_redef_table

Question

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?

Did you create `TABLE_1` already before you run `DBMS_REDEFINITION.start_redef_table`? — Wernfried Domscheit, May 29 '14 at 18:57
Have you had any luck on this one? (i.e. I'm facing the same ...) — kiwiwings, Oct 24 '14 at 15:49
I just discovered that privileges requirements of procedures from DBMS_REDEFINITION package differ between oracle db versions. At version *11.2.0.1* it required a lot of `create any` global grants, while at version *11.2.0.4* is only requires `create` schema-local grants. — Krzysztof Jabłoński, Sep 19 '17 at 14:30
I am also getting the same error. Can anyone of you help me to resolve the same. — , Jul 15 '18 at 05:34

score 3 · Answer 1 · answered May 22 '14 at 13:49

3

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.

answered May 22 '14 at 13:49

Wernfried Domscheit

54,457
9
76
110

Thanks for the response. But no luck with this also, getting same error. – Nitin_Sen May 29 '14 at 12:55
I assume then you have to ask Oracle support. They should know which operation is executed in line 50 at package DBMS_REDEFINITION. – Wernfried Domscheit May 29 '14 at 18:56
seems fair. Could you please guide me how to reach oracle support in that case. Is there any specific team for that..? – Nitin_Sen May 30 '14 at 10:29
Go to Oracle Support page and open a "SR". Follow the form. – Wernfried Domscheit May 30 '14 at 11:09

score 2 · Answer 2 · answered Jul 15 '18 at 05:45

I have just faced this issue. and I was getting exactly the same error on start_redef_table.

So after searching a lot I gave below privileges to user and it worked.

grant execute on dbms_redefinition package to myuser (You already have)

then

grant CREATE ANY TABLE to myuser;

grant  ALTER ANY TABLE to myuser;

grant  DROP ANY TABLE to myuser;

grant LOCK ANY TABLE to myuser;

grant SELECT ANY TABLE to myuser;

After giving these privileges start_redef_table is working perfectly.

Is it safe to give all above system privileges to non-dba users? — homer, May 22 '19 at 15:59

Dudi Boy · Answer 3 · 2018-02-20T10:36:01.127

On Oracle 12c there is additional system privilege 'REDEFINE ANY TABLE'.

This works for me.

grant REDEFINE ANY TABLE  to myUser;
grant ADMINISTER DATABASE TRIGGER to myUser;
grant ALTER ANY INDEX to myUser; 
grant ALTER ANY MATERIALIZED VIEW to myUser;
grant ALTER ANY SEQUENCE to myUser;
grant ALTER ANY TRIGGER to myUser;
grant CREATE ANY INDEX to myUser;
grant CREATE ANY MATERIALIZED VIEW to myUser;
grant CREATE ANY SEQUENCE to myUser;
grant CREATE ANY TABLE to myUser;
grant CREATE ANY TRIGGER to myUser;
grant CREATE ANY VIEW to myUser;
grant CREATE MATERIALIZED VIEW to myUser;
grant CREATE SESSION to myUser;
grant CREATE VIEW to myUser;
grant DROP ANY INDEX to myUser;
grant DROP ANY MATERIALIZED VIEW to myUser;
grant DROP ANY SEQUENCE to myUser;
grant DROP ANY TRIGGER to myUser;
grant DROP ANY VIEW to myUser;
grant EXECUTE ANY PROCEDURE to myUser;
grant INSERT ANY TABLE to myUser;
grant MERGE ANY VIEW to myUser;
grant SELECT ANY DICTIONARY to myUser;
grant SELECT ANY TABLE to myUser;
grant UNDER ANY VIEW to myUser;
grant UPDATE ANY TABLE to myUser;

Good luck

score 0 · Answer 4 · answered Jun 05 '20 at 08:00

you were missing CREATE Materialized View after adding this I was successful redefining as normal user

for Oracle 12.1 instruction says https://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPLS67511

I think in other versions may be the same

grant CREATE MATERIALIZED VIEW 
grant CREATE TABLE 
grant EXECUTE on dbms_redefinition

Insufficient privilege error with DBMS_REDEFINITION.start_redef_table

4 Answers4