ALTER TABLE t1 MODIFY (nr_pct NUMBER(5,2))¿Cómo disminuir la longitud de una columna con datos y evitar el error?
ORA-01440: column to be modified must be empty to decrease precision or scale
Para este ejemplo, vamos a agregar una dificultad extra: la columna que queremos alterar es NOT NULL.
Presento dos formas de hacer esto con SQL*Plus:
FORMA 1: VACIANDO Y ALTERANDO
Respaldar la columna
CREATE TABLE t1_backup AS SELECT id, nr_pct FROM t1;Setear la columna como NULL
ALTER TABLE t1 MODIFY (nr_pct NULL);Vaciar la columna
UPDATE t1 SET nr_pct=NULL;Ajustar el tamaño deseado de la columna
ALTER TABLE t1 MODIFY (nr_pct NUMBER(5,2));Cargar los datos a la columna
UPDATE t1 SET nr_pct=(SELECT nr_pct FROM t1_backup WHERE t1_backup.id=t1.id);Alterar la columna como NOT NULL nuevamente
ALTER TABLE t1 MODIFY (nr_pct NOT NULL);
Ventajas: Se preserva el orden de las columnas dentro de la tabla
Desventajas: Poco performante para tablas muy grandes (millones de datos), requiere crear un índice en la tabla t1_backup para mejorar la performance
FORMA 2: AGREGANDO COLUMNA EXTRA
Agregar una columna a la tabla con el tamaño deseado
ALTER TABLE t1 ADD (nr_pct_2 NUMBER(5,2) NOT NULL);Cargar la nueva columna con los datos
UPDATE t1 SET nr_pct_2=nr_pct;Eliminar la columna original
ALTER TABLE t1 DROP COLUMN nr_pct;Renombrar la nueva columna al nombre de la original
ALTER TABLE t1 RENAME COLUMN nr_pct_2 TO nr_pct;
Ventajas: Más performante que la forma 1 para tablas grandes. Más fácil de realizar.
Desventajas: Sólo aplicable a 9i o superiores (8i no posibilita el renombrado de columnas).
La columna alterada quedará al final de la tabla si hacemos un DESC.