Oracle and SQL database application has to be migrated for new release.
The migration script includes increase of numeric column width from (16,2) to (20,2) for about 130 columns out of 200 column in each table.
The existing database is very huge. Each column width increase DDL statement takes alomost 5 minutes to execute. This is an unacceptable rate as for each tabl it takes hours.
I thought of an approach wherein we would copy the contents of the table into another temporary table, Truncate the table, Increase the column size, dump the table contents into the original table from the temporary table and delete the temporay table. Because we cannot loose the existing data of the client, is checking just the number of rows sufficient before truncating the original table?
Please inform me if there is any other standard and efficient way which works for both Oracle and SQL server.