Correcting datatypes with minimal downtime

SQL> drop table t purge;

Table dropped.

SQL> drop table t_interim purge;

Table dropped.

--
-- Source table with string primary key that we want to convert to numeric
--
SQL>
SQL> create table t as
  2  select
  3     to_char(object_id) pk
  4    ,owner
  5    ,object_name
  6    ,subobject_name
  7    ,object_id
  8    ,data_object_id
  9  from all_objects
 10  where object_id is not null;

Table created.

SQL>
SQL> alter table t add primary key ( pk );

Table altered.

--
-- Empty interim table with numeric string primary, representing our corrected data type
--
SQL>
SQL> create table t_interim as
  2  select
  3     object_id pk
  4    ,owner
  5    ,object_name
  6    ,subobject_name
  7    ,object_id
  8    ,data_object_id
  9  from all_objects
 10  where 1=0;

Table created.

SQL>
SQL> desc t
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------------------------
 PK                                                                      NOT NULL VARCHAR2(40)
 OWNER                                                                   NOT NULL VARCHAR2(128)
 OBJECT_NAME                                                             NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                                                                   VARCHAR2(128)
 OBJECT_ID                                                               NOT NULL NUMBER
 DATA_OBJECT_ID                                                                   NUMBER

SQL>
SQL> desc t_interim
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------------------------
 PK                                                                      NOT NULL NUMBER
 OWNER                                                                   NOT NULL VARCHAR2(128)
 OBJECT_NAME                                                             NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                                                                   VARCHAR2(128)
 OBJECT_ID                                                               NOT NULL NUMBER
 DATA_OBJECT_ID                                                                   NUMBER

--
-- Standard call will fail, because the columns do not align
--

SQL>
SQL> begin
  2    dbms_redefinition.start_redef_table
  3         (  uname           => user,
  4            orig_table      => 'T',
  5            int_table       => 'T_INTERIM'
  6            );
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 109
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3887
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5208
ORA-06512: at line 2

--
-- Column mapping call fails, because the columns being manipulated is the primary key
--

SQL>
SQL> declare
  2    l_colmap varchar2(200) :=
  3      q'{   to_number(pk) as pk
  4           ,owner
  5           ,object_name
  6           ,subobject_name
  7           ,object_id
  8           ,data_object_id}';
  9  begin
 10    dbms_redefinition.start_redef_table
 11         (  uname           => user,
 12            orig_table      => 'T',
 13            int_table       => 'T_INTERIM',
 14            col_mapping   => l_colmap
 15            );
 16  end;
 17  /
declare
*
ERROR at line 1:
ORA-42008: error occurred while instantiating the redefinition
ORA-12016: materialized view does not include all primary key columns
ORA-06512: at "SYS.DBMS_REDEFINITION", line 109
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3887
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5208
ORA-06512: at line 10

--
-- Abort this run to reset our objects
--

SQL>
SQL> begin
  2    dbms_redefinition.abort_redef_table
  3         (  uname           => user,
  4            orig_table      => 'T',
  5            int_table       => 'T_INTERIM'
  6            );
  7  end;
  8  /

PL/SQL procedure successfully completed.

--
-- Use the ROWID in lieu of the primary key
--

SQL>
SQL> declare
  2    l_colmap varchar2(200) :=
  3      q'{   to_number(pk) as pk
  4           ,owner
  5           ,object_name
  6           ,subobject_name
  7           ,object_id
  8           ,data_object_id}';
  9  begin
 10    dbms_redefinition.start_redef_table
 11         (  uname           => user,
 12            orig_table      => 'T',
 13            int_table       => 'T_INTERIM',
 14            col_mapping   => l_colmap,
 15            options_flag=>DBMS_REDEFINITION.cons_use_rowid
 16            );
 17  end;
 18  /

PL/SQL procedure successfully completed.

--
-- Add our subordinate objects
--

SQL>
SQL> alter table t_interim add primary key ( pk );

Table altered.

--
-- And finally finish off the process (this is the window of unavailability)
--

SQL>
SQL> begin
  2    dbms_redefinition.finish_redef_table
  3         (  uname           => user,
  4            orig_table      => 'T',
  5            int_table       => 'T_INTERIM');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> desc t
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------------------------
 PK                                                                      NOT NULL NUMBER
 OWNER                                                                   NOT NULL VARCHAR2(128)
 OBJECT_NAME                                                             NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                                                                   VARCHAR2(128)
 OBJECT_ID                                                               NOT NULL NUMBER
 DATA_OBJECT_ID                                                                   NUMBER