Módulo 29: Modificación y borrado de objetos
Objetivos del módulo
- Comprender por qué un esquema de base de datos evoluciona y qué riesgos tiene modificarlo cuando ya contiene datos
- Dominar la sentencia
ALTER TABLE: añadir, modificar, eliminar y renombrar columnas - Saber qué cambios de tipo, tamaño y nulabilidad admite Oracle sobre una tabla con datos y reconocer los errores
ORA-que aparecen cuando no es posible - Gestionar restricciones existentes: añadirlas, borrarlas, renombrarlas, habilitarlas y deshabilitarlas, con y sin validación de los datos
- Usar
SET UNUSED,READ ONLY,MOVE,RENAMEyCOMMENT - Borrar tablas con
DROP TABLEy recuperarlas desde la papelera de reciclaje conFLASHBACK TABLE - Distinguir con precisión
TRUNCATEdeDELETE - Conocer las cláusulas
IF [NOT] EXISTSde Oracle 23ai/26ai - Entender y demostrar el commit implícito de las sentencias DDL
- Escribir scripts de migración de un esquema real con buenas prácticas de despliegue
1. Introducción: un esquema nunca está terminado
En los módulos anteriores has creado las tablas del esquema EMPRESA con CREATE TABLE (módulo 23) y has definido sus restricciones (módulo 24). En un mundo ideal, el diseño sería perfecto desde el primer día y nunca habría que tocarlo. En el mundo real, eso no ocurre nunca:
- Aparecen requisitos nuevos (“necesitamos guardar la fecha de baja de los empleados”).
- Se detectan errores de diseño (“el descuento está definido como
NUMBER(4,2)y no admite el valor 100”). - Cambia la normativa (“hay que guardar el consentimiento RGPD de cada cliente”).
- Se mejora el rendimiento o la calidad de los datos (“la localidad del departamento debería ser una tabla aparte”).
📘 Concepto: La evolución del esquema (schema evolution) es el conjunto de cambios que sufre la estructura de una base de datos a lo largo de su vida. Se realiza con las sentencias DDL de modificación (
ALTER), borrado (DROP,TRUNCATE) y renombrado (RENAME), y suele organizarse en scripts de migración versionados.
Modificar una tabla vacía es trivial. Modificar una tabla con millones de filas, que usan cientos de usuarios y que está referenciada por otras tablas es una operación delicada: algunos cambios son instantáneos, otros reescriben la tabla entera, y otros Oracle sencillamente no los permite para no perder información.
Sentencias que veremos
| Sentencia | Sublenguaje | Para qué sirve |
|---|---|---|
ALTER TABLE | DDL | Cambiar la estructura de una tabla: columnas, restricciones, modo de lectura, almacenamiento |
RENAME | DDL | Cambiar el nombre de una tabla, vista, secuencia o sinónimo privado |
DROP TABLE | DDL | Eliminar una tabla (estructura + datos) |
TRUNCATE TABLE | DDL | Vaciar una tabla de forma rápida e irreversible |
FLASHBACK TABLE ... TO BEFORE DROP | DDL | Recuperar una tabla borrada desde la papelera |
PURGE | DDL | Vaciar la papelera de reciclaje |
COMMENT ON | DDL | Documentar tablas y columnas en el diccionario de datos |
⚠️ Importante: Todas las salidas de este módulo se han obtenido ejecutando las sentencias en Oracle AI Database 26ai Free con SQLPlus. Los mensajes de SQLPlus (
Tabla modificada.,3 filas seleccionadas.) se muestran en español, como los verás con un cliente configurado en español; los mensajes de errorORA-aparecen en inglés porque la edición Free solo incluye ese idioma. Además, desde 23ai cada error va seguido de una líneaHelp: https://docs.oracle.com/error-help/db/ora-NNNNN/que omitimos por brevedad. La sesión usaNLS_LANGUAGE = 'SPANISH',NLS_TERRITORY = 'SPAIN'(por eso los decimales se muestran con coma) yNLS_DATE_FORMAT = 'DD/MM/YYYY', y los valores nulos se muestran como(nulo)gracias al comando de SQL*PlusSET NULL '(nulo)'.
💡 Consejo: Los ejemplos modifican las tablas de EMPRESA. Cuando termines cada sección (o si algo se rompe), vuelve al estado inicial ejecutando empresa_borrar.sql, empresa_crear.sql y empresa_datos.sql. Tener un script que reconstruye el esquema desde cero es, de hecho, la primera buena práctica de este módulo.
2. La sentencia ALTER TABLE
ALTER TABLE es una sentencia “paraguas”: con ella se hace casi cualquier cambio sobre una tabla existente. Su forma general es:
ALTER TABLE [esquema.]tabla
clausula_de_modificacion;
Las cláusulas más importantes son:
flowchart LR
AT["ALTER TABLE tabla"] --> COL["Columnas"]
AT --> RES["Restricciones"]
AT --> TAB["Tabla completa"]
COL --> ADD["ADD"]
COL --> MOD["MODIFY"]
COL --> DRO["DROP COLUMN / DROP (...)"]
COL --> UNU["SET UNUSED / DROP UNUSED COLUMNS"]
COL --> REN["RENAME COLUMN"]
RES --> ADC["ADD CONSTRAINT"]
RES --> DRC["DROP CONSTRAINT / PRIMARY KEY / UNIQUE"]
RES --> RNC["RENAME CONSTRAINT"]
RES --> ENA["ENABLE / DISABLE [VALIDATE / NOVALIDATE]"]
RES --> MDC["MODIFY CONSTRAINT"]
TAB --> RO["READ ONLY / READ WRITE"]
TAB --> RT["RENAME TO"]
TAB --> MV["MOVE"]
📘 Concepto: A diferencia de MySQL, Oracle no permite elegir la posición de una columna nueva (no existe
AFTER columnaniFIRST): las columnas añadidas siempre van al final. El orden físico de las columnas es irrelevante en el modelo relacional (las columnas de una relación no tienen orden); si necesitas un orden concreto en la salida, escríbelo en elSELECTo crea una vista.
3. Añadir columnas: ADD
3.1. Sintaxis básica
-- Una sola columna (los paréntesis son opcionales)
ALTER TABLE tabla ADD columna tipo [DEFAULT expr] [restricción_en_línea];
-- Varias columnas a la vez (paréntesis obligatorios)
ALTER TABLE tabla ADD (
columna1 tipo [...],
columna2 tipo [...]
);
💻 Ejemplo: Añadimos la fecha de nacimiento a los empleados y, de una vez, teléfono y correo a los departamentos.
ALTER TABLE empleados ADD fecha_nacimiento DATE;
ALTER TABLE departamentos ADD (
telefono VARCHAR2(15),
email VARCHAR2(60)
);
DESC departamentos
Tabla modificada.
Tabla modificada.
Nombre ¿Nulo? Tipo
--------------------------------------- -------- ----------------------------
DEPT_ID NOT NULL NUMBER(4)
NOMBRE NOT NULL VARCHAR2(40)
LOCALIDAD VARCHAR2(40)
PRESUPUESTO NUMBER(12,2)
TELEFONO VARCHAR2(15)
EMAIL VARCHAR2(60)
Las columnas nuevas aparecen al final y, en las filas que ya existían, valen NULL:
SELECT dept_id, nombre, telefono, email
FROM departamentos
WHERE dept_id <= 30;
DEPT_ID NOMBRE TELEFONO EMAIL
---------- ------------ ---------- ----------
10 DIRECCIÓN (nulo) (nulo)
20 VENTAS (nulo) (nulo)
30 INFORMÁTICA (nulo) (nulo)
3 filas seleccionadas.
3.2. Columnas NOT NULL en una tabla con datos
¿Qué pasa si la columna nueva es obligatoria? Las filas existentes tendrían que recibir algún valor, y si no le decimos cuál, Oracle no puede inventárselo:
ALTER TABLE empleados ADD nif VARCHAR2(9) NOT NULL;
ALTER TABLE empleados ADD nif VARCHAR2(9) NOT NULL
*
ERROR en línea 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
Hay dos soluciones:
- Dar un valor por defecto junto con el
NOT NULL: las filas existentes reciben ese valor. - Hacerlo en tres pasos: añadir la columna admitiendo nulos, rellenarla con
UPDATEy, por último, convertirla enNOT NULLconMODIFY(lo verás en el ejercicio resuelto 1).
ALTER TABLE empleados ADD activo CHAR(1) DEFAULT 'S' CONSTRAINT nn_emp_activo NOT NULL;
SELECT emp_id, nombre, activo FROM empleados WHERE emp_id <= 103;
Tabla modificada.
EMP_ID NOMBRE A
---------- ---------- -
100 Ana S
101 Luis S
102 Marta S
103 Pedro S
4 filas seleccionadas.
3.3. DEFAULT sin NOT NULL: ¿reciben valor las filas existentes?
Mucha gente cree que el DEFAULT solo se aplica a las filas futuras. En Oracle, cuando se añade una columna con DEFAULT, las filas existentes también reciben el valor por defecto, aunque la columna admita nulos:
ALTER TABLE empleados ADD nivel NUMBER(1) DEFAULT 1;
SELECT emp_id, nombre, nivel FROM empleados WHERE emp_id <= 102;
Tabla modificada.
EMP_ID NOMBRE NIVEL
---------- ---------- ----------
100 Ana 1
101 Luis 1
102 Marta 1
3 filas seleccionadas.
📘 Concepto: Desde Oracle 11g (columnas
NOT NULL) y 12c (columnas que admiten nulos), añadir una columna conDEFAULTes una operación solo de metadatos (metadata-only): Oracle no escribe el valor en cada fila, sino que lo guarda una vez en el diccionario y lo “rellena” al leer las filas antiguas. Por eso añadir una columna con valor por defecto a una tabla de cien millones de filas tarda lo mismo que en una tabla de diez: milisegundos.
⚠️ Importante: No confundas añadir una columna con
DEFAULT(afecta a las filas existentes) con modificar elDEFAULTde una columna que ya existe (solo afecta a las inserciones futuras, sección 4.6).
3.4. DEFAULT ON NULL
Un DEFAULT normal solo actúa cuando la columna se omite en el INSERT. Si alguien inserta explícitamente NULL, se guarda NULL. La variante DEFAULT ON NULL (Oracle 12c) también sustituye el NULL explícito, y convierte la columna en NOT NULL automáticamente:
ALTER TABLE empleados ADD turno VARCHAR2(10) DEFAULT ON NULL 'MAÑANA';
INSERT INTO empleados (emp_id, nombre, apellidos, email, fecha_alta, oficio, salario, turno)
VALUES (900, 'Prueba', 'Pérez Pérez', 'prueba@empresa.es', DATE '2026-01-01', 'BECARIO', 900, NULL);
SELECT emp_id, nombre, turno, activo, nivel FROM empleados WHERE emp_id = 900;
ROLLBACK;
Tabla modificada.
1 fila creada.
EMP_ID NOMBRE TURNO A NIVEL
---------- ---------- ---------- - ----------
900 Prueba MAÑANA S 1
1 fila seleccionada.
Rollback terminado.
Aunque el INSERT pasó NULL en turno, se guardó 'MAÑANA'. Las columnas activo y nivel se omitieron en el INSERT y tomaron su DEFAULT.
3.5. Columnas con restricción en línea, columnas duplicadas y columnas virtuales
Puedes añadir la columna junto con una restricción con nombre:
ALTER TABLE empleados ADD extension NUMBER(4)
CONSTRAINT ck_emp_extension CHECK (extension BETWEEN 1000 AND 9999);
Tabla modificada.
Si la columna ya existe, Oracle lo indica:
ALTER TABLE empleados ADD nombre VARCHAR2(30);
ALTER TABLE empleados ADD nombre VARCHAR2(30)
*
ERROR en línea 1:
ORA-01430: column being added already exists in table
Una columna virtual (virtual column, Oracle 11g) no ocupa espacio: su valor se calcula a partir de otras columnas cada vez que se consulta. Es ideal para datos derivados, como el importe de una línea de pedido:
ALTER TABLE lineas_pedido ADD importe NUMBER(12,2)
GENERATED ALWAYS AS (ROUND(cantidad * precio_unitario * (1 - descuento / 100), 2)) VIRTUAL;
SELECT pedido_id, num_linea, cantidad, precio_unitario, descuento, importe
FROM lineas_pedido
WHERE pedido_id = 5002;
Tabla modificada.
PEDIDO_ID NUM_LINEA CANTIDAD PRECIO_UNITARIO DESCUENTO IMPORTE
---------- ---------- ---------- --------------- ---------- ----------
5002 1 10 649 10 5841
5002 2 10 139 10 1251
5002 3 10 34,5 10 310,5
3 filas seleccionadas.
💡 Consejo: Las columnas virtuales implementan los atributos derivados del modelo E/R (módulo 05) sin el riesgo de que el dato guardado y el calculado diverjan. No se pueden insertar ni actualizar directamente.
Podemos comprobar en el diccionario los valores por defecto y la nulabilidad de las columnas añadidas:
SELECT column_name, data_default, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLEADOS' AND column_id > 11
ORDER BY column_id;
COLUMN_NAME DATA_DEFAULT N
---------------- ------------ -
FECHA_NACIMIENTO (nulo) Y
ACTIVO 'S' N
NIVEL 1 Y
TURNO 'MAÑANA' N
EXTENSION (nulo) Y
5 filas seleccionadas.
Observa que TURNO aparece como N (no admite nulos) aunque no escribimos NOT NULL: es el efecto de DEFAULT ON NULL.
📘 Concepto: Oracle también admite columnas invisibles (12c):
ALTER TABLE t MODIFY columna INVISIBLE;. Una columna invisible no aparece enSELECT *ni enDESC, pero se puede consultar nombrándola. Se usan para introducir columnas nuevas sin romper aplicaciones que hacenSELECT *oINSERTsin lista de columnas.
4. Modificar columnas: MODIFY
MODIFY cambia la definición de una columna existente: tipo, tamaño, nulabilidad o valor por defecto.
ALTER TABLE tabla MODIFY columna [nuevo_tipo] [DEFAULT expr] [NULL | NOT NULL];
ALTER TABLE tabla MODIFY (
columna1 ...,
columna2 ...
);
La regla de oro es sencilla: Oracle permite cualquier cambio que no pueda provocar pérdida de información. Si el cambio podría truncar o reinterpretar datos existentes, se rechaza.
4.1. Ampliar el tamaño: siempre se permite
ALTER TABLE empleados MODIFY telefono VARCHAR2(20);
Tabla modificada.
Ampliar una columna VARCHAR2, CHAR, RAW o la precisión de un NUMBER siempre es posible, tenga o no datos, y es una operación instantánea.
4.2. Reducir el tamaño: solo si los datos caben
Para reducir una columna de texto, ningún valor existente puede superar la nueva longitud. Antes de intentarlo, mide los datos:
SELECT MAX(LENGTH(apellidos)) AS max_caracteres,
MAX(LENGTHB(apellidos)) AS max_bytes
FROM empleados;
MAX_CARACTERES MAX_BYTES
-------------- ----------
16 17
1 fila seleccionada.
¡Curioso! El apellido más largo tiene 16 caracteres pero ocupa 17 bytes. Intentemos reducir la columna a 16:
ALTER TABLE empleados MODIFY apellidos VARCHAR2(16);
ALTER TABLE empleados MODIFY apellidos VARCHAR2(16)
*
ERROR en línea 1:
ORA-01441: cannot decrease column length because some value is too big
📘 Concepto: Semántica de longitud (length semantics). En Oracle,
VARCHAR2(16)significa por defecto 16 bytes (NLS_LENGTH_SEMANTICS = BYTE), no 16 caracteres. Con el juego de caracteres AL32UTF8 (el habitual hoy), las letras con tilde o la ñ ocupan 2 bytes. Si quieres medir en caracteres, escríbelo explícitamente:VARCHAR2(16 CHAR).
ALTER TABLE empleados MODIFY apellidos VARCHAR2(16 CHAR);
SELECT apellidos, LENGTH(apellidos) AS caracteres, LENGTHB(apellidos) AS bytes
FROM empleados
WHERE emp_id = 104;
Tabla modificada.
APELLIDOS CARACTERES BYTES
------------------ ---------- ----------
Fernández Moreno 16 17
1 fila seleccionada.
Ahora sí cabe: “Fernández Moreno” tiene 16 caracteres (17 bytes por la “á”). Devolvemos la columna a su definición original:
ALTER TABLE empleados MODIFY apellidos VARCHAR2(50 BYTE);
Tabla modificada.
4.3. Reducir la precisión o la escala de un NUMBER
Con los números Oracle es más estricto: no permite reducir la precisión ni la escala si la columna tiene algún valor, aunque todos los valores cupieran:
ALTER TABLE empleados MODIFY salario NUMBER(6,2); -- 8,2 -> 6,2: reducir precisión
ALTER TABLE empleados MODIFY salario NUMBER(10,2); -- ampliar: correcto
ALTER TABLE empleados MODIFY salario NUMBER(10,1); -- reducir escala
ALTER TABLE empleados MODIFY salario NUMBER(6,2)
*
ERROR en línea 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
Tabla modificada.
ALTER TABLE empleados MODIFY salario NUMBER(10,1)
*
ERROR en línea 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
El mayor salario es 6500, que cabría en NUMBER(6,2) (hasta 9999,99), pero Oracle no hace esa comprobación: la columna debe estar vacía (todos sus valores a NULL).
4.4. Cambiar el tipo de datos
Cambiar el tipo de una columna con datos tampoco se permite, salvo entre tipos de la misma familia (CHAR ↔ VARCHAR2):
ALTER TABLE empleados MODIFY telefono NUMBER(15); -- texto -> número, con datos
ALTER TABLE empleados MODIFY fecha_nacimiento TIMESTAMP; -- columna vacía (todo NULL)
ALTER TABLE empleados MODIFY fecha_nacimiento DATE;
ALTER TABLE empleados MODIFY telefono CHAR(20); -- VARCHAR2 -> CHAR, con datos
ALTER TABLE empleados MODIFY telefono VARCHAR2(20);
ALTER TABLE empleados MODIFY comision VARCHAR2(10); -- tiene una CHECK asociada
ALTER TABLE empleados MODIFY telefono NUMBER(15)
*
ERROR en línea 1:
ORA-01439: column to be modified must be empty to change datatype
Tabla modificada.
Tabla modificada.
Tabla modificada.
Tabla modificada.
ALTER TABLE empleados MODIFY comision VARCHAR2(10)
*
ERROR en línea 1:
ORA-01463: cannot modify column datatype with current constraint(s)
Observa los dos matices:
fecha_nacimientoacaba de añadirse y todos sus valores sonNULL: para Oracle está “vacía” y admite cualquier cambio de tipo.- En
comisionel error no esORA-01439sinoORA-01463: antes de mirar los datos, Oracle comprueba que hay una restricción (ck_emp_comision CHECK (comision >= 0)) cuya condición depende del tipo de la columna.
💡 Consejo: Para cambiar el tipo de una columna que tiene datos se usa la técnica de la columna puente: (1) añadir una columna nueva con el tipo deseado, (2) copiar los datos convertidos con
UPDATE, (3) borrar la columna antigua y (4) renombrar la nueva. La verás completa en el ejercicio resuelto 2.
4.5. Cambiar la nulabilidad: NULL y NOT NULL
Hacer obligatoria una columna exige que no tenga ningún nulo:
ALTER TABLE empleados MODIFY telefono NOT NULL;
ALTER TABLE empleados MODIFY telefono CONSTRAINT nn_emp_telefono NOT NULL;
ALTER TABLE empleados MODIFY telefono NOT NULL
*
ERROR en línea 1:
ORA-02296: cannot enable (CURSO.) - null values found
ALTER TABLE empleados MODIFY telefono CONSTRAINT nn_emp_telefono NOT NULL
*
ERROR en línea 1:
ORA-02296: cannot enable (CURSO.NN_EMP_TELEFONO) - null values found
Los empleados 123, 131 y 160 no tienen teléfono. En el primer mensaje el nombre de la restricción aparece vacío (CURSO.) porque no le dimos nombre; en el segundo, sí. CURSO es el usuario propietario del esquema en el que hemos probado los ejemplos: tú verás el tuyo.
⚠️ Importante: En Oracle,
NOT NULLes una restricción (de tipoCenUSER_CONSTRAINTS). Por eso se puede nombrar (CONSTRAINT nn_...), y por eso no se añade conADD CONSTRAINT ... NOT NULLsino conMODIFY. Si intentas la formaADD CONSTRAINT, obtienes un error de sintaxis:
ALTER TABLE empleados ADD CONSTRAINT nn_emp_dept NOT NULL (dept_id);
ALTER TABLE empleados ADD CONSTRAINT nn_emp_dept NOT NULL (dept_id)
*
ERROR en línea 1:
ORA-03050: invalid identifier: "NOT" is a reserved word
Oracle también protesta si pides algo que ya es así:
ALTER TABLE empleados MODIFY fecha_nacimiento NULL; -- ya admite nulos
ALTER TABLE empleados MODIFY email NOT NULL; -- ya es NOT NULL
ALTER TABLE empleados MODIFY fecha_nacimiento NULL
*
ERROR en línea 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL
ALTER TABLE empleados MODIFY email NOT NULL
*
ERROR en línea 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL
Una columna con datos y sin nulos puede pasar a NOT NULL sin problema, y quitar el NOT NULL siempre es posible:
ALTER TABLE departamentos MODIFY localidad CONSTRAINT nn_dep_localidad NOT NULL;
ALTER TABLE departamentos MODIFY localidad NULL;
Tabla modificada.
Tabla modificada.
⚠️ Importante:
MODIFY columna NULLborra la restricciónNOT NULL(la de nombrenn_...desaparece deUSER_CONSTRAINTS). Si luego vuelves a ponerNOT NULLsin nombre, Oracle crea una restricción nueva con nombre de sistema (SYS_Cnnnnnn). Pon siempre nombre a tus restricciones.
4.6. Cambiar el valor por defecto
ALTER TABLE empleados MODIFY fecha_alta DEFAULT SYSDATE;
ALTER TABLE empleados MODIFY nivel DEFAULT 2;
SELECT emp_id, nivel FROM empleados WHERE emp_id <= 101;
Tabla modificada.
Tabla modificada.
EMP_ID NIVEL
---------- ----------
100 1
101 1
2 filas seleccionadas.
Cambiar el DEFAULT de una columna existente no toca las filas que ya existen (siguen con nivel 1): solo afecta a los INSERT futuros que omitan la columna. Para quitar un valor por defecto se escribe DEFAULT NULL. Se pueden modificar varias columnas a la vez:
ALTER TABLE empleados MODIFY nivel DEFAULT NULL;
ALTER TABLE empleados MODIFY (
telefono VARCHAR2(25),
oficio VARCHAR2(40)
);
Tabla modificada.
Tabla modificada.
4.7. Resumen: ¿qué permite MODIFY?
| Cambio | Columna vacía (todo NULL) | Columna con datos | Error si no se puede |
|---|---|---|---|
Ampliar longitud de VARCHAR2/CHAR | Sí | Sí | — |
Reducir longitud de VARCHAR2/CHAR | Sí | Solo si ningún valor supera la nueva longitud (¡en bytes o caracteres!) | ORA-01441 |
Ampliar precisión de NUMBER | Sí | Sí | — |
Reducir precisión o escala de NUMBER | Sí | No | ORA-01440 |
| Cambiar de tipo (texto ↔ número ↔ fecha) | Sí | No | ORA-01439 |
CHAR ↔ VARCHAR2 | Sí | Sí, si los datos caben | ORA-01441 |
| Cambiar tipo con restricciones que dependen de él | Depende | No | ORA-01463 |
NULL → NOT NULL | No (hay nulos) | Solo si no hay nulos | ORA-02296 |
NOT NULL → NULL | Sí | Sí | ORA-01451 si ya era NULL |
Cambiar DEFAULT | Sí | Sí (solo afecta a filas futuras) | — |
5. Eliminar columnas: DROP COLUMN y SET UNUSED
5.1. DROP COLUMN
ALTER TABLE tabla DROP COLUMN columna [CASCADE CONSTRAINTS];
ALTER TABLE tabla DROP (columna1, columna2, ...) [CASCADE CONSTRAINTS];
ALTER TABLE departamentos ADD (telefono VARCHAR2(15), email VARCHAR2(60));
ALTER TABLE departamentos DROP COLUMN telefono;
ALTER TABLE departamentos DROP (email);
Tabla modificada.
Tabla modificada.
Tabla modificada.
⚠️ Importante: Borrar una columna es irreversible: los datos desaparecen y no pasan por la papelera de reciclaje. Además,
DROP COLUMNrecorre la tabla entera para eliminar físicamente el valor de cada fila, lo que en tablas grandes puede tardar mucho y generar mucho redo.
Oracle protege la integridad del esquema impidiendo algunos borrados:
-- 1) Columna referenciada por claves ajenas de otras tablas
ALTER TABLE departamentos DROP COLUMN dept_id;
-- 2) Columna que forma parte de una restricción de varias columnas (PK compuesta)
ALTER TABLE asignaciones DROP COLUMN proyecto_id;
-- 3) La única columna de una tabla
CREATE TABLE notas_demo (texto VARCHAR2(100));
ALTER TABLE notas_demo DROP COLUMN texto;
DROP TABLE notas_demo PURGE;
ALTER TABLE departamentos DROP COLUMN dept_id
*
ERROR en línea 1:
ORA-12992: cannot drop parent key column
ALTER TABLE asignaciones DROP COLUMN proyecto_id
*
ERROR en línea 1:
ORA-12991: column is referenced in a multi-column constraint
Tabla creada.
ALTER TABLE notas_demo DROP COLUMN texto
*
ERROR en línea 1:
ORA-12983: cannot drop all columns in a table
Tabla borrada.
Con CASCADE CONSTRAINTS se eliminan también las restricciones afectadas (la PK compuesta, las FK que referencian a la columna y las restricciones de varias columnas que la incluyen):
ALTER TABLE asignaciones DROP COLUMN proyecto_id CASCADE CONSTRAINTS;
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'ASIGNACIONES'
ORDER BY 1;
Tabla modificada.
CONSTRAINT_NAME C
---------------------- -
CK_ASI_HORAS C
FK_ASI_EMP R
NN_ASI_FECHA C
3 filas seleccionadas.
pk_asignaciones (que incluía proyecto_id) y fk_asi_proyecto han desaparecido. ¡La tabla se ha quedado sin clave primaria! Es un ejemplo perfecto de por qué CASCADE CONSTRAINTS debe usarse con los ojos bien abiertos.
5.2. SET UNUSED y DROP UNUSED COLUMNS
En una tabla enorme y en horario de producción, DROP COLUMN puede bloquear la tabla demasiado tiempo. Oracle ofrece una alternativa en dos fases:
SET UNUSED: marca la columna como no utilizada. Es instantáneo (solo cambia el diccionario). A partir de ese momento la columna es invisible e inaccesible, como si no existiera, pero sus datos siguen ocupando espacio.DROP UNUSED COLUMNS: más tarde, en una ventana de mantenimiento, se eliminan físicamente todas las columnas marcadas.
ALTER TABLE clientes SET UNUSED COLUMN telefono;
ALTER TABLE clientes SET UNUSED (email, provincia);
DESC clientes
Tabla modificada.
Tabla modificada.
Nombre ¿Nulo? Tipo
--------------------------------------- -------- ----------------------------
CLIENTE_ID NOT NULL NUMBER(6)
NOMBRE NOT NULL VARCHAR2(60)
NIF NOT NULL VARCHAR2(9)
CIUDAD VARCHAR2(40)
FECHA_ALTA NOT NULL DATE
LIMITE_CREDITO NUMBER(10,2)
COMERCIAL_ID NUMBER(6)
La vista USER_UNUSED_COL_TABS indica qué tablas tienen columnas pendientes de borrar:
SELECT * FROM user_unused_col_tabs;
SELECT telefono FROM clientes;
TABLE_NAME COUNT
-------------- ----------
CLIENTES 3
1 fila seleccionada.
SELECT telefono FROM clientes
*
ERROR en línea 1:
ORA-00904: "TELEFONO": invalid identifier
⚠️ Importante:
SET UNUSEDno tiene vuelta atrás: no existe ninguna sentencia para “recuperar” una columna marcada. Desde el punto de vista lógico es un borrado; solo se aplaza el trabajo físico.
Como la columna ya “no existe”, su nombre queda libre y se puede añadir otra columna con el mismo nombre. Internamente, las columnas marcadas siguen ahí con un nombre de sistema y ocultas:
ALTER TABLE clientes ADD telefono VARCHAR2(15);
SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'CLIENTES' AND hidden_column = 'YES';
Tabla modificada.
COLUMN_NAME HID
------------------------------ ---
SYS_C00004_26100322:54:38$ YES
SYS_C00005_26100322:54:38$ YES
SYS_C00007_26100322:54:38$ YES
3 filas seleccionadas.
Por último, el borrado físico:
ALTER TABLE clientes DROP UNUSED COLUMNS;
SELECT * FROM user_unused_col_tabs;
Tabla modificada.
ninguna fila seleccionada
💡 Consejo: En tablas muy grandes puedes añadir
CHECKPOINT n(ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 10000;) para que Oracle haga un punto de control cada n filas y no agote el espacio de undo.
DROP COLUMN | SET UNUSED | DROP UNUSED COLUMNS | |
|---|---|---|---|
| Efecto lógico | La columna desaparece | La columna desaparece | — |
| Efecto físico | Borra los datos de cada fila | Ninguno (los datos siguen ocupando espacio) | Borra los datos de las columnas marcadas |
| Duración | Proporcional al tamaño de la tabla | Instantánea | Proporcional al tamaño de la tabla |
| Reversible | No | No | No |
6. Renombrar columnas: RENAME COLUMN
ALTER TABLE tabla RENAME COLUMN nombre_actual TO nombre_nuevo;
ALTER TABLE clientes RENAME COLUMN limite_credito TO credito_maximo;
SELECT constraint_name, search_condition
FROM user_constraints
WHERE constraint_name = 'CK_CLI_LIMITE';
Tabla modificada.
CONSTRAINT_NAME SEARCH_CONDITION
---------------------- ------------------------------
CK_CLI_LIMITE "CREDITO_MAXIMO">=0
1 fila seleccionada.
Oracle actualiza automáticamente las restricciones, índices y columnas virtuales que usaban la columna: la CHECK ahora se refiere a CREDITO_MAXIMO. Lo que no se actualiza es el código que hay fuera de la tabla: las vistas que nombraban la columna antigua quedan inválidas (módulo 26), y las aplicaciones y consultas guardadas fallarán con ORA-00904.
El nuevo nombre no puede coincidir con otra columna de la tabla:
ALTER TABLE clientes RENAME COLUMN nombre TO nif;
ALTER TABLE clientes RENAME COLUMN nombre TO nif
*
ERROR en línea 1:
ORA-00957: duplicate column name
⚠️ Importante: Renombrar es barato para la base de datos y caro para las aplicaciones. En un sistema en producción, antes de renombrar busca todas las referencias a la columna (código de la aplicación, vistas, informes, procedimientos).
7. Gestión de restricciones con ALTER TABLE
7.1. Añadir restricciones: ADD CONSTRAINT
Cualquier restricción de tabla (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) se puede añadir después de crear la tabla. Es, de hecho, la forma habitual en los scripts generados por herramientas de modelado: primero se crean todas las tablas y después todas las claves ajenas, para no depender del orden de creación.
ALTER TABLE clientes ADD CONSTRAINT uq_cli_email UNIQUE (email);
ALTER TABLE empleados ADD CONSTRAINT ck_emp_salario_max CHECK (salario <= 10000);
Tabla modificada.
Tabla modificada.
Por defecto, al añadir una restricción Oracle comprueba todas las filas existentes. Si alguna la incumple, la restricción no se crea:
ALTER TABLE empleados ADD CONSTRAINT ck_emp_salario_min CHECK (salario >= 1000);
ALTER TABLE clientes ADD CONSTRAINT uq_cli_ciudad UNIQUE (ciudad);
ALTER TABLE pedidos ADD CONSTRAINT fk_ped_empleado2
FOREIGN KEY (cliente_id) REFERENCES empleados (emp_id);
ALTER TABLE empleados ADD CONSTRAINT ck_emp_salario_min CHECK (salario >= 1000)
*
ERROR en línea 1:
ORA-02293: cannot validate (CURSO.CK_EMP_SALARIO_MIN) - check constraint violated
ALTER TABLE clientes ADD CONSTRAINT uq_cli_ciudad UNIQUE (ciudad)
*
ERROR en línea 1:
ORA-02299: cannot validate (CURSO.UQ_CLI_CIUDAD) - duplicate keys found
ALTER TABLE pedidos ADD CONSTRAINT fk_ped_empleado2 FOREIGN KEY (cliente_id) REFERENCES empleados (emp_id)
*
ERROR en línea 1:
ORA-02298: cannot validate (CURSO.FK_PED_EMPLEADO2) - parent keys not found
- El becario 160 cobra 900 €, menos de 1000.
- Hay varias ciudades repetidas (Madrid, Valencia, Sevilla).
- La FK es absurda a propósito (un cliente no es un empleado): ningún
cliente_idexiste comoemp_id.
| Error | Restricción | Significado |
|---|---|---|
ORA-02293 | CHECK | Alguna fila no cumple la condición |
ORA-02296 | NOT NULL | Hay valores nulos |
ORA-02299 | UNIQUE | Hay valores duplicados |
ORA-02437 | PRIMARY KEY | Hay duplicados o nulos en la clave |
ORA-02298 | FOREIGN KEY | Hay valores sin fila padre (“huérfanos”) |
7.2. Renombrar y eliminar restricciones
ALTER TABLE empleados RENAME CONSTRAINT ck_emp_salario_max TO ck_emp_salario_tope;
ALTER TABLE clientes DROP UNIQUE (email); -- por columnas, sin saber su nombre
SELECT constraint_name FROM user_constraints WHERE constraint_name = 'UQ_CLI_EMAIL';
ALTER TABLE clientes DROP CONSTRAINT uq_cli_email;
Tabla modificada.
Tabla modificada.
ninguna fila seleccionada
ALTER TABLE clientes DROP CONSTRAINT uq_cli_email
*
ERROR en línea 1:
ORA-02443: Cannot drop constraint - nonexistent constraint
Las formas de eliminar una restricción son:
| Sintaxis | Uso |
|---|---|
DROP CONSTRAINT nombre | Cualquier restricción, por nombre (la forma recomendada) |
DROP PRIMARY KEY | La clave primaria de la tabla (solo hay una) |
DROP UNIQUE (col1, col2...) | La restricción UNIQUE sobre esas columnas |
... CASCADE | Borra también las FK de otras tablas que dependen de esa PK/UNIQUE |
... KEEP INDEX / DROP INDEX | Conserva o elimina el índice asociado a la PK/UNIQUE |
Una clave primaria referenciada por claves ajenas no se puede borrar ni deshabilitar sin más:
ALTER TABLE departamentos DROP PRIMARY KEY;
ALTER TABLE departamentos DISABLE CONSTRAINT pk_departamentos;
ALTER TABLE departamentos DROP PRIMARY KEY
*
ERROR en línea 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
ALTER TABLE departamentos DISABLE CONSTRAINT pk_departamentos
*
ERROR en línea 1:
ORA-02297: cannot disable constraint (CURSO.PK_DEPARTAMENTOS) - dependencies exist
7.3. Estados de una restricción: ENABLE/DISABLE y VALIDATE/NOVALIDATE
Una restricción tiene dos “interruptores” independientes:
- ENABLE / DISABLE: ¿se comprueba en las operaciones futuras (
INSERT,UPDATE)? - VALIDATE / NOVALIDATE: ¿se garantiza que los datos existentes la cumplen?
| Estado | Datos nuevos | Datos existentes | Uso típico |
|---|---|---|---|
ENABLE VALIDATE (por defecto al habilitar) | Se comprueban | Se comprueban todos | Situación normal |
ENABLE NOVALIDATE | Se comprueban | No se comprueban (puede haber filas incorrectas antiguas) | Aplicar una regla nueva “de aquí en adelante” |
DISABLE NOVALIDATE (por defecto al deshabilitar) | No se comprueban | No se comprueban | Cargas masivas de datos |
DISABLE VALIDATE | Se prohíbe modificar la tabla | Se garantizan | Casos especiales de almacenes de datos |
💻 Ejemplo: Deshabilitamos el tope de salario, subimos el sueldo de la directora general por encima del tope y tratamos de volver a habilitarlo.
ALTER TABLE empleados DISABLE CONSTRAINT ck_emp_salario_tope;
UPDATE empleados SET salario = 12000 WHERE emp_id = 100;
COMMIT;
ALTER TABLE empleados ENABLE CONSTRAINT ck_emp_salario_tope;
Tabla modificada.
1 fila actualizada.
Confirmación terminada.
ALTER TABLE empleados ENABLE CONSTRAINT ck_emp_salario_tope
*
ERROR en línea 1:
ORA-02293: cannot validate (CURSO.CK_EMP_SALARIO_TOPE) - check constraint violated
ENABLE equivale a ENABLE VALIDATE y la fila de Ana lo impide. Con NOVALIDATE la regla vuelve a aplicarse a los datos nuevos, pero se toleran los antiguos:
ALTER TABLE empleados ENABLE NOVALIDATE CONSTRAINT ck_emp_salario_tope;
SELECT constraint_name, status, validated
FROM user_constraints
WHERE constraint_name = 'CK_EMP_SALARIO_TOPE';
UPDATE empleados SET salario = 11000 WHERE emp_id = 101;
Tabla modificada.
CONSTRAINT_NAME STATUS VALIDATED
---------------------- -------- -------------
CK_EMP_SALARIO_TOPE ENABLED NOT VALIDATED
1 fila seleccionada.
UPDATE empleados SET salario = 11000 WHERE emp_id = 101
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_EMP_SALARIO_TOPE) violated
Una vez corregidos los datos antiguos, validamos y podemos ver cómo cambia el estado con MODIFY CONSTRAINT, que permite cambiar el estado sin repetir la definición:
UPDATE empleados SET salario = 6400 WHERE emp_id = 100;
COMMIT;
ALTER TABLE empleados MODIFY CONSTRAINT ck_emp_salario_tope VALIDATE;
SELECT constraint_name, status, validated FROM user_constraints
WHERE constraint_name = 'CK_EMP_SALARIO_TOPE';
ALTER TABLE empleados MODIFY CONSTRAINT ck_emp_salario_tope DISABLE;
SELECT constraint_name, status, validated FROM user_constraints
WHERE constraint_name = 'CK_EMP_SALARIO_TOPE';
1 fila actualizada.
Confirmación terminada.
Tabla modificada.
CONSTRAINT_NAME STATUS VALIDATED
---------------------- -------- -------------
CK_EMP_SALARIO_TOPE ENABLED VALIDATED
1 fila seleccionada.
Tabla modificada.
CONSTRAINT_NAME STATUS VALIDATED
---------------------- -------- -------------
CK_EMP_SALARIO_TOPE DISABLED NOT VALIDATED
1 fila seleccionada.
📘 Concepto:
ALTER TABLE t ENABLE CONSTRAINT cyALTER TABLE t MODIFY CONSTRAINT c ENABLEson equivalentes.MODIFY CONSTRAINTadmite además otros atributos del estado de la restricción, comoRELY/NORELYo, en las diferibles,INITIALLY DEFERRED/INITIALLY IMMEDIATE(módulo 24).
7.4. Claves ajenas deshabilitadas y datos huérfanos
Las claves ajenas se deshabilitan a menudo para acelerar cargas masivas de datos. El riesgo es que, mientras están deshabilitadas, entren filas huérfanas:
ALTER TABLE empleados DISABLE CONSTRAINT fk_emp_dept;
UPDATE empleados SET dept_id = 99 WHERE emp_id = 160; -- el departamento 99 no existe
COMMIT;
ALTER TABLE empleados ENABLE CONSTRAINT fk_emp_dept;
Tabla modificada.
1 fila actualizada.
Confirmación terminada.
ALTER TABLE empleados ENABLE CONSTRAINT fk_emp_dept
*
ERROR en línea 1:
ORA-02298: cannot validate (CURSO.FK_EMP_DEPT) - parent keys not found
Tras corregir el dato, la clave ajena se habilita sin problemas:
UPDATE empleados SET dept_id = NULL WHERE emp_id = 160;
COMMIT;
ALTER TABLE empleados ENABLE CONSTRAINT fk_emp_dept;
1 fila actualizada.
Confirmación terminada.
Tabla modificada.
7.5. Localizar las filas que incumplen: EXCEPTIONS INTO
Cuando hay miles de filas, encontrar a mano las culpables es tedioso. La cláusula EXCEPTIONS INTO guarda el ROWID de cada fila que incumple la restricción en una tabla de excepciones con esta estructura (Oracle la proporciona en el script utlexcpt.sql, pero puedes crearla tú mismo):
CREATE TABLE excepciones (
row_id ROWID,
owner VARCHAR2(128),
table_name VARCHAR2(128),
constraint VARCHAR2(128)
);
ALTER TABLE productos DISABLE CONSTRAINT ck_pro_stock;
UPDATE productos SET stock = -1 WHERE producto_id IN (8, 14);
COMMIT;
ALTER TABLE productos ENABLE CONSTRAINT ck_pro_stock EXCEPTIONS INTO excepciones;
Tabla creada.
Tabla modificada.
2 filas actualizadas.
Confirmación terminada.
ALTER TABLE productos ENABLE CONSTRAINT ck_pro_stock EXCEPTIONS INTO excepciones
*
ERROR en línea 1:
ORA-02293: cannot validate (CURSO.CK_PRO_STOCK) - check constraint violated
La restricción sigue deshabilitada, pero la tabla excepciones nos dice qué filas hay que arreglar:
SELECT e.constraint, p.producto_id, p.nombre, p.stock
FROM excepciones e
JOIN productos p ON p.ROWID = e.row_id;
CONSTRAINT PRODUCTO_ID NOMBRE STOCK
-------------- ----------- ---------------------- ----------
CK_PRO_STOCK 8 Pack teclado y ratón -1
CK_PRO_STOCK 14 Altavoz Inteligente -1
2 filas seleccionadas.
UPDATE productos SET stock = 0 WHERE ROWID IN (SELECT row_id FROM excepciones);
COMMIT;
ALTER TABLE productos ENABLE CONSTRAINT ck_pro_stock;
DROP TABLE excepciones PURGE;
2 filas actualizadas.
Confirmación terminada.
Tabla modificada.
Tabla borrada.
📘 Concepto: El ROWID es la dirección física de una fila (fichero, bloque y posición dentro del bloque). Es la forma más rápida de localizar una fila concreta y Oracle lo usa internamente en los índices (módulo 26).
7.6. DISABLE … CASCADE y DROP PRIMARY KEY CASCADE
Para no estropear EMPRESA, trabajamos con copias de departamentos y empleados:
CREATE TABLE dep_copia AS SELECT dept_id, nombre FROM departamentos;
ALTER TABLE dep_copia ADD CONSTRAINT pk_dep_copia PRIMARY KEY (dept_id);
CREATE TABLE emp_copia AS SELECT emp_id, nombre, dept_id FROM empleados;
ALTER TABLE emp_copia ADD CONSTRAINT fk_empc_dep
FOREIGN KEY (dept_id) REFERENCES dep_copia (dept_id);
DISABLE ... CASCADE deshabilita la PK y todas las FK que dependen de ella:
ALTER TABLE dep_copia DISABLE CONSTRAINT pk_dep_copia CASCADE;
SELECT constraint_name, table_name, constraint_type, status
FROM user_constraints
WHERE table_name IN ('DEP_COPIA', 'EMP_COPIA') AND constraint_type IN ('P', 'R')
ORDER BY 1;
SELECT index_name FROM user_indexes WHERE table_name = 'DEP_COPIA';
Tabla modificada.
CONSTRAINT_NAME TABLE_NAME C STATUS
---------------------- -------------- - --------
FK_EMPC_DEP EMP_COPIA R DISABLED
PK_DEP_COPIA DEP_COPIA P DISABLED
2 filas seleccionadas.
ninguna fila seleccionada
Fíjate en el segundo resultado: al deshabilitar la PK, Oracle ha eliminado su índice (lo había creado automáticamente al crear la PK). Al habilitarla de nuevo lo reconstruirá. Lo que no hace Oracle es rehabilitar las FK en cascada: hay que hacerlo una a una.
ALTER TABLE dep_copia ENABLE CONSTRAINT pk_dep_copia;
ALTER TABLE emp_copia ENABLE CONSTRAINT fk_empc_dep;
Tabla modificada.
Tabla modificada.
Del mismo modo, DROP PRIMARY KEY CASCADE elimina la PK y las FK que la referencian:
ALTER TABLE dep_copia DROP PRIMARY KEY CASCADE;
SELECT constraint_name, table_name, constraint_type, status
FROM user_constraints
WHERE table_name IN ('DEP_COPIA', 'EMP_COPIA') AND constraint_type IN ('P', 'R')
ORDER BY 1;
Tabla modificada.
ninguna fila seleccionada
Por último, KEEP INDEX permite borrar la restricción conservando su índice:
ALTER TABLE dep_copia ADD CONSTRAINT pk_dep_copia PRIMARY KEY (dept_id);
ALTER TABLE dep_copia DROP CONSTRAINT pk_dep_copia KEEP INDEX;
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'DEP_COPIA';
DROP TABLE emp_copia PURGE;
DROP TABLE dep_copia PURGE;
Tabla modificada.
Tabla modificada.
INDEX_NAME UNIQUENES
---------------- ---------
PK_DEP_COPIA UNIQUE
1 fila seleccionada.
Tabla borrada.
Tabla borrada.
8. Tablas de solo lectura: READ ONLY y READ WRITE
Desde Oracle 11g una tabla puede ponerse en modo de solo lectura. Es útil para proteger datos históricos (los pedidos de un ejercicio cerrado), tablas de referencia o durante una migración.
ALTER TABLE categorias READ ONLY;
SELECT table_name, read_only FROM user_tables WHERE table_name = 'CATEGORIAS';
INSERT INTO categorias VALUES (12, 'Gaming', 1);
UPDATE categorias SET nombre = 'Informática y redes' WHERE categoria_id = 1;
DELETE FROM categorias WHERE categoria_id = 11;
TRUNCATE TABLE categorias;
Tabla modificada.
TABLE_NAME REA
---------------- ---
CATEGORIAS YES
1 fila seleccionada.
INSERT INTO categorias VALUES (12, 'Gaming', 1)
*
ERROR en línea 1:
ORA-12081: update operation not allowed on table "CURSO"."CATEGORIAS"
UPDATE categorias SET nombre = 'Informática y redes' WHERE categoria_id = 1
*
ERROR en línea 1:
ORA-12081: update operation not allowed on table "CURSO"."CATEGORIAS"
DELETE FROM categorias WHERE categoria_id = 11
*
ERROR en línea 1:
ORA-12081: update operation not allowed on table "CURSO"."CATEGORIAS"
TRUNCATE TABLE categorias
*
ERROR en línea 1:
ORA-12081: update operation not allowed on table "CURSO"."CATEGORIAS"
Las operaciones que no cambian los datos sí están permitidas: SELECT, crear y borrar índices, añadir o quitar restricciones e incluso DROP TABLE (la tabla de solo lectura protege los datos, no la existencia de la tabla).
SELECT COUNT(*) FROM categorias;
CREATE INDEX ix_cat_padre ON categorias (categoria_padre_id);
DROP INDEX ix_cat_padre;
ALTER TABLE categorias READ WRITE;
COUNT(*)
----------
11
1 fila seleccionada.
Índice creado.
Índice borrado.
Tabla modificada.
⚠️ Importante: En nuestras pruebas con Oracle 26ai, sobre una tabla
READ ONLYse permitióADDyMODIFYde columnas, mientras queRENAME COLUMN,SET UNUSEDyDROP COLUMNfallaron conORA-12081. La documentación de versiones anteriores incluye tambiénADD/MODIFYentre las operaciones prohibidas, así que no confíes enREAD ONLYpara impedir cambios de estructura: para eso están los privilegios (módulo 37).
9. Reorganizar una tabla: ALTER TABLE … MOVE
ALTER TABLE ... MOVE reconstruye físicamente la tabla en un segmento nuevo (opcionalmente en otro tablespace o con otra compresión). Se usa, sobre todo, para recuperar espacio después de borrar muchas filas:
-- Tabla de 50.000 filas de la que se borra el 90 %
SELECT segment_name, blocks FROM user_segments WHERE segment_name = 'VENTAS_DEMO';
ALTER TABLE ventas_demo MOVE;
SELECT segment_name, blocks FROM user_segments WHERE segment_name = 'VENTAS_DEMO';
SELECT index_name, status FROM user_indexes WHERE table_name = 'VENTAS_DEMO';
SEGMENT_NAME BLOCKS
-------------------- ----------
VENTAS_DEMO 896
1 fila seleccionada.
Tabla modificada.
SEGMENT_NAME BLOCKS
-------------------- ----------
VENTAS_DEMO 96
1 fila seleccionada.
INDEX_NAME STATUS
-------------------- --------
PK_VENTAS_DEMO UNUSABLE
1 fila seleccionada.
La tabla pasa de 896 a 96 bloques, pero hay un efecto secundario: al cambiar de sitio las filas, cambian sus ROWID, y los índices quedan inutilizables (UNUSABLE) hasta que se reconstruyen con ALTER INDEX ... REBUILD. Desde 12.2 existe ALTER TABLE ... MOVE ONLINE, que permite seguir trabajando con la tabla durante el movimiento y mantiene los índices válidos:
ALTER INDEX pk_ventas_demo REBUILD;
ALTER TABLE ventas_demo MOVE ONLINE;
SELECT index_name, status FROM user_indexes WHERE table_name = 'VENTAS_DEMO';
Índice modificado.
Tabla modificada.
INDEX_NAME STATUS
-------------------- --------
PK_VENTAS_DEMO VALID
1 fila seleccionada.
💡 Consejo:
MOVEes una tarea de administración física (se estudia a fondo en los módulos de administración de ASIR). Para este curso basta con saber que existe y que invalida los índices si no se usaONLINEoUPDATE INDEXES. La tablaventas_demose construye en la sección 13.
10. Renombrar tablas: RENAME y ALTER TABLE … RENAME TO
Hay dos formas de cambiar el nombre de una tabla:
CREATE TABLE copia_clientes AS SELECT * FROM clientes;
RENAME copia_clientes TO clientes_2025; -- sentencia RENAME
ALTER TABLE clientes_2025 RENAME TO clientes_hist; -- cláusula de ALTER TABLE
SELECT table_name FROM user_tables WHERE table_name LIKE 'CLIENTES%';
Tabla creada.
Tabla cambiada de nombre.
Tabla modificada.
TABLE_NAME
----------------
CLIENTES
CLIENTES_HIST
2 filas seleccionadas.
RENAME a TO b | ALTER TABLE a RENAME TO b | |
|---|---|---|
| Objetos | Tablas, vistas, secuencias y sinónimos privados | Solo tablas |
| Esquema | Solo objetos de tu propio esquema (no admite esquema.objeto) | Admite esquema.tabla (con privilegios) |
| Efecto | Idéntico: cambia el nombre | Idéntico |
Al renombrar una tabla, Oracle traslada automáticamente sus índices, restricciones, privilegios concedidos y las claves ajenas que la referencian. Las vistas y el código que usaban el nombre antiguo quedan inválidos.
11. Borrar tablas: DROP TABLE
DROP TABLE [esquema.]tabla [CASCADE CONSTRAINTS] [PURGE];
DROP TABLE elimina la tabla completa: su definición, sus datos, sus índices, sus restricciones y sus privilegios. Es la operación más destructiva del DDL.
11.1. Tablas referenciadas por claves ajenas
DROP TABLE departamentos;
DROP TABLE departamentos
*
ERROR en línea 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
Oracle se niega porque empleados y proyectos tienen claves ajenas hacia departamentos. Las opciones son borrar antes las tablas hijas (por eso empresa_borrar.sql borra en orden inverso a las dependencias) o usar CASCADE CONSTRAINTS, que elimina las FK de las otras tablas (¡no sus filas!):
CREATE TABLE dep_copia AS SELECT * FROM departamentos;
ALTER TABLE dep_copia ADD CONSTRAINT pk_dep_copia PRIMARY KEY (dept_id);
CREATE TABLE emp_copia AS SELECT emp_id, nombre, dept_id FROM empleados;
ALTER TABLE emp_copia ADD CONSTRAINT fk_empc_dep
FOREIGN KEY (dept_id) REFERENCES dep_copia (dept_id);
DROP TABLE dep_copia;
DROP TABLE dep_copia CASCADE CONSTRAINTS;
SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = 'EMP_COPIA';
DROP TABLE dep_copia
*
ERROR en línea 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
Tabla borrada.
CONSTRAINT_NAME C
---------------------- -
SYS_C009705 C
1 fila seleccionada.
En emp_copia solo queda el NOT NULL de nombre (copiado por CREATE TABLE ... AS SELECT con nombre de sistema); fk_empc_dep ha desaparecido.
11.2. DROP TABLE … PURGE
Sin PURGE, la tabla borrada va a la papelera de reciclaje (sección siguiente) y sigue ocupando espacio. Con PURGE se elimina definitivamente, sin posibilidad de recuperación con FLASHBACK:
DROP TABLE emp_copia PURGE;
Tabla borrada.
⚠️ Importante:
DROP TABLE ... PURGEes irreversible. La única forma de recuperar los datos será una copia de seguridad. Úsalo en scripts de pruebas (comoempresa_borrar.sql), nunca “por costumbre” en producción.
12. La papelera de reciclaje (RECYCLEBIN)
📘 Concepto: La papelera de reciclaje (recycle bin, Oracle 10g) es un diccionario de objetos borrados. Cuando haces
DROP TABLEsinPURGE, Oracle no libera el espacio: renombra la tabla (y sus índices, restricciones y triggers) con un nombre de sistema que empieza porBIN$y la registra en la papelera. Mientras el espacio no se necesite, la tabla puede recuperarse conFLASHBACK TABLE ... TO BEFORE DROP.
12.1. Ver la papelera
Borramos la tabla clientes_hist que creamos en la sección 10:
DROP TABLE clientes_hist;
SHOW RECYCLEBIN
SELECT object_name, original_name, type, droptime, can_undrop
FROM user_recyclebin;
Tabla borrada.
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
CLIENTES_HIST BIN$XPi8byXLElzgYwIAEazanw==$0 TABLE 2026-10-03:23:09:08
OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME CAN
-------------------------------- ---------------- ---------- -------------------- ---
BIN$XPi8byXLElzgYwIAEazanw==$0 CLIENTES_HIST TABLE 2026-10-03:23:09:08 YES
1 fila seleccionada.
SHOW RECYCLEBINes un comando de SQL*Plus/SQLcl/SQL Developer que muestra las tablas de tu papelera.USER_RECYCLEBIN(sinónimo:RECYCLEBIN) es la vista del diccionario, con más detalle: incluye índices y otros objetos. Los administradores disponen deDBA_RECYCLEBINcon los objetos de todos los usuarios.
La tabla borrada ya no existe con su nombre original, pero se puede consultar (no modificar) con su nombre de papelera, entre comillas dobles:
SELECT COUNT(*) FROM clientes_hist;
SELECT cliente_id, nombre FROM "BIN$XPi8byXLElzgYwIAEazanw==$0" WHERE cliente_id <= 1003;
DELETE FROM "BIN$XPi8byXLElzgYwIAEazanw==$0";
SELECT COUNT(*) FROM clientes_hist
*
ERROR en línea 1:
ORA-00942: table or view "CURSO"."CLIENTES_HIST" does not exist
CLIENTE_ID NOMBRE
---------- ------------------------------
1001 Talleres Martín S.L.
1002 Construcciones Levante S.A.
1003 María José Herrera
3 filas seleccionadas.
DELETE FROM "BIN$XPi8byXLElzgYwIAEazanw==$0"
*
ERROR en línea 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
💡 Consejo: Los nombres
BIN$...los genera Oracle y serán distintos en tu base de datos. Cópialos deSHOW RECYCLEBINo deUSER_RECYCLEBIN, y escríbelos siempre entre comillas dobles (contienen caracteres como$,/o=).
12.2. Recuperar una tabla: FLASHBACK TABLE … TO BEFORE DROP
FLASHBACK TABLE clientes_hist TO BEFORE DROP;
SELECT COUNT(*) FROM clientes_hist;
Flashback terminado.
COUNT(*)
----------
14
1 fila seleccionada.
La tabla vuelve con todos sus datos. Pero ¿qué pasa con sus índices y restricciones? Lo comprobamos añadiendo una PK y un índice, borrando y recuperando con otro nombre (RENAME TO):
ALTER TABLE clientes_hist ADD CONSTRAINT pk_clientes_hist PRIMARY KEY (cliente_id);
CREATE INDEX ix_clih_ciudad ON clientes_hist (ciudad);
DROP TABLE clientes_hist;
SELECT object_name, original_name, type FROM user_recyclebin ORDER BY type;
Tabla modificada.
Índice creado.
Tabla borrada.
OBJECT_NAME ORIGINAL_NAME TYPE
-------------------------------- ---------------- ----------
BIN$XPh0ag/WCFfgYwIAEaw0SA==$0 IX_CLIH_CIUDAD INDEX
BIN$XPh0ag/XCFfgYwIAEaw0SA==$0 PK_CLIENTES_HIST INDEX
BIN$XPh0ag/YCFfgYwIAEaw0SA==$0 CLIENTES_HIST TABLE
3 filas seleccionadas.
FLASHBACK TABLE clientes_hist TO BEFORE DROP RENAME TO clientes_recuperados;
SELECT index_name FROM user_indexes WHERE table_name = 'CLIENTES_RECUPERADOS';
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'CLIENTES_RECUPERADOS' AND constraint_type = 'P';
Flashback terminado.
INDEX_NAME
--------------------------------
BIN$XPh0ag/XCFfgYwIAEaw0SA==$0
BIN$XPh0ag/WCFfgYwIAEaw0SA==$0
2 filas seleccionadas.
CONSTRAINT_NAME
--------------------------------
BIN$XPh0ag/VCFfgYwIAEaw0SA==$0
1 fila seleccionada.
⚠️ Importante: Tras un
FLASHBACK TABLE ... TO BEFORE DROP:
- La tabla recupera su nombre original (o el indicado en
RENAME TO).- Los índices, restricciones y triggers se recuperan, pero conservan los nombres
BIN$.... Debes renombrarlos tú conALTER INDEX ... RENAME TOyALTER TABLE ... RENAME CONSTRAINT.- Las claves ajenas que la tabla tenía hacia otras tablas no se recuperan, y tampoco las FK de otras tablas que se eliminaron con
CASCADE CONSTRAINTS. Hay que volver a crearlas (ejercicio resuelto 6).
12.3. Varias versiones de una tabla en la papelera
Si borras varias veces una tabla con el mismo nombre, la papelera guarda todas las versiones. FLASHBACK recupera la más reciente (orden LIFO). Si ya existe una tabla con ese nombre, hay que recuperarla con otro:
CREATE TABLE notas_demo (version NUMBER);
INSERT INTO notas_demo VALUES (1);
DROP TABLE notas_demo;
CREATE TABLE notas_demo (version NUMBER);
INSERT INTO notas_demo VALUES (2);
DROP TABLE notas_demo;
CREATE TABLE notas_demo (version NUMBER);
INSERT INTO notas_demo VALUES (3);
COMMIT;
SELECT object_name, original_name, droptime
FROM user_recyclebin
WHERE original_name = 'NOTAS_DEMO'
ORDER BY droptime;
FLASHBACK TABLE notas_demo TO BEFORE DROP;
FLASHBACK TABLE notas_demo TO BEFORE DROP RENAME TO notas_v2;
SELECT * FROM notas_v2;
OBJECT_NAME ORIGINAL_NAME DROPTIME
-------------------------------- ---------------- --------------------
BIN$XPh/2yYGCezgYwIAEazf3Q==$0 NOTAS_DEMO 2026-10-03:22:52:16
BIN$XPh/2yYHCezgYwIAEazf3Q==$0 NOTAS_DEMO 2026-10-03:22:52:16
2 filas seleccionadas.
FLASHBACK TABLE notas_demo TO BEFORE DROP
*
ERROR en línea 1:
ORA-38312: original name is used by an existing object
Flashback terminado.
VERSION
----------
2
1 fila seleccionada.
Se ha recuperado la versión 2 (la última borrada). La versión 1 sigue en la papelera. Para recuperar una versión concreta, usa su nombre BIN$: FLASHBACK TABLE "BIN$XPh/2yYGCezgYwIAEazf3Q==$0" TO BEFORE DROP RENAME TO notas_v1;.
12.4. Vaciar la papelera: PURGE
| Sentencia | Efecto |
|---|---|
PURGE TABLE nombre | Elimina definitivamente de la papelera la versión más antigua de esa tabla |
PURGE INDEX nombre | Elimina un índice de la papelera |
PURGE RECYCLEBIN | Vacía tu papelera |
PURGE DBA_RECYCLEBIN | Vacía la papelera de todos los usuarios (requiere privilegios de administración) |
PURGE TABLESPACE ts [USER u] | Elimina los objetos de la papelera de un tablespace |
SHOW RECYCLEBIN
PURGE TABLE notas_demo;
SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
NOTAS_DEMO BIN$XPh/2yYGCezgYwIAEazf3Q==$0 TABLE 2026-10-03:22:52:16
Tabla depurada.
El segundo SHOW RECYCLEBIN no muestra nada: la papelera está vacía. Borramos ahora las tablas de prueba sin PURGE y vaciamos la papelera de golpe:
DROP TABLE notas_demo PURGE;
DROP TABLE notas_v2;
DROP TABLE emp_copia;
DROP TABLE dep_copia;
SHOW RECYCLEBIN
PURGE RECYCLEBIN;
SELECT COUNT(*) FROM user_recyclebin;
Tabla borrada.
Tabla borrada.
Tabla borrada.
Tabla borrada.
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
DEP_COPIA BIN$XPh/2yYOCezgYwIAEazf3Q==$0 TABLE 2026-10-03:22:52:16
EMP_COPIA BIN$XPh/2yYKCezgYwIAEazf3Q==$0 TABLE 2026-10-03:22:52:16
NOTAS_V2 BIN$XPh/2yYICezgYwIAEazf3Q==$0 TABLE 2026-10-03:22:52:16
Papelera de reciclaje depurada.
COUNT(*)
----------
0
1 fila seleccionada.
12.5. Cómo funciona por dentro y cómo desactivarla
- Los objetos de la papelera siguen ocupando espacio en su tablespace y cuentan para tu cuota. Cuando el tablespace se queda sin espacio libre, Oracle purga automáticamente los objetos más antiguos de la papelera antes de dar un error de espacio. Por eso la papelera es una red de seguridad, no una copia de seguridad.
- La papelera se controla con el parámetro
RECYCLEBIN(por defectoON), que puede cambiarse para tu sesión:
ALTER SESSION SET recyclebin = OFF;
CREATE TABLE copia_dep AS SELECT dept_id, nombre FROM departamentos;
DROP TABLE copia_dep;
SELECT COUNT(*) FROM user_recyclebin;
ALTER SESSION SET recyclebin = ON;
Sesión modificada.
Tabla creada.
Tabla borrada.
COUNT(*)
----------
0
1 fila seleccionada.
Sesión modificada.
- Las tablas del tablespace
SYSTEMy algunos tipos de objetos no pasan por la papelera. FLASHBACK TABLE ... TO BEFORE DROPes solo una de las tecnologías Flashback de Oracle. Otras (comoFLASHBACK TABLE ... TO TIMESTAMPo las consultasAS OF TIMESTAMP) permiten ver o recuperar datos modificados en el pasado y se basan en la información de undo.
13. TRUNCATE TABLE frente a DELETE
TRUNCATE TABLE [esquema.]tabla [DROP STORAGE | REUSE STORAGE] [CASCADE];
TRUNCATE elimina todas las filas de una tabla. A primera vista hace lo mismo que DELETE FROM tabla; sin WHERE, pero por dentro son operaciones completamente distintas.
13.1. La marca de agua superior (HWM)
📘 Concepto: Una tabla se almacena en un segmento formado por bloques. La marca de agua superior (High Water Mark, HWM) es la frontera entre los bloques que han contenido datos alguna vez y los que nunca se han usado. Las lecturas completas de la tabla (full table scan) recorren todos los bloques hasta la HWM, estén llenos o vacíos.
flowchart LR
subgraph ANTES["Tabla con 200.000 filas"]
direction LR
A1["Bloques llenos"] --> HWM1["HWM"] --> L1["Espacio libre"]
end
subgraph DEL["Tras DELETE"]
direction LR
D1["Bloques VACÍOS<br/>(siguen bajo la HWM)"] --> HWM2["HWM<br/>(no se mueve)"] --> L2["Espacio libre"]
end
subgraph TRU["Tras TRUNCATE"]
direction LR
HWM3["HWM<br/>(vuelve al inicio)"] --> L3["Espacio liberado"]
end
ANTES --> DEL
ANTES --> TRU
13.2. Demostración
Creamos una tabla de 200.000 filas con una consulta jerárquica (la técnica CONNECT BY LEVEL se explica en el módulo 33; aquí solo la usamos para generar filas):
CREATE TABLE ventas_demo AS
SELECT LEVEL AS venta_id,
MOD(LEVEL, 1000) + 1 AS cliente_id,
DATE '2025-01-01' + MOD(LEVEL, 365) AS fecha,
ROUND(DBMS_RANDOM.VALUE(1, 1000), 2) AS importe,
RPAD('x', 100, 'x') AS relleno
FROM dual
CONNECT BY LEVEL <= 200000;
ALTER TABLE ventas_demo ADD CONSTRAINT pk_ventas_demo PRIMARY KEY (venta_id);
SELECT segment_name, blocks, bytes/1024/1024 AS mb
FROM user_segments
WHERE segment_name = 'VENTAS_DEMO';
Tabla creada.
Tabla modificada.
SEGMENT_NAME BLOCKS MB
-------------------- ---------- ----------
VENTAS_DEMO 3712 29
1 fila seleccionada.
💡 Consejo:
DBMS_RANDOM.VALUEes una función de un paquete del sistema que devuelve un número aleatorio. Se puede llamar desde SQL como cualquier función; no estamos escribiendo PL/SQL.
Con DELETE (activamos SET TIMING ON en SQL*Plus para medir el tiempo):
SET TIMING ON
DELETE FROM ventas_demo;
SET TIMING OFF
SELECT segment_name, blocks, bytes/1024/1024 AS mb
FROM user_segments
WHERE segment_name = 'VENTAS_DEMO';
ROLLBACK;
SELECT COUNT(*) FROM ventas_demo;
200000 filas suprimidas.
Transcurrido: 00:00:04.03
SEGMENT_NAME BLOCKS MB
-------------------- ---------- ----------
VENTAS_DEMO 3712 29
1 fila seleccionada.
Rollback terminado.
COUNT(*)
----------
200000
1 fila seleccionada.
El DELETE tarda 4 segundos (borra fila a fila, genera undo y redo para cada una y mantiene el índice), no libera espacio (siguen los 3712 bloques) y se puede deshacer con ROLLBACK.
Con TRUNCATE:
SET TIMING ON
TRUNCATE TABLE ventas_demo;
SET TIMING OFF
SELECT segment_name, blocks, bytes/1024/1024 AS mb
FROM user_segments
WHERE segment_name = 'VENTAS_DEMO';
ROLLBACK;
SELECT COUNT(*) FROM ventas_demo;
Tabla truncada.
Transcurrido: 00:00:00.54
SEGMENT_NAME BLOCKS MB
-------------------- ---------- ----------
VENTAS_DEMO 8 ,0625
1 fila seleccionada.
Rollback terminado.
COUNT(*)
----------
0
1 fila seleccionada.
TRUNCATE no borra filas: reinicia el segmento (mueve la HWM al principio y libera los extents). Tarda una fracción de segundo, deja la tabla en 8 bloques y el ROLLBACK posterior no recupera nada.
Con REUSE STORAGE se vacía la tabla pero se conserva el espacio reservado, útil si se va a volver a llenar enseguida (por ejemplo, una tabla de carga diaria):
-- (antes se vuelven a insertar las 200.000 filas)
TRUNCATE TABLE ventas_demo REUSE STORAGE;
SELECT segment_name, blocks FROM user_segments WHERE segment_name = 'VENTAS_DEMO';
TRUNCATE TABLE ventas_demo DROP STORAGE; -- la opción por defecto
SELECT segment_name, blocks FROM user_segments WHERE segment_name = 'VENTAS_DEMO';
Tabla truncada.
SEGMENT_NAME BLOCKS
-------------------- ----------
VENTAS_DEMO 3584
1 fila seleccionada.
Tabla truncada.
SEGMENT_NAME BLOCKS
-------------------- ----------
VENTAS_DEMO 8
1 fila seleccionada.
13.3. TRUNCATE y las claves ajenas
TRUNCATE TABLE departamentos;
TRUNCATE TABLE pedidos;
TRUNCATE TABLE departamentos
*
ERROR en línea 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
TRUNCATE TABLE pedidos
*
ERROR en línea 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
TRUNCATE no comprueba fila a fila como DELETE, así que no puede saber si deja huérfanos: si la tabla es padre de una FK habilitada y la tabla hija tiene filas, se niega. Desde Oracle 12c existe TRUNCATE ... CASCADE, que vacía también las tablas hijas, pero solo si las FK están definidas con ON DELETE CASCADE:
CREATE TABLE padre_demo (id NUMBER CONSTRAINT pk_padre_demo PRIMARY KEY);
CREATE TABLE hijo_demo (id NUMBER, padre_id NUMBER CONSTRAINT fk_hijo_padre REFERENCES padre_demo (id));
INSERT INTO padre_demo VALUES (1);
INSERT INTO hijo_demo VALUES (1, 1);
COMMIT;
TRUNCATE TABLE padre_demo CASCADE;
TRUNCATE TABLE padre_demo CASCADE
*
ERROR en línea 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "CURSO"."HIJO_DEMO"
En EMPRESA, lineas_pedido → pedidos y asignaciones → proyectos sí son ON DELETE CASCADE, por lo que TRUNCATE TABLE pedidos CASCADE; vaciaría a la vez pedidos y lineas_pedido (lo usarás en el ejercicio resuelto 7).
⚠️ Importante: En nuestras pruebas con Oracle 26ai,
TRUNCATEdel padre funcionó cuando todas las tablas hijas estaban vacías, aunque la FK estuviera habilitada. En versiones anteriores la comprobación se hacía sobre la existencia de la FK habilitada, con independencia de los datos. Si tu script debe funcionar también en 19c, deshabilita las FK antes delTRUNCATE(y vuélvelas a habilitar después) o usaDELETE.
13.4. Tabla comparativa
| Característica | DELETE FROM t | TRUNCATE TABLE t |
|---|---|---|
| Sublenguaje | DML | DDL |
| Filas afectadas | Todas o las que cumplan el WHERE | Siempre todas (no admite WHERE) |
| Transacción | Forma parte de la transacción actual | Hace COMMIT implícito antes y después |
ROLLBACK | Sí, se puede deshacer | No, es irreversible |
| Undo / redo generado | Mucho (una entrada por fila e índice) | Mínimo (solo cambios de diccionario) |
Triggers DELETE | Se disparan | No se disparan |
| Velocidad | Proporcional al número de filas | Prácticamente constante |
| High Water Mark | No se mueve | Vuelve al inicio del segmento |
| Espacio | No se libera (queda para nuevas filas de la misma tabla) | Se libera (DROP STORAGE) o se conserva (REUSE STORAGE) |
| Claves ajenas | Comprueba fila a fila; aplica ON DELETE CASCADE/SET NULL | Error ORA-02266 si hay hijas con datos y FK habilitada; CASCADE solo con ON DELETE CASCADE |
| Privilegio necesario | DELETE sobre la tabla | Ser el propietario o tener DROP ANY TABLE |
| Pasa por la papelera | No aplica | No: las filas no se recuperan con FLASHBACK TABLE ... TO BEFORE DROP |
💡 Consejo: Usa
TRUNCATEpara vaciar tablas de trabajo, de carga o de pruebas. UsaDELETEcuando necesites borrar solo algunas filas, poder deshacer, disparar triggers o respetar las acciones referenciales.
14. IF EXISTS e IF NOT EXISTS (Oracle 23ai)
⚠️ Importante: Disponible desde Oracle 23ai.
Un problema clásico de los scripts de despliegue: DROP TABLE x falla si la tabla no existe y CREATE TABLE x falla si ya existe. Oracle 23ai incorpora las cláusulas IF EXISTS e IF NOT EXISTS en el DDL:
DROP TABLE no_existe;
DROP TABLE IF EXISTS no_existe;
CREATE TABLE IF NOT EXISTS departamentos (x NUMBER);
ALTER TABLE IF EXISTS no_existe ADD x NUMBER;
DROP TABLE no_existe
*
ERROR en línea 1:
ORA-00942: table or view "CURSO"."NO_EXISTE" does not exist
Tabla borrada.
Tabla creada.
Tabla modificada.
Observa que, con IF [NOT] EXISTS, SQL*Plus informa “Tabla borrada”, “Tabla creada” o “Tabla modificada” aunque no haya hecho nada: la sentencia termina bien sin ejecutar la acción.
⚠️ Importante:
CREATE TABLE IF NOT EXISTS departamentos (x NUMBER)no comprueba la estructura: como ya existe una tabladepartamentos, no hace nada, aunque la existente tenga columnas totalmente distintas. No es un “crear o sustituir”.
Las cláusulas se aplican al objeto, no a las columnas: no existe ADD IF NOT EXISTS columna ni DROP COLUMN IF EXISTS:
CREATE TABLE prueba_ie (a NUMBER);
ALTER TABLE prueba_ie ADD IF NOT EXISTS (b NUMBER);
ALTER TABLE prueba_ie DROP COLUMN IF EXISTS b;
DROP TABLE IF EXISTS prueba_ie PURGE;
Tabla creada.
ALTER TABLE prueba_ie ADD IF NOT EXISTS (b NUMBER)
*
ERROR en línea 1:
ORA-02000: missing NULL keyword
ALTER TABLE prueba_ie DROP COLUMN IF EXISTS b
*
ERROR en línea 1:
ORA-03048: SQL reserved word 'EXISTS' is not syntactically valid following '...prueba_ie DROP COLUMN IF '
Tabla borrada.
IF [NOT] EXISTS está disponible en CREATE, ALTER y DROP de tablas, vistas, índices, secuencias, sinónimos, usuarios y otros objetos. Alternativa clásica (19c/21c): ejecutar el DROP y aceptar el error ORA-00942 cuando el objeto no existe (es lo que hace empresa_borrar.sql), usando en SQL*Plus WHENEVER SQLERROR CONTINUE para que el script siga. La otra alternativa clásica, capturar la excepción en un bloque anónimo, requiere PL/SQL (Curso de PL/SQL).
15. Documentar el esquema: COMMENT ON
Un buen esquema se documenta dentro de la propia base de datos. COMMENT ON guarda un texto descriptivo (hasta 4000 bytes) asociado a una tabla, vista o columna, que cualquier herramienta (SQL Developer, generadores de documentación, herramientas de BI) puede mostrar.
COMMENT ON TABLE tabla IS 'texto';
COMMENT ON COLUMN tabla.columna IS 'texto';
COMMENT ON TABLE empleados IS
'Empleados de la empresa. Cada empleado pertenece (opcionalmente) a un departamento y puede tener un jefe';
COMMENT ON COLUMN empleados.salario IS 'Salario bruto mensual en euros';
COMMENT ON COLUMN empleados.comision IS 'Comisión mensual en euros (solo vendedores). NULL = no aplica';
COMMENT ON COLUMN empleados.jefe_id IS 'Jefe directo (FK a EMPLEADOS). NULL para el director general';
Comentario creado.
Comentario creado.
Comentario creado.
Comentario creado.
Los comentarios se consultan en USER_TAB_COMMENTS (tablas y vistas) y USER_COL_COMMENTS (columnas), y sus equivalentes ALL_ y DBA_:
SELECT table_name, table_type, comments
FROM user_tab_comments
WHERE table_name = 'EMPLEADOS';
SELECT column_name, comments
FROM user_col_comments
WHERE table_name = 'EMPLEADOS' AND comments IS NOT NULL;
TABLE_NAME TABLE_TYPE COMMENTS
------------ ---------- ----------------------------------------------------------------------
EMPLEADOS TABLE Empleados de la empresa. Cada empleado pertenece (opcionalmente) a un
departamento y puede tener un jefe
1 fila seleccionada.
COLUMN_NAME COMMENTS
------------ ----------------------------------------------------------------------
SALARIO Salario bruto mensual en euros
COMISION Comisión mensual en euros (solo vendedores). NULL = no aplica
JEFE_ID Jefe directo (FK a EMPLEADOS). NULL para el director general
3 filas seleccionadas.
Para borrar un comentario se asigna la cadena vacía (que en Oracle equivale a NULL):
COMMENT ON COLUMN empleados.comision IS '';
SELECT column_name, comments
FROM user_col_comments
WHERE table_name = 'EMPLEADOS' AND comments IS NOT NULL;
Comentario creado.
COLUMN_NAME COMMENTS
------------ ----------------------------------------------------------------------
SALARIO Salario bruto mensual en euros
JEFE_ID Jefe directo (FK a EMPLEADOS). NULL para el director general
2 filas seleccionadas.
💡 Consejo:
COMMENT ONtambién sirve para vistas, vistas materializadas (COMMENT ON MATERIALIZED VIEW) y, desde 23ai, existen las anotaciones (ANNOTATIONS), pares clave-valor más estructurados para metadatos. Un comentario por tabla y uno por cada columna no evidente es una norma de calidad en muchos equipos.
16. El commit implícito de las sentencias DDL
📘 Concepto: En Oracle, cada sentencia DDL (
CREATE,ALTER,DROP,TRUNCATE,RENAME,COMMENT,GRANT,REVOKE…) se ejecuta en su propia transacción y realiza un COMMIT implícito antes y después de ejecutarse. Consecuencia: cualquier cambio DML pendiente de la transacción en curso queda confirmado en cuanto lanzas un DDL, aunque después hagasROLLBACK.
Las transacciones se estudian a fondo en el módulo 35; aquí basta con saber que ROLLBACK deshace los cambios no confirmados. Veamos cuatro casos:
Caso 1: DDL correcto.
INSERT INTO departamentos (dept_id, nombre, localidad) VALUES (80, 'CALIDAD', 'Málaga');
CREATE TABLE auditoria_demo (id NUMBER, texto VARCHAR2(50));
ROLLBACK;
SELECT dept_id, nombre, localidad FROM departamentos WHERE dept_id = 80;
1 fila creada.
Tabla creada.
Rollback terminado.
DEPT_ID NOMBRE LOCALIDAD
---------- -------------------- ------------
80 CALIDAD Málaga
1 fila seleccionada.
El ROLLBACK no ha deshecho el INSERT: el CREATE TABLE lo confirmó.
Caso 2: DDL que falla al ejecutarse (la tabla ya existe):
INSERT INTO departamentos (dept_id, nombre, localidad) VALUES (81, 'COMPRAS', 'Murcia');
CREATE TABLE auditoria_demo (id NUMBER);
ROLLBACK;
SELECT dept_id, nombre, localidad FROM departamentos WHERE dept_id IN (80, 81);
1 fila creada.
CREATE TABLE auditoria_demo (id NUMBER)
*
ERROR en línea 1:
ORA-00955: name is already used by an existing object
Rollback terminado.
DEPT_ID NOMBRE LOCALIDAD
---------- -------------------- ------------
80 CALIDAD Málaga
81 COMPRAS Murcia
2 filas seleccionadas.
¡Sorpresa! El CREATE TABLE falló, pero el commit implícito previo ya se había hecho: el departamento 81 queda confirmado.
Caso 3: DDL con error de sintaxis:
INSERT INTO departamentos (dept_id, nombre, localidad) VALUES (82, 'SEGURIDAD', 'Cádiz');
CREATE TABLA auditoria_demo2 (id NUMBER);
ROLLBACK;
SELECT dept_id, nombre, localidad FROM departamentos WHERE dept_id IN (80, 81, 82);
1 fila creada.
CREATE TABLA auditoria_demo2 (id NUMBER)
*
ERROR en línea 1:
ORA-00901: invalid CREATE command
Rollback terminado.
DEPT_ID NOMBRE LOCALIDAD
---------- -------------------- ------------
80 CALIDAD Málaga
81 COMPRAS Murcia
2 filas seleccionadas.
Ahora el INSERT del 82 sí se ha deshecho: la sentencia ni siquiera llegó a reconocerse como DDL (falló en el análisis sintáctico), así que no hubo commit.
Caso 4: TRUNCATE de otra tabla también confirma:
INSERT INTO departamentos (dept_id, nombre, localidad) VALUES (83, 'JURÍDICO', 'Toledo');
TRUNCATE TABLE auditoria_demo;
ROLLBACK;
SELECT dept_id, nombre FROM departamentos WHERE dept_id >= 80;
1 fila creada.
Tabla truncada.
Rollback terminado.
DEPT_ID NOMBRE
---------- --------------------
80 CALIDAD
81 COMPRAS
83 JURÍDICO
3 filas seleccionadas.
sequenceDiagram
participant U as Usuario
participant O as Oracle
U->>O: INSERT (fila 80)
Note over O: Transacción abierta,<br/>cambio pendiente
U->>O: CREATE TABLE ...
O->>O: COMMIT implícito (confirma la fila 80)
O->>O: Ejecuta el DDL
O->>O: COMMIT implícito
U->>O: ROLLBACK
Note over O: No hay nada pendiente<br/>que deshacer
Limpiamos lo que hemos dejado:
DELETE FROM departamentos WHERE dept_id >= 80;
COMMIT;
DROP TABLE auditoria_demo PURGE;
3 filas suprimidas.
Confirmación terminada.
Tabla borrada.
⚠️ Importante: No mezcles DML y DDL en la misma transacción esperando poder deshacerlo todo. En Oracle el DDL no es transaccional: no se puede deshacer un
CREATE,ALTERoDROPconROLLBACK. Otros SGBD (PostgreSQL, SQL Server) sí permiten DDL transaccional; Oracle no.
17. Evolución de un esquema real: migración de EMPRESA v1.0 a v2.0
Vamos a aplicar todo lo anterior a un caso realista. La dirección de la empresa pide estos cambios:
- Las localidades de los departamentos deben pasar a una tabla
SEDES(en el futuro se guardará la dirección de cada sede). Es un caso de normalización: la localidad se repite (Madrid aparece dos veces) y queremos una entidad propia. - Hay que registrar la fecha de baja de los empleados, que nunca puede ser anterior a la fecha de alta.
- Se ha detectado un error de diseño:
lineas_pedido.descuentoesNUMBER(4,2)(máximo 99,99), pero la restricciónck_lin_descuentopermite hasta 100. Un descuento del 100 % (un regalo) es imposible de registrar:
INSERT INTO lineas_pedido VALUES (5024, 2, 6, 1, 19.90, 100);
INSERT INTO lineas_pedido VALUES (5024, 2, 6, 1, 19.90, 100)
*
ERROR en línea 1:
ORA-01438: value 100 greater than specified precision (4, 2) for column
- A partir de ahora, el esquema debe registrar su versión para saber qué migraciones se han aplicado.
17.1. Esquema antes y después
EMPRESA v1.0 (fragmento):
DEPARTAMENTOS (dept_id, nombre, localidad, presupuesto)
EMPRESA v2.0 (fragmento):
SEDES (sede_id, ciudad, direccion) — UK: ciudad
DEPARTAMENTOS (dept_id, nombre, presupuesto, sede_id)
sede_id → SEDES(sede_id),NOT NULL
ESQUEMA_VERSION (version, fecha, descripcion)
erDiagram
SEDES ||--o{ DEPARTAMENTOS : "alberga"
DEPARTAMENTOS ||--o{ EMPLEADOS : "tiene"
SEDES {
NUMBER sede_id PK
VARCHAR2 ciudad UK
VARCHAR2 direccion
}
DEPARTAMENTOS {
NUMBER dept_id PK
VARCHAR2 nombre
NUMBER presupuesto
NUMBER sede_id FK
}
EMPLEADOS {
NUMBER emp_id PK
DATE fecha_alta
DATE fecha_baja "nueva"
}
ESQUEMA_VERSION {
VARCHAR2 version PK
DATE fecha
VARCHAR2 descripcion
}
17.2. El script de migración
-- =====================================================================
-- EMPRESA - Migración v1.0 -> v2.0
-- Autor: equipo de BD Fecha: 2026-10-04
-- Requisitos: esquema EMPRESA v1.0 cargado
-- =====================================================================
SET ECHO ON
WHENEVER SQLERROR EXIT SQL.SQLCODE -- si algo falla, se detiene el script
-- Paso 0: tabla de control de versiones
CREATE TABLE esquema_version (
version VARCHAR2(10) CONSTRAINT pk_esquema_version PRIMARY KEY,
fecha DATE DEFAULT SYSDATE CONSTRAINT nn_ver_fecha NOT NULL,
descripcion VARCHAR2(200)
);
INSERT INTO esquema_version (version, descripcion) VALUES ('1.0', 'Esquema inicial EMPRESA');
-- Paso 1: nueva tabla SEDES, rellenada con las localidades existentes
CREATE TABLE sedes (
sede_id NUMBER(3) CONSTRAINT pk_sedes PRIMARY KEY,
ciudad VARCHAR2(40) CONSTRAINT nn_sed_ciudad NOT NULL,
direccion VARCHAR2(100),
CONSTRAINT uq_sed_ciudad UNIQUE (ciudad)
);
INSERT INTO sedes (sede_id, ciudad)
SELECT ROWNUM, localidad
FROM (SELECT DISTINCT localidad FROM departamentos WHERE localidad IS NOT NULL ORDER BY localidad);
-- Paso 2: DEPARTAMENTOS apunta a SEDES (patrón: añadir, rellenar, restringir, retirar)
ALTER TABLE departamentos ADD sede_id NUMBER(3);
UPDATE departamentos d
SET d.sede_id = (SELECT s.sede_id FROM sedes s WHERE s.ciudad = d.localidad);
ALTER TABLE departamentos ADD CONSTRAINT fk_dep_sede FOREIGN KEY (sede_id) REFERENCES sedes (sede_id);
ALTER TABLE departamentos MODIFY sede_id CONSTRAINT nn_dep_sede NOT NULL;
ALTER TABLE departamentos SET UNUSED COLUMN localidad;
-- Paso 3: fecha de baja de los empleados
ALTER TABLE empleados ADD (
fecha_baja DATE,
CONSTRAINT ck_emp_fechas CHECK (fecha_baja >= fecha_alta)
);
-- Paso 4: corregir la precisión del descuento (ampliar siempre se permite)
ALTER TABLE lineas_pedido MODIFY descuento NUMBER(5,2);
-- Paso 5: documentación
COMMENT ON TABLE sedes IS 'Sedes (ciudades) donde la empresa tiene oficinas';
COMMENT ON COLUMN departamentos.sede_id IS 'Sede en la que se ubica el departamento';
COMMENT ON COLUMN empleados.fecha_baja IS 'Fecha de baja en la empresa. NULL = empleado en activo';
-- Paso 6: registrar la versión
INSERT INTO esquema_version (version, descripcion)
VALUES ('2.0', 'SEDES, fecha_baja, descuento NUMBER(5,2)');
COMMIT;
WHENEVER SQLERROR CONTINUE
Salida de la ejecución (resumida):
Tabla creada.
1 fila creada.
Tabla creada.
6 filas creadas.
Tabla modificada.
7 filas actualizadas.
Tabla modificada.
Tabla modificada.
Tabla modificada.
Tabla modificada.
Tabla modificada.
Comentario creado.
Comentario creado.
Comentario creado.
1 fila creada.
Confirmación terminada.
📘 Concepto:
WHENEVER SQLERROR EXIT SQL.SQLCODEes un comando de SQL*Plus/SQLcl (no SQL ni PL/SQL): si cualquier sentencia posterior falla, el script termina y devuelve el código de error al sistema operativo. Así, una migración que falla a mitad no sigue aplicando cambios sobre un esquema en estado inconsistente.
17.3. Verificación
SELECT * FROM sedes ORDER BY sede_id;
SELECT d.dept_id, d.nombre, s.ciudad
FROM departamentos d
JOIN sedes s ON s.sede_id = d.sede_id
ORDER BY d.dept_id;
SELECT version, descripcion FROM esquema_version ORDER BY version;
DESC departamentos
INSERT INTO lineas_pedido VALUES (5024, 2, 6, 1, 19.90, 100);
ROLLBACK;
SEDE_ID CIUDAD DIRECCION
---------- ------------ ----------------
1 Barcelona (nulo)
2 Bilbao (nulo)
3 Madrid (nulo)
4 Sevilla (nulo)
5 Valencia (nulo)
6 Zaragoza (nulo)
6 filas seleccionadas.
DEPT_ID NOMBRE CIUDAD
---------- --------------- ------------
10 DIRECCIÓN Madrid
20 VENTAS Madrid
30 INFORMÁTICA Valencia
40 CONTABILIDAD Sevilla
50 MARKETING Barcelona
60 LOGÍSTICA Zaragoza
70 INVESTIGACIÓN Bilbao
7 filas seleccionadas.
VERSION DESCRIPCION
-------- ---------------------------------------------
1.0 Esquema inicial EMPRESA
2.0 SEDES, fecha_baja, descuento NUMBER(5,2)
2 filas seleccionadas.
Nombre ¿Nulo? Tipo
--------------------------------------- -------- ----------------------------
DEPT_ID NOT NULL NUMBER(4)
NOMBRE NOT NULL VARCHAR2(40)
PRESUPUESTO NUMBER(12,2)
SEDE_ID NOT NULL NUMBER(3)
1 fila creada.
Rollback terminado.
El descuento del 100 % ya se puede registrar.
17.4. El script de vuelta atrás
Todo script de migración debería tener su script de vuelta atrás (rollback script o down migration), porque el DDL no se puede deshacer con ROLLBACK:
-- EMPRESA - Vuelta atrás v2.0 -> v1.0
ALTER TABLE departamentos ADD localidad VARCHAR2(40);
UPDATE departamentos d
SET d.localidad = (SELECT s.ciudad FROM sedes s WHERE s.sede_id = d.sede_id);
ALTER TABLE departamentos DROP COLUMN sede_id CASCADE CONSTRAINTS;
ALTER TABLE departamentos DROP UNUSED COLUMNS;
DROP TABLE sedes CASCADE CONSTRAINTS PURGE;
ALTER TABLE empleados DROP COLUMN fecha_baja CASCADE CONSTRAINTS;
DROP TABLE esquema_version PURGE;
-- Nota: lineas_pedido.descuento se queda en NUMBER(5,2): no se puede reducir
-- la precisión de una columna con datos (ORA-01440) y el cambio es inocuo.
Tabla modificada.
7 filas actualizadas.
Tabla modificada.
Tabla modificada.
Tabla borrada.
Tabla modificada.
Tabla borrada.
Observa el detalle del paso 2: en la migración usamos SET UNUSED sobre localidad (rápido y seguro), pero la vuelta atrás no puede “resucitar” esa columna: tiene que reconstruir el dato a partir de SEDES. Si en la migración hubiéramos borrado SEDES antes de copiar las ciudades, la información se habría perdido para siempre.
18. Buenas prácticas en scripts de despliegue
- Versiona el esquema. Cada cambio es un script numerado (
V2.0__sedes.sql,V2.1__...) guardado en el control de versiones (Git) junto al código de la aplicación. Herramientas como Liquibase o Flyway automatizan este patrón y registran en una tabla qué scripts se han aplicado (como nuestraesquema_version). - Prueba antes en un entorno de pruebas con una copia de los datos reales. Muchos errores (
ORA-01441,ORA-02296,ORA-02298…) dependen de los datos, no de la sintaxis. - Copia de seguridad antes de desplegar. Recuerda: el DDL no se deshace con
ROLLBACK, yDROP COLUMNoDROP ... PURGEno pasan por la papelera. - Escribe siempre el script de vuelta atrás y pruébalo.
- Detén el script ante el primer error (
WHENEVER SQLERROR EXIT) y registra la salida (SPOOL fichero.log). - Separa DDL y DML en pasos claros. Haz
COMMITexplícito tras cada bloque de DML: no dependas del commit implícito del DDL siguiente. - Patrón “ampliar y contraer” (expand and contract): primero añade lo nuevo (columnas, tablas) sin romper lo viejo; migra los datos; adapta las aplicaciones; y solo al final retira lo antiguo (
SET UNUSED/DROP). Así la aplicación sigue funcionando durante la transición. - Pon nombre a todas las restricciones (
pk_,fk_,uq_,ck_,nn_): los scripts de modificación y de vuelta atrás necesitan referirse a ellas. - Haz los scripts idempotentes cuando sea posible: en 23ai/26ai con
IF [NOT] EXISTS; en 19c, documentando qué errores son esperables e inocuos. - Mide antes de reducir o restringir:
MAX(LENGTHB(...)),COUNT(*) ... WHERE col IS NULL, búsqueda de huérfanos conNOT EXISTS, etc. - Cuidado con las operaciones que reescriben la tabla (
DROP COLUMN,MOVE, cambios de tipo con columna puente): en tablas grandes, planifícalas en ventanas de mantenimiento. - Documenta con
COMMENT ONcada objeto nuevo.
💻 Ejemplo: Plantilla de cabecera para un script de despliegue en SQL*Plus/SQLcl.
-- =====================================================================
-- Proyecto : EMPRESA
-- Script : V2.1__descripcion_corta.sql
-- Autor : nombre
-- Fecha : AAAA-MM-DD
-- Descr. : qué cambia y por qué (enlace a la petición o incidencia)
-- Vuelta : U2.1__descripcion_corta.sql
-- =====================================================================
SPOOL V2.1__descripcion_corta.log
SET ECHO ON TIMING ON
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
-- 1. Comprobaciones previas (consultas que deben devolver 0 filas)
-- 2. DDL de ampliación
-- 3. Migración de datos + COMMIT
-- 4. DDL de restricción / contracción
-- 5. Comentarios
-- 6. Registro de versión + COMMIT
SPOOL OFF
19. Ejercicios
Antes de empezar, recarga EMPRESA con los tres scripts para partir de un estado limpio. Al terminar, vuelve a recargarlo.
Ejercicios resueltos
Ejercicio 1: Añadir una columna obligatoria a una tabla con datos
Añade a empleados la columna dni VARCHAR2(9), obligatoria y única. Como todavía no tenemos los DNI reales, rellénala provisionalmente con el emp_id en 8 cifras seguido de una X.
Ver solución
ALTER TABLE empleados ADD dni VARCHAR2(9); -- 1. añadir (admite nulos)
UPDATE empleados SET dni = LPAD(emp_id, 8, '0') || 'X'; -- 2. rellenar
ALTER TABLE empleados MODIFY dni CONSTRAINT nn_emp_dni NOT NULL; -- 3. hacer obligatoria
ALTER TABLE empleados ADD CONSTRAINT uq_emp_dni UNIQUE (dni); -- 4. hacer única
SELECT emp_id, nombre, dni FROM empleados WHERE emp_id <= 102;
Tabla modificada.
24 filas actualizadas.
Tabla modificada.
Tabla modificada.
EMP_ID NOMBRE DNI
---------- ---------------------- ---------
100 Ana 00000100X
101 Luis 00000101X
102 Marta 00000102X
3 filas seleccionadas.
No podemos añadirla directamente como NOT NULL (ORA-01758) ni con un DEFAULT constante, porque todas las filas tendrían el mismo valor y la restricción UNIQUE fallaría. El patrón de cuatro pasos (añadir, rellenar, restringir) es el estándar. Observa además que el ALTER TABLE del paso 3 hace un commit implícito del UPDATE del paso 2.
Ejercicio 2: Cambiar el tipo de una columna con datos
La columna productos.descatalogado es CHAR(1) con valores 'S'/'N'. Conviértela en NUMBER(1) con valores 1/0, conservando el nombre, la obligatoriedad, el valor por defecto (no descatalogado) y una CHECK.
Ver solución
ALTER TABLE productos ADD descatalogado_n NUMBER(1) DEFAULT 0
CONSTRAINT nn_pro_descat_n NOT NULL;
UPDATE productos SET descatalogado_n = CASE descatalogado WHEN 'S' THEN 1 ELSE 0 END;
ALTER TABLE productos DROP COLUMN descatalogado;
ALTER TABLE productos RENAME COLUMN descatalogado_n TO descatalogado;
ALTER TABLE productos RENAME CONSTRAINT nn_pro_descat_n TO nn_pro_descatalogado;
ALTER TABLE productos ADD CONSTRAINT ck_pro_descatalogado CHECK (descatalogado IN (0, 1));
SELECT producto_id, nombre, descatalogado FROM productos WHERE producto_id IN (13, 14);
SELECT constraint_name, search_condition
FROM user_constraints
WHERE table_name = 'PRODUCTOS' AND constraint_name LIKE '%DESCAT%';
Tabla modificada.
20 filas actualizadas.
Tabla modificada.
Tabla modificada.
Tabla modificada.
Tabla modificada.
PRODUCTO_ID NOMBRE DESCATALOGADO
----------- ---------------------- -------------
13 Auriculares Bluetooth 0
14 Altavoz Inteligente 1
2 filas seleccionadas.
CONSTRAINT_NAME SEARCH_CONDITION
---------------------- ----------------------------------------
NN_PRO_DESCATALOGADO "DESCATALOGADO" IS NOT NULL
CK_PRO_DESCATALOGADO descatalogado IN (0, 1)
2 filas seleccionadas.
Es la técnica de la columna puente. Un MODIFY descatalogado NUMBER(1) directo fallaría con ORA-01463 (por la CHECK IN ('S','N')) o con ORA-01439 (por tener datos). Al borrar la columna antigua, Oracle borra con ella sus restricciones de una sola columna (nn_pro_descatalogado y ck_pro_descatalogado originales), lo que nos permite reutilizar sus nombres. La columna nueva queda la última de la tabla. En 23ai/26ai también podrías usar el tipo BOOLEAN.
Ejercicio 3: Reducir una columna al mínimo posible
Reduce clientes.nombre (VARCHAR2(60)) al menor tamaño que admitan los datos actuales. Demuestra que un byte menos falla.
Ver solución
SELECT MAX(LENGTH(nombre)) AS caracteres, MAX(LENGTHB(nombre)) AS bytes FROM clientes;
ALTER TABLE clientes MODIFY nombre VARCHAR2(26);
ALTER TABLE clientes MODIFY nombre VARCHAR2(27);
CARACTERES BYTES
---------- ----------
27 27
1 fila seleccionada.
ALTER TABLE clientes MODIFY nombre VARCHAR2(26)
*
ERROR en línea 1:
ORA-01441: cannot decrease column length because some value is too big
Tabla modificada.
Se mide con LENGTHB porque la columna está definida en bytes. El nombre más largo (“Construcciones Levante S.A.”) tiene 27 caracteres sin tildes, así que caracteres y bytes coinciden. En la práctica, nunca dejes una columna “justa”: el siguiente cliente con un nombre más largo no podría darse de alta. Devuelve la columna a VARCHAR2(60).
Ejercicio 4: Añadir una regla de negocio
Solo los empleados con oficio VENDEDOR pueden tener comisión (los demás deben tenerla a NULL). Añade la restricción y comprueba que impide poner comisión a un analista, pero no a un vendedor.
Ver solución
ALTER TABLE empleados ADD CONSTRAINT ck_emp_comision_vendedor
CHECK (comision IS NULL OR oficio = 'VENDEDOR');
UPDATE empleados SET comision = 100 WHERE emp_id = 120; -- Pablo, ANALISTA
UPDATE empleados SET comision = 100 WHERE emp_id = 113; -- Sergio, VENDEDOR
ROLLBACK;
Tabla modificada.
UPDATE empleados SET comision = 100 WHERE emp_id = 120
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_EMP_COMISION_VENDEDOR) violated
1 fila actualizada.
Rollback terminado.
La restricción se crea con validación, así que Oracle comprobó primero que todos los empleados actuales con comisión no nula son vendedores (lo son: 110 a 113). Observa el comision IS NULL OR ...: una CHECK se cumple cuando la condición es verdadera o desconocida, pero escribir explícitamente el caso NULL deja clara la intención.
Ejercicio 5: Carga de datos con la clave ajena deshabilitada
Simula una carga masiva: deshabilita fk_ped_cliente, inserta los pedidos 5025 (cliente 1099, inexistente) y 5026 (cliente 1004), confirma, e intenta rehabilitar la FK registrando las filas erróneas. Elimina las filas incorrectas y rehabilita la restricción.
Ver solución
CREATE TABLE excepciones (row_id ROWID, owner VARCHAR2(128),
table_name VARCHAR2(128), constraint VARCHAR2(128));
ALTER TABLE pedidos DISABLE CONSTRAINT fk_ped_cliente;
INSERT INTO pedidos (pedido_id, cliente_id, empleado_id, fecha_pedido, estado)
VALUES (5025, 1099, 110, DATE '2026-07-15', 'PENDIENTE');
INSERT INTO pedidos (pedido_id, cliente_id, empleado_id, fecha_pedido, estado)
VALUES (5026, 1004, 111, DATE '2026-07-16', 'PENDIENTE');
COMMIT;
ALTER TABLE pedidos ENABLE CONSTRAINT fk_ped_cliente EXCEPTIONS INTO excepciones;
SELECT p.pedido_id, p.cliente_id
FROM pedidos p
WHERE p.ROWID IN (SELECT row_id FROM excepciones);
DELETE FROM pedidos WHERE ROWID IN (SELECT row_id FROM excepciones);
COMMIT;
ALTER TABLE pedidos ENABLE CONSTRAINT fk_ped_cliente;
DROP TABLE excepciones PURGE;
Tabla creada.
Tabla modificada.
1 fila creada.
1 fila creada.
Confirmación terminada.
ALTER TABLE pedidos ENABLE CONSTRAINT fk_ped_cliente EXCEPTIONS INTO excepciones
*
ERROR en línea 1:
ORA-02298: cannot validate (CURSO.FK_PED_CLIENTE) - parent keys not found
PEDIDO_ID CLIENTE_ID
---------- ----------
5025 1099
1 fila seleccionada.
1 fila suprimida.
Confirmación terminada.
Tabla modificada.
Tabla borrada.
Solo el pedido 5025 es huérfano; el 5026 es correcto y se conserva. Alternativa sin tabla de excepciones: SELECT * FROM pedidos p WHERE NOT EXISTS (SELECT 1 FROM clientes c WHERE c.cliente_id = p.cliente_id); (subconsultas, módulo 31).
Ejercicio 6: Recuperar una tabla borrada por error
Un compañero ha ejecutado DROP TABLE asignaciones;. Recupérala y déjala exactamente como estaba: mismos nombres de índice y restricciones, y con sus claves ajenas.
Ver solución
DROP TABLE asignaciones;
FLASHBACK TABLE asignaciones TO BEFORE DROP;
SELECT index_name FROM user_indexes WHERE table_name = 'ASIGNACIONES';
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'ASIGNACIONES'
ORDER BY constraint_type;
Tabla borrada.
Flashback terminado.
INDEX_NAME
--------------------------------
BIN$XPiRTv1UDBPgYwIAEayTZw==$0
1 fila seleccionada.
CONSTRAINT_NAME C SEARCH_CONDITION
-------------------------------- - ------------------------------
BIN$XPiRTv1RDBPgYwIAEayTZw==$0 C "FECHA_ASIGNACION" IS NOT NULL
BIN$XPiRTv1SDBPgYwIAEayTZw==$0 C horas >= 0
BIN$XPiRTv1TDBPgYwIAEayTZw==$0 P
3 filas seleccionadas.
La tabla ha vuelto con sus datos, pero el índice y las restricciones tienen nombres BIN$ y faltan las dos claves ajenas (fk_asi_emp y fk_asi_proyecto). Lo arreglamos (los nombres BIN$ serán distintos en tu base de datos):
ALTER INDEX "BIN$XPiRTv1UDBPgYwIAEayTZw==$0" RENAME TO pk_asignaciones;
ALTER TABLE asignaciones RENAME CONSTRAINT "BIN$XPiRTv1TDBPgYwIAEayTZw==$0" TO pk_asignaciones;
ALTER TABLE asignaciones RENAME CONSTRAINT "BIN$XPiRTv1RDBPgYwIAEayTZw==$0" TO nn_asi_fecha;
ALTER TABLE asignaciones RENAME CONSTRAINT "BIN$XPiRTv1SDBPgYwIAEayTZw==$0" TO ck_asi_horas;
ALTER TABLE asignaciones ADD CONSTRAINT fk_asi_emp
FOREIGN KEY (emp_id) REFERENCES empleados (emp_id);
ALTER TABLE asignaciones ADD CONSTRAINT fk_asi_proyecto
FOREIGN KEY (proyecto_id) REFERENCES proyectos (proyecto_id) ON DELETE CASCADE;
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'ASIGNACIONES'
ORDER BY constraint_name;
Índice modificado.
Tabla modificada.
Tabla modificada.
Tabla modificada.
Tabla modificada.
Tabla modificada.
CONSTRAINT_NAME C
---------------------- -
CK_ASI_HORAS C
FK_ASI_EMP R
FK_ASI_PROYECTO R
NN_ASI_FECHA C
PK_ASIGNACIONES P
5 filas seleccionadas.
Moraleja: la papelera recupera los datos, pero las claves ajenas hay que reconstruirlas. Por eso es tan valioso tener el script de creación (empresa_crear.sql) a mano y bajo control de versiones.
Ejercicio 7: Vaciar proyectos y asignaciones
Hay que vaciar proyectos y asignaciones para recargarlas desde otro sistema. Hazlo con TRUNCATE (guarda antes una copia para poder restaurarlas) y explica qué ocurre en cada intento.
Ver solución
CREATE TABLE proy_bak AS SELECT * FROM proyectos;
CREATE TABLE asig_bak AS SELECT * FROM asignaciones;
TRUNCATE TABLE proyectos CASCADE;
SELECT (SELECT COUNT(*) FROM proyectos) AS proyectos,
(SELECT COUNT(*) FROM asignaciones) AS asignaciones
FROM dual;
-- Restauración
INSERT INTO proyectos SELECT * FROM proy_bak;
INSERT INTO asignaciones SELECT * FROM asig_bak;
COMMIT;
DROP TABLE proy_bak PURGE;
DROP TABLE asig_bak PURGE;
Tabla creada.
Tabla creada.
Tabla truncada.
PROYECTOS ASIGNACIONES
---------- ------------
0 0
1 fila seleccionada.
5 filas creadas.
14 filas creadas.
Confirmación terminada.
Tabla borrada.
Tabla borrada.
TRUNCATE TABLE proyectos;a secas fallaría conORA-02266, porqueasignacionestiene filas que referencian proyectos.TRUNCATE ... CASCADEfunciona porquefk_asi_proyectoestá definida conON DELETE CASCADE; vacía las dos tablas.- Otra opción válida en 26ai es truncar primero la hija y luego el padre; en 19c, el segundo
TRUNCATEdaríaORA-02266mientras la FK siga habilitada. - La restauración del final es posible solo porque hicimos copias:
TRUNCATEno se deshace conROLLBACK.
Ejercicio 8: Documentar una tabla y generar su ficha
Añade comentarios a la tabla pedidos y a sus columnas no evidentes, y escribe una consulta que genere la “ficha” de la tabla: columna, tipo, si admite nulos y comentario.
Ver solución
COMMENT ON TABLE pedidos IS 'Pedidos realizados por los clientes';
COMMENT ON COLUMN pedidos.empleado_id IS 'Empleado que gestiona el pedido. NULL = pedido online';
COMMENT ON COLUMN pedidos.fecha_envio IS 'Fecha de envío. NULL mientras no se ha enviado';
COMMENT ON COLUMN pedidos.estado IS 'PENDIENTE, ENVIADO, ENTREGADO o CANCELADO';
SELECT c.column_name,
CASE
WHEN c.data_type = 'NUMBER' THEN 'NUMBER(' || c.data_precision || ')'
WHEN c.data_type = 'VARCHAR2' THEN 'VARCHAR2(' || c.char_length || ')'
ELSE c.data_type
END AS tipo,
c.nullable AS n,
m.comments
FROM user_tab_columns c
LEFT JOIN user_col_comments m
ON m.table_name = c.table_name AND m.column_name = c.column_name
WHERE c.table_name = 'PEDIDOS'
ORDER BY c.column_id;
Comentario creado.
Comentario creado.
Comentario creado.
Comentario creado.
COLUMN_NAME TIPO N COMMENTS
------------- ------------- - -------------------------------------------------------
PEDIDO_ID NUMBER(8) N (nulo)
CLIENTE_ID NUMBER(6) N (nulo)
EMPLEADO_ID NUMBER(6) Y Empleado que gestiona el pedido. NULL = pedido online
FECHA_PEDIDO DATE N (nulo)
FECHA_ENVIO DATE Y Fecha de envío. NULL mientras no se ha enviado
ESTADO VARCHAR2(12) N PENDIENTE, ENVIADO, ENTREGADO o CANCELADO
6 filas seleccionadas.
USER_TAB_COLUMNS describe las columnas y USER_COL_COMMENTS sus comentarios; el LEFT JOIN (módulo 30) conserva las columnas sin comentario. Este tipo de consultas sobre el diccionario de datos se amplía en el módulo 26.
Ejercicio 9: Predice el resultado
Sin ejecutarlo, ¿qué departamentos con código ≥ 70 habrá al final de este script? Después compruébalo.
INSERT INTO departamentos VALUES (80, 'CALIDAD', 'Málaga', 90000);
SAVEPOINT antes_ddl;
COMMENT ON TABLE departamentos IS 'Departamentos de la empresa';
DELETE FROM departamentos WHERE dept_id = 70;
ROLLBACK TO antes_ddl;
ROLLBACK;
SELECT dept_id, nombre FROM departamentos WHERE dept_id >= 70 ORDER BY dept_id;
Ver solución
1 fila creada.
Punto de grabación creado.
Comentario creado.
1 fila suprimida.
ROLLBACK TO antes_ddl
*
ERROR en línea 1:
ORA-01086: savepoint 'ANTES_DDL' never established in this session or is invalid
Rollback terminado.
DEPT_ID NOMBRE
---------- ----------------------
70 INVESTIGACIÓN
80 CALIDAD
2 filas seleccionadas.
- El
INSERTdel 80 queda pendiente y se crea un punto de grabación (savepoint, módulo 35). COMMENT ONes DDL: confirma elINSERTy elimina el savepoint (un commit borra todos los savepoints).- El
DELETEdel 70 abre una transacción nueva. ROLLBACK TO antes_ddlfalla porque el savepoint ya no existe.- El
ROLLBACKfinal deshace elDELETE: el 70 sigue ahí.
Resultado: están el 70 y el 80. ¡Hasta un inofensivo comentario confirma la transacción! Limpia con DELETE FROM departamentos WHERE dept_id = 80; COMMIT;.
Ejercicios propuestos
Ejercicio 10: Fecha de baja de clientes
Añade a clientes una columna fecha_baja de tipo DATE con una restricción con nombre que impida que sea anterior a fecha_alta. Comprueba con un UPDATE que la restricción funciona y deshaz el cambio con ROLLBACK.
Ejercicio 11: Peso de los productos
Añade a productos la columna peso_kg NUMBER(6,2), obligatoria, con valor por defecto 0,5 y una CHECK que exija un valor mayor que 0. Comprueba que los 20 productos existentes tienen 0,5. Después cambia el valor por defecto a 1 e inserta un producto nuevo sin indicar el peso: ¿qué valor tiene el nuevo? ¿Y los antiguos? Explica por qué.
Ejercicio 12: El mínimo de la descripción
Averigua cuál es el tamaño mínimo (en bytes y en caracteres) al que se podría reducir productos.descripcion. Intenta reducirla a 30 bytes, interpreta el error y prueba después con la semántica CHAR.
Ejercicio 13: Horas con decimales
Se quiere registrar medias horas en asignaciones.horas (NUMBER(5)). Propón el ALTER TABLE necesario, ejecútalo e indica si Oracle lo permite con datos. Si no lo permite, aplica la técnica de la columna puente conservando la CHECK y el DEFAULT.
Ejercicio 14: Renombrar una tabla con dependencias
Renombra asignaciones a participaciones y su clave primaria e índice a pk_participaciones. Comprueba en USER_CONSTRAINTS que las claves ajenas siguen apuntando a empleados y proyectos. ¿Qué pasaría con una vista que consultara asignaciones? Devuelve después todo a su nombre original.
Ejercicio 15: Borrado accidental de una tabla padre
Ejecuta DROP TABLE categorias CASCADE CONSTRAINTS;. Comprueba qué restricciones ha perdido productos. Recupera la tabla con FLASHBACK, renombra sus índices y restricciones y vuelve a crear todas las claves ajenas perdidas (¡incluida la autorrelación fk_cat_padre!). Verifica el resultado comparándolo con empresa_crear.sql.
Ejercicio 16: Migración v2.0 → v2.1
Partiendo de EMPRESA v2.0 (sección 17), escribe un script de migración a la v2.1 que: (a) cree la tabla telefonos_empleado (emp_id, telefono, tipo) con PK compuesta, FK a empleados y CHECK sobre tipo (‘MÓVIL’, ‘FIJO’, ‘EXTENSIÓN’); (b) copie en ella los teléfonos actuales de los empleados como tipo ‘MÓVIL’; (c) marque como no utilizada la columna empleados.telefono; (d) registre la versión. Escribe también su script de vuelta atrás y pruébalos los dos.
Ejercicio 17: DELETE frente a TRUNCATE con 500.000 filas
Crea una tabla ventas_demo de 500.000 filas como en la sección 13. Mide con SET TIMING ON el tiempo de DELETE (seguido de ROLLBACK) y de TRUNCATE, y anota los bloques de USER_SEGMENTS tras cada operación. Después, borra con DELETE el 90 % de las filas, haz COMMIT y comprueba si SELECT COUNT(*) tarda menos que antes. Explica el resultado con el concepto de HWM y recupera el espacio con MOVE. Borra la tabla al terminar.
Resumen
| Concepto | Sintaxis / idea clave |
|---|---|
| Añadir columna | ALTER TABLE t ADD col tipo [DEFAULT v] [NOT NULL]; (siempre al final) |
| Columna obligatoria en tabla con datos | Con DEFAULT, o en tres pasos: ADD → UPDATE → MODIFY ... NOT NULL (ORA-01758) |
DEFAULT ON NULL | Sustituye también los NULL explícitos (12c) |
| Modificar columna | ALTER TABLE t MODIFY col nuevo_tipo / DEFAULT v / NULL / NOT NULL; |
Límites de MODIFY con datos | Reducir texto si cabe (ORA-01441); no reducir NUMBER (ORA-01440); no cambiar tipo (ORA-01439) |
| Semántica de longitud | VARCHAR2(n) = n bytes por defecto; VARCHAR2(n CHAR) = n caracteres |
| Borrar columna | ALTER TABLE t DROP COLUMN col [CASCADE CONSTRAINTS]; / DROP (c1, c2) |
| Borrado diferido | SET UNUSED (col) + DROP UNUSED COLUMNS (USER_UNUSED_COL_TABS) |
| Renombrar columna | ALTER TABLE t RENAME COLUMN a TO b; |
| Restricciones | ADD CONSTRAINT, DROP CONSTRAINT, RENAME CONSTRAINT, DROP PRIMARY KEY [CASCADE], KEEP INDEX |
| Estados | ENABLE/DISABLE × VALIDATE/NOVALIDATE, MODIFY CONSTRAINT, EXCEPTIONS INTO |
| Solo lectura | ALTER TABLE t READ ONLY; / READ WRITE; (ORA-12081) |
| Reorganizar | ALTER TABLE t MOVE [ONLINE]; (índices UNUSABLE sin ONLINE) |
| Renombrar tabla | RENAME a TO b; o ALTER TABLE a RENAME TO b; |
| Borrar tabla | DROP TABLE t [CASCADE CONSTRAINTS] [PURGE]; (ORA-02449) |
| Papelera | SHOW RECYCLEBIN, USER_RECYCLEBIN, FLASHBACK TABLE t TO BEFORE DROP [RENAME TO n], PURGE |
| Vaciar tabla | TRUNCATE TABLE t [REUSE STORAGE] [CASCADE]; DDL, irreversible, mueve la HWM (ORA-02266) |
| 23ai/26ai | DROP TABLE IF EXISTS, CREATE TABLE IF NOT EXISTS, ALTER TABLE IF EXISTS |
| Comentarios | COMMENT ON TABLE/COLUMN ... IS '...'; → USER_TAB_COMMENTS, USER_COL_COMMENTS |
| Commit implícito | Todo DDL confirma la transacción en curso, incluso si falla al ejecutarse |
| Migraciones | Scripts versionados, WHENEVER SQLERROR EXIT, vuelta atrás, “ampliar y contraer” |