Home » RDBMS Server » Server Administration » can not execute dbms_redefinition.start_redef_table (11.1.0.7 windows)
can not execute dbms_redefinition.start_redef_table [message #518841] Mon, 08 August 2011 01:33 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Hi,
Which privs do grant to user? i can not execute start_redef_table

SQL>  Begin
  2    Dbms_Redefinition.Can_Redef_Table(USER, 'TB_HXL_USER');
  3   End;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    dbms_redefinition.start_redef_table(
  3      uname      => USER,
  4      orig_table => 'TB_HXL_USER',
  5      int_table  => 'TB_HXL_USER_MID',
  6      options_flag => DBMS_REDEFINITION.cons_use_pk);
  7  END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

Re: can not execute dbms_redefinition.start_redef_table [message #518843 is a reply to message #518841] Mon, 08 August 2011 01:43 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

have you checked if the required privileges are give to the user:

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


There are some restrictions as well so check you are not doing any one among them.

One cannot redefine Materialized Views (MViews) and tables with MViews or MView Logs defined on them.
One cannot redefine Temporary and Clustered Tables
One cannot redefine tables with BFILE, LONG or LONG RAW columns
One cannot redefine tables belonging to SYS or SYSTEM
One cannot redefine Object tables
Table redefinition cannot be done in NOLOGGING mode (watch out for heavy archiving)
Cannot be used to add or remove rows from a table





Regards
Deepak

[Updated on: Mon, 08 August 2011 01:45]

Report message to a moderator

Re: can not execute dbms_redefinition.start_redef_table [message #518844 is a reply to message #518841] Mon, 08 August 2011 01:51 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables007.htm#sthref1900
Re: can not execute dbms_redefinition.start_redef_table [message #518845 is a reply to message #518841] Mon, 08 August 2011 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
At least the privileges and quota necessary to create the objects: TABLE/INDEX, TRIGGER, CONSTRAINT...

Regards
Michel
Re: can not execute dbms_redefinition.start_redef_table [message #518856 is a reply to message #518845] Mon, 08 August 2011 02:55 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Previous Topic: archiver error. Connect internal only, until freed.(merged)
Next Topic: how to apped when get ddl
Goto Forum:
  


Current Time: Fri Nov 29 06:51:47 CST 2024