sábado, 26 de julio de 2008

ORA-01440: column to be modified must be empty to decrease precision or scale

ALTER TABLE t1 MODIFY (nr_pct NUMBER(5,2))
ORA-01440: column to be modified must be empty to decrease precision or scale
¿Cómo disminuir la longitud de una columna con datos y evitar el error?

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.

2 comentarios:

g€®mãn ã£Øn§Ø dijo...

La forma 2 he tratado de probarla y tiene varios errores, comenzado con que indicaste que la nueva columna es NOT NULL sin un DEFAULT

Anónimo dijo...

las 2 formas tienen errores. Hay que ver si la columna a cambiar no es NOT NULL por que no te dejara actualizar como NULL la columna para cambiar el tamaño, tendrias que modificar el campo 3 veces, quitarle el defecto de NOT NULL, cambiar el tamaño, actualizar con la data y luego volver a cambiarla a NOT NULL.