René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

dbms_redefinition

Privileges needed

A user needs the following privileges to use dbms_redefinition:
  • execute on dbms_redefinition
  • alter any table
  • create any table
  • drop any table
  • lock any table
  • select any table

Example

set feedback off

create table tableA (
  col1 Varchar2(3),
  col2 Number primary key,
  col3 Number(6)
);


insert into tableA values ('1',1,1);
insert into tableA values ('2',2,2);
insert into tableA values ('3',3,3);
insert into tableA values ('4',4,4);

commit;

create table tableA_temp (
  col1 Number(20),
  col2 Number primary key,
  col3 Number(3)
);

begin
  dbms_redefinition.start_redef_table(
    user,
    'tableA',
    'tableA_temp',
    'to_number(col1) col1, col2 col2, col3 col3');

  dbms_redefinition.sync_interim_table(
    user,
    'tableA',
    'tableA_temp');

  dbms_redefinition.finish_redef_table(
    user,
    'tableA',
    'tableA_temp');
end;
/

drop table tableA_temp;

desc tableA;

select * from tableA;

drop table tableA;