14 Replies Latest reply: Jan 29, 2013 12:08 PM by Haider Hussain RSS

    Error using dbms_redefinition package

    Haider Hussain
      Hi,

      I am trying to use dbms_redefinition package to add a column to a table using primary key method

      SQL> conn / as sysdba
      Connected.
      SQL> grant execute on sys.dbms_redefinition to hr;

      Grant succeeded.

      SQL> grant dba to hr;

      Grant succeeded.


      SQL> conn hr/<password>
      Connected.

      SQL> create table tab1(roll_no number constraint roll_pk primary key,last_name varchar2(30));

      Table created.

      SQL> insert into tab1 values (1,'Hall');

      1 row created.

      SQL> commit;

      Commit complete.

      SQL> insert into tab1 values (2,'Mall');

      1 row created.

      SQL> commit;

      Commit complete.

      SQL> insert into tab1 values (3,'Roll');

      1 row created.

      SQL> commit;

      Commit complete.


      Adding required column

      SQL>
      SQL> alter table tab1 add(first_name varchar2(30));

      Table altered.

      Checking if table can be redefined

      SQL> exec dbms_redefinition.can_redef_table('HR','TAB1',DBMS_REDEFINITION.CONS_USE_PK);

      PL/SQL procedure successfully completed.


      Creating interim table


      SQL> create table tab2 as select roll_no,first_name,last_name from tab1 where 1=2;

      Table created.

      Adding primary key to interim table

      SQL> alter table tab2 add constraint roll_pk_1 primary key (roll_no);

      Table altered.


      Starting redefinition :


      SQL> exec dbms_redefinition.start_redef_table('HR','TAB1','TAB2','ROLL_NO,FIRST_NAME,LAST_NAME');
      BEGIN dbms_redefinition.start_redef_table('HR','TAB1','TAB2','ROLL_NO,FIRST_NAME,LAST_NAME'); END;

      *
      ERROR at line 1:
      ORA-00439: feature not enabled: Advanced replication
      ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
      ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
      ORA-06512: at line 1


      SQL>

      Could you please help.

      Thanks,
      Haider