Insufficient privilege error with DBMS_REDEFINITION.start_redef_table

5
votes

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?

4
oracleplsqloracle10gdbms-redefinition
Did you create TABLE_1 already before you run DBMS_REDEFINITION.start_redef_table? – Wernfried Domscheit
yes, it has been already created. – Nitin_Sen
Have you had any luck on this one? (i.e. I'm facing the same ...) – kiwiwings
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
I am also getting the same error. Can anyone of you help me to resolve the same. – user9892866

4 Answers

3
votes

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.

2
votes

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.

1
votes

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

0
votes

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