This discussion is archived
14 Replies Latest reply: Jan 29, 2013 10:08 AM by Haider Hussain RSS

Error using dbms_redefinition package

Haider Hussain Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points