Search notes:
DBMS_REDEFINITION example: Rename columns and change data types
Create a table for this example …
create table tq84_redefinition (
id number primary key,
num varchar2(10),
ts timestamp
);
… and insert some values:
insert into tq84_redefinition values (1, '42' , systimestamp + 59/23);
insert into tq84_redefinition values (2, '-9.876', systimestamp + 51/31);
insert into tq84_redefinition values (3, '1.2e3' , systimestamp + 61/17);
commit;
select * from tq84_redefinition;
Check if table can be redefined:
begin
dbms_redefinition.can_redef_table(user, 'tq84_redefinition');
end;
/
Create an (interim) table with the correct
column definitions.
create table tq84_redefinition_int (
id number , -- No primary key to prevent «ORA-01408: such column list already indexed»
num number(6,2), -- Change varchar2 to number
dt date -- Rename column and data type
);
Start the redifinition:
begin
dbms_redefinition.start_redef_table(
uname => user,
orig_table => 'tq84_redefinition',
int_table => 'tq84_redefinition_int',
col_mapping => q'[
id,
cast(num as number) num,
cast(ts as date ) dt
]',
options_flag => dbms_redefinition.cons_use_pk,
orderby_cols => null,
part_name => null,
continue_after_errors => false,
copy_vpd_opt => dbms_redefinition.cons_vpd_none,
refresh_dep_mviews => 'N',
enable_rollback => false
);
end;
/
If something goes wrong, the started redefinition can be aborted. This step is hopefully not needed.
begin
dbms_redefinition.abort_redef_table (
uname => user,
orig_table =>'tq84_redefinition',
int_table =>'tq84_redefinition_int',
part_name => null
);
end;
/
Clone dependent
objects like grants, triggers,
constraints and
privileges from the table being redefined to the interim table (which represents the post-redefinition table).
declare
cnt_errors binary_integer;
begin
dbms_redefinition.copy_table_dependents(
uname => user,
orig_table =>'tq84_redefinition',
int_table =>'tq84_redefinition_int',
copy_indexes => dbms_redefinition.cons_orig_params,
copy_triggers => true,
copy_constraints => true,
copy_privileges => true,
ignore_errors => false,
num_errors => cnt_errors,
copy_statistics => true,
copy_mvlog => false);
if cnt_errors > 0 then
dbms_output.put_line('There were ' || cnt_errors || ' errors.');
end if;
end;
/
exec dbms_redefinition.sync_interim_table(user, 'tq84_redefinition', 'tq84_redefinition_int');
exec dbms_redefinition.finish_redef_table(user, 'tq84_redefinition', 'tq84_redefinition_int');
Select from the two tables. Their column definitions have exchanged with each other.
select * from tq84_redefinition;
select * from tq84_redefinition_int;
drop table tq84_redefinition_int purge;
drop table tq84_redefinition purge;