Privileges Required for the DBMS_REDEFINITION Package

Sometimes  Oracle Developers face Ora-1031 error for insufficient privileges and execute privileges on dbms_redefinition is already given to them, So what is missing ??  Oracle Developers need to redefine the table online in oracle and its not possible to give them DBA privileges straight away, In this case only specific privileges should be given which are mentioned below :-

[node4.example.com ]$ oerr ora 1031

01031, 00000, "insufficient privileges"

// *Cause: An attempt was made to change the current username or password
//         without the appropriate privilege. This error also occurs if
//         attempting to install a database without the necessary operating
//         system privileges.
//         When Trusted Oracle is configure in DBMS MAC, this error may occur
//         if the user was granted the necessary privilege at a higher label
//         than the current login.

// *Action: Ask the database administrator to perform the operation or grant
//          the required privileges.
//          For Trusted Oracle users getting this error although granted the
//          the appropriate privilege at a higher label, ask the database
//          administrator to regrant the privilege at the appropriate label.

Privileges Required for the DBMS_REDEFINITION Package

Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:

CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

The following additional privileges are required to execute COPY_TABLE_DEPENDENTS:

CREATE ANY TRIGGER
CREATE ANY INDEX