Change precision of timestamp columns

change_timestamp_precision.sql

set serveroutput on;
set autoprint on;

declare
    type t_temp_storage is table of varchar2(4000);

    column_constraints t_temp_storage;
    column_indexes t_temp_storage := t_temp_storage();
    column_indexes_names t_temp_storage;
    l_row number;

    cursor c_timestamp_columns is
        select
            c.table_name,
            c.column_name,
            c.data_type
        from user_tab_columns c
                 left join user_tables t on t.table_name = c.table_name
        where       t.table_name is not null
          and c.data_type = 'TIMESTAMP(6)'
          and c.table_name <> 'schema_version'
        order by c.table_name;

    err_msg varchar2(1000);

begin

    dbms_output.enable(null);

    for c in c_timestamp_columns loop
            begin
                dbms_output.put_line('MIGRATING COLUMN - ' || c.table_name || '.' || c.column_name);

                select
                    dbms_metadata.get_ddl(CASE WHEN uc.constraint_type = 'R' THEN 'REF_CONSTRAINT' ELSE 'CONSTRAINT' END, uc.constraint_name)
                        bulk collect into column_constraints
                from user_constraints uc
                         left join all_cons_columns acc on acc.constraint_name = uc.constraint_name
                where uc.table_name = c.table_name
                  and acc.column_name = c.column_name;

                FOR l_row IN 1 .. column_constraints.COUNT
                    LOOP
                        dbms_output.put_line('Constraint found... ' || column_constraints (l_row));
                    END LOOP;

                select
                    index_name bulk collect into column_indexes_names
                from (
                         select /*+ no_merge(b) */
                             a.table_name, a.index_name, a.column_name, a.column_position, b.column_position as exp_col_position, b.column_expression
                         from all_ind_columns a
                                  left outer join xmltable(
                                 '/ROWSET/ROW'
                                 passing dbms_xmlgen.getXMLType(
                                         replace('select column_expression, column_position from all_ind_expressions where index_name = '':1''', ':1', a.index_name)
                                     )

                                 columns
                                     column_expression varchar2(4000) path 'COLUMN_EXPRESSION',
                                     column_position   number         path 'COLUMN_POSITION'
                             ) b

                                                  on a.column_position = b.column_position

                         where a.table_name = c.table_name and (a.column_name = c.column_name or b.column_expression like '%' || c.column_name || '%')
                     );


                FOR l_row IN 1 .. column_indexes_names.COUNT
                    LOOP
                        dbms_output.put_line('Index found... ' || column_indexes_names (l_row));
                    END LOOP;

                column_indexes.extend(column_indexes_names.count);
                FOR l_row IN 1 .. column_indexes_names.COUNT
                    LOOP
                        select
                            dbms_metadata.get_ddl('INDEX', column_indexes_names(l_row))
                        into column_indexes(l_row)
                        from dual;
                        dbms_output.put_line('drop index ' || column_indexes_names(l_row));
                        execute immediate('drop index ' || column_indexes_names(l_row));
                    END LOOP;

                dbms_output.put_line('1/6 creating temp column');
                execute immediate('alter table ' || c.table_name || ' add (c_temp timestamp(9))');

                dbms_output.put_line('2/6 copy data to temp column');
                execute immediate('update ' || c.table_name || ' set c_temp = ' || c.column_name);

                dbms_output.put_line('3/6 drop column cascade constraints');
                execute immediate('alter table ' || c.table_name || ' drop column ' || c.column_name || ' cascade constraints');

                dbms_output.put_line('4/6 rename temp column to origin name');
                execute immediate('alter table ' || c.table_name || ' rename column c_temp to ' || c.column_name);

                dbms_output.put_line('5/6 recreate column constraints');
                FOR l_row IN 1 .. column_constraints.COUNT
                    LOOP
                        execute immediate(column_constraints (l_row));
                    END LOOP;

                dbms_output.put_line('6/6 recreate column indexes');
                FOR l_row IN 1 .. column_indexes.COUNT
                    LOOP
                        execute immediate(column_indexes (l_row));
                    END LOOP;

                dbms_output.put_line('DONE - SUCCESS - ' || c.table_name || '.' || c.column_name );

            exception
                when others then
                    err_msg := substr(sqlerrm, 1, 100);
                    dbms_output.put_line(err_msg);

            end;
        end loop;

END;
/