This is an update to the previous post, that allows the layout of the column mapping string and the interim table to be table driven. This allows more flexibility, though the preparation is more extensive.
At my current employer, we have come up with a requirement to change a large number of tables with columns of type CHAR to VARCHAR2 or TIMESTAMP, depending on the column contents. We want to do that with minimal downtime, so the use of DBMS_REDEFINITION is our only real option considering the size of several of the tables.
To accomplish this, we broke the problem down into several steps.
First, we built a table that would be used to drive the redefinition process. This allows us much more flexibility in the redefinition process, however it does also require us to have every column of the tables to be redefined stored in this table. Below is the layout of the driving table:
SQL> desc col_map_table
Name Null? Type
----------------------------------------- -------- ---------------------
DATA_TYPE VARCHAR2(100)
DATA_LENGTH NUMBER
NEW_DATA_TYPE VARCHAR2(100)
NEW_DATA_LENGTH NUMBER
CONVERSION_FORMULA VARCHAR2(1000)
COLUMN_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
NEW_COLUMN_NAME VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
COLUMN_ID NUMBER
Note that there is a lot of string manipulation involved in creating the conversion formula, it has to be embedded in a string. Below is the formula I wrote to convert a char(26) column to timestamp:
TO_TIMESTAMP(RTRIM(LTRIM(COLNAME)),'||''''||'yyyy-mm-dd-hh24.mi.ss.ff'||''''||') NNAME
The first step in the redefinition process rebuilds all the indices into the desired index tablespace There is probably a way to do this with DBMS_REDEFINITION also, but it appeared that the rebuild online option accomplished the same thing.
Next, we use DBMS_METADATA to extract the current table definition, using the transform_param option of DBMS_METADATA to make storage changes for the table if required. Then we went ahead and created the interim table (empty at this time).
Next, we used the driver table, col_map_table, to change data types on the columns of the interim table. This works much better than using the replace function since it just uses the alter table command to make the required changes.
One major problem we did run into is that when the data type for a column referenced in a referential integrity constraint is changed, an error will be generated out of the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS process because until the second table is redefined, the data types will be different. The only work around for this is to drop the constraint and re-create it after the tables are redefined. Disabling the constraint is not sufficient, an error is still generated.
We decided that we wanted the ability to review all the code before we ran it, so we added the runcrt option, which is set to true if we want the interim table created, and the run_redef option that is set to true if we also want the redefinition option to be run. The drop_interim_table option is used to tell the script whether or not to run the cleanup when completed. Below is the driving procedure I wrote to redefine a table:
CREATE OR REPLACE
procedure gen_ddl(in_owner in varchar2,
in_table_name in varchar2,
int_table_name in varchar2 :=null,
tablespace_name in varchar2 :=null,
ind_tablespace_name in varchar2:=null,
new_tablespace in varchar2 :=null,
new_ind_tablespace in varchar2 :=null,
runcrt boolean :=false,
runredef boolean :=false,
drop_interim_table boolean :=false)
authid current_user
as
h number;
th number;
h2 number;
th2 number;
cmd_string varchar2(2000);
ddl_text varchar2(32000);
orig_string varchar2(500);
new_string varchar2(500);
orig_string2 varchar2(500);
new_string2 varchar2(500);
orig_string3 varchar2(500);
new_string3 varchar2(500);
int_tabname varchar2(30);
int_tabcounter number :=0;
start_redef varchar2(32000);
copy_dependents varchar2(500);
finish_redef varchar2(500);
abort_redef varchar2(500);
redef_errors number(5):=0;
opts_flag varchar2(200);
nopts_flag number;
ind_defined number :=0;
success boolean :=false;
dropcmd varchar2(2000);
dropunused varchar2(2000);
col_map_string varchar2(32000);
begin
dbms_output.enable(1000000);
if new_ind_tablespace is not null
then
for ind_cursor in (select owner, index_name from all_indexes where table_name=in_table_name and owner=in_owner)
loop
move_indexes(in_owner=>ind_cursor.owner,in_index_name=>ind_cursor.index_name,in_tablespace_name=>new_ind_tablespace,runit=>runcrt);
end loop;
end if;
-- dbms_output.put_line(rtrim(owner)||'.'||rtrim(table_name));
h := dbms_metadata.open('TABLE');
dbms_metadata.set_filter(h,'SCHEMA',in_owner);
dbms_metadata.set_filter(h,'NAME',in_table_name);
th := dbms_metadata.add_transform(h,'MODIFY');
if new_tablespace is not null
then
dbms_metadata.set_remap_param(th,'REMAP_TABLESPACE',tablespace_name,new_tablespace);
end if;
if new_ind_tablespace is not null
then
dbms_metadata.set_remap_param(th,'REMAP_TABLESPACE',ind_tablespace_name,new_ind_tablespace);
end if;
if int_table_name is null
then
int_tabname:=in_table_name||'_INT';
else
int_tabname:=int_table_name;
end if;
map_cols(in_owner=>in_owner,in_table_name=>in_table_name,col_map_string=>col_map_string);
th := dbms_metadata.add_transform(h,'DDL');
dbms_metadata.set_transform_param(th,'STORAGE',false);
dbms_metadata.set_transform_param(th,'CONSTRAINTS',false);
dbms_metadata.set_transform_param(th,'REF_CONSTRAINTS',false);
if runcrt = true
then
dbms_metadata.set_transform_param(th,'PRETTY',false);
dbms_metadata.set_transform_param(th,'SQLTERMINATOR',false);
else
dbms_metadata.set_transform_param(th,'PRETTY',true);
dbms_metadata.set_transform_param(th,'SQLTERMINATOR',true);
end if;
orig_string:='CREATE TABLE "'||rtrim(in_owner)||'"'||'.'||'"'||rtrim(in_table_name)||'"';
new_string:='create table "'||in_owner||'"."'||int_tabname||'"';
--orig_string2:='VARCHAR2(26)';
--new_string2:='TIMESTAMP';
--orig_string3:='CHAR(26)';
--new_string3:='TIMESTAMP';
--select replace(replace(replace(replace(dbms_metadata.fetch_clob(h),orig_string,new_string),' CHAR','VARCHAR2'),orig_string2,new_string2),orig_string3,new_string3)
--into ddl_text from dual;
select replace(dbms_metadata.fetch_clob(h),orig_string,new_string) into ddl_text from dual;
opts_flag:='dbms_redefinition.cons_use_rowid';
nopts_flag:=dbms_redefinition.cons_use_rowid;
dbms_output.put_line(ddl_text);
mod_interim_table(in_owner,in_table_name,int_table_name,false);
start_redef:='dbms_redefinition.start_redef_table(uname=>'||''''||in_owner||''''||',orig_table=>'||''''||in_table_name||''''||',int_table=>'||''''||int_tabname||''''||',options_flag=>'||opts_flag||',col_mapping=>'||''''||col_map_string||''''||')';
copy_dependents:='dbms_redefinition.copy_table_dependents(uname=>'||''''||in_owner||''''||',orig_table=>'||''''||in_table_name||''''||',int_table=>'||''''||int_tabname||''''||',num_errors=>redef_errors)';
finish_redef:='dbms_redefinition.finish_redef_table(uname=>'||''''||in_owner||''''||',orig_table=>'||''''||in_table_name||''''||',int_table=>'||''''||int_tabname||''''||')';
abort_redef:='dbms_redefinition.abort_redef_table(uname=>'||''''||in_owner||''''||',orig_table=>'||''''||in_table_name||''''||',int_table=>'||''''||int_tabname||''''||')';
dbms_output.put_line(start_redef||';');
dbms_output.put_line(copy_dependents||';');
dbms_output.put_line(finish_redef||';');
dbms_output.put_line(abort_redef||';');
-- dbms_output.put_line('col_map_string::'||col_map_string);
dropcmd:='drop table '||in_owner||'.'||int_tabname;
dropunused:='alter table '||in_owner||'.'||in_table_name||' drop unused columns';
dbms_output.put_line(dropcmd||';');
dbms_output.put_line(dropunused||';');
if runcrt=true
then
dbms_output.put_line('Start redef - ddl text');
execute immediate ddl_text;
mod_interim_table(in_owner,in_table_name,int_table_name,runcrt);
-- dbms_output.put_line('DDL TEXTED');
if runredef=true
then
begin
dbms_output.put_line(start_redef);
dbms_redefinition.start_redef_table(uname=>in_owner,orig_table=>in_table_name,int_table=>int_tabname,options_flag=>nopts_flag,col_mapping=>col_map_string);
dbms_output.put_line('Finished start');
dbms_output.put_line(copy_dependents);
dbms_redefinition.copy_table_dependents(uname=>in_owner,orig_table=>in_table_name,int_table=>int_tabname,num_errors=>redef_errors);
dbms_output.put_line('Dependency Errors: '||to_char(redef_errors));
dbms_output.put_line(finish_redef);
dbms_redefinition.finish_redef_table(uname=>in_owner,orig_table=>in_table_name,int_table=>int_tabname);
success:=true;
exception
when others then
success:=false;
dbms_redefinition.abort_redef_table(uname=>in_owner,orig_table=>in_table_name,int_table=>int_tabname);
raise;
end;
if success=true and drop_interim_table=true
then
execute immediate dropunused;
execute immediate dropcmd;
end if;
end if;
end if;
end;
/
I mentioned earlier that we modified the interim table after creation to appropriate data types. Below is the procedure that we called to make the changes to the interim table:
procedure mod_interim_table(in_owner in varchar2, in_table_name varchar2, in_interim_table_name varchar2,runmod in boolean) authid current_user
is
cmd_string varchar2(32000);
begin
dbms_output.enable(100000);
for mod_cursor in (select column_name, new_column_name, new_data_type, new_data_length, column_id,
decode(new_column_name,null,replace(replace(conversion_formula,'COLNAME',column_name),
'NNAME',column_name),replace(replace(conversion_formula,'COLNAME',column_name),'NNAME',new_column_name)) conv_string
from col_map_table where owner=in_owner and table_name=in_table_name order by column_id)
loop
if mod_cursor.new_data_length is not null
then
cmd_string:='alter table '||in_owner||'.'||in_interim_table_name||' modify '||mod_cursor.column_name||' '||mod_cursor.new_data_type||'('||mod_cursor.new_data_length||')';
else
cmd_string:='alter table '||in_owner||'.'||in_interim_table_name||' modify '||mod_cursor.column_name||' '||mod_cursor.new_data_type;
end if;
dbms_output.put_line(cmd_string||';');
if runmod=true
then
execute immediate cmd_string;
end if;
if mod_cursor.new_column_name is not null
then
cmd_string:='alter table '||in_owner||'.'||in_interim_table_name||' rename column '||mod_cursor.column_name||' '||mod_cursor.new_column_name;
dbms_output.put_line(cmd_string||';');
if runmod=true
then
execute immediate cmd_string;
end if;
end if;
end loop;
end;
One of the more complicated parts of the DBMS_REDEFINITION process is creation of the column mapping, when columns have to be converted from one data type to another. I quickly discovered that if one column data type is being changed, all columns have to be listed in the the col_mapping parameter of DBMS_REDEFINITION.START_REDEF_TABLE. This can be very complex when, for example, quotation marks have to be embedded in the string. Here is the script for creating the COL_MAPPING. Note that it just appends the conversion formula to the end of the string, so the formula must have the appropriate quotation marks and any other required string manipulation commands embedded into it as part of a string:
procedure map_cols(in_owner in varchar2,in_table_name in varchar2,col_map_string in out varchar2) authid current_user
as
map_string varchar2(32000):=' ';
colcounter integer :=0;
colcounter2 integer :=0;
begin
dbms_output.enable(1000000);
execute immediate 'alter session set nls_timestamp_format='||''''||'yyyy-mm-dd-hh24.mi.ss.ff'||'''';
for colcursor in (select column_name, data_type, column_id from all_tab_columns where owner=in_owner and table_name=in_table_name order by column_id)
loop
colcounter:=colcounter+1;
end loop;
if colcounter > 0
then
for colcursor2 in (select column_name, data_type, data_length, new_column_name,
decode(new_column_name,null,replace(replace(conversion_formula,'COLNAME',column_name),'NNAME',column_name),replace(replace(conversion_formula,'COLNAME',column_name),'NNAME',new_column_name)) conv_string
from col_map_table where owner=in_owner and table_name=in_table_name)
loop
colcounter2:=colcounter2+1;
map_string:=rtrim(map_string)||colcursor2.conv_string;
if colcounter2<colcounter
then
map_string:=map_string||',';
end if;
end loop;
end if;
col_map_string:=rtrim(ltrim(map_string));
end;
The final piece of code involved in this was the code used to moved the indexes at the start. I am sure any experienced DBA could write this piece with a little thought, but here it is for the sake of completeness:
procedure move_indexes(in_owner in varchar2, in_index_name in varchar2, in_tablespace_name in varchar2,runit in boolean :=false)
is
move_cmd varchar2(4000);
begin
dbms_output.enable(1000000);
move_cmd:='alter index '||in_owner||'.'||in_index_name||' rebuild online parallel tablespace '||in_tablespace_name;
dbms_output.put_line(move_cmd||';');
if runit = true
then
execute immediate move_cmd;
end if;
end;
This process turned out to be one of the more interesting utilities I have written. Please feel free to post questions.
