Consultas útiles en Oracle: el diccionario de datos en la práctica

Oracle guarda la descripción completa de la base de datos dentro de la propia base de datos. Las vistas del diccionario —USER_*, ALL_*, DBA_*— permiten responder preguntas cotidianas sin abrir una sola herramienta gráfica: qué tablas ocupan más, qué columna es una clave ajena, quién bloquea a quién, cuándo se recogieron estadísticas por última vez. Esta página reúne las consultas que más se han copiado y pegado de este sitio a lo largo de los años, comentadas una a una.

Tablas, filas y estadísticas

La primera parada es USER_TABLES. La columna NUM_ROWS no cuenta filas en vivo: refleja el último análisis estadístico, y por eso va acompañada de LAST_ANALYZED. Para dimensionar tablas es más que suficiente y no castiga al sistema como un COUNT(*) sobre una tabla de mil millones de filas.

SELECT table_name, num_rows, last_analyzed
FROM   user_tables
ORDER  BY num_rows DESC NULLS LAST
FETCH FIRST 10 ROWS ONLY;

Espacio real: segmentos, no extents

Cuando alguien pregunta «¿cuánto ocupa esta tabla?», la respuesta honesta está en USER_SEGMENTS, que suma los extents asignados a cada segmento, incluidos los índices. Conviene recordar que una tabla puede ocupar gigabytes aunque tenga cero filas: el espacio asignado no se libera con un DELETE, solo con TRUNCATE, DROP o movimientos de segmento.

SELECT segment_name, segment_type,
       ROUND(SUM(bytes)/1024/1024) AS mb
FROM   user_segments
GROUP  BY segment_name, segment_type
ORDER  BY SUM(bytes) DESC
FETCH FIRST 10 ROWS ONLY;

Constraints: quién referencia a quién

Antes de borrar una tabla o desactivar una carga masiva, es útil mapear las dependencias. USER_CONSTRAINTS junto con USER_CONS_COLUMNS muestra qué restricciones existen y sobre qué columnas caen:

SELECT c.constraint_name, c.constraint_type, c.table_name,
       cc.column_name, c.status
FROM   user_constraints c
JOIN   user_cons_columns cc
       ON cc.constraint_name = c.constraint_name
WHERE  c.table_name = 'FACTURAS'
ORDER  BY c.constraint_type, cc.position;

El código de tipo cuenta una historia completa en una letra: P primary key, R referencia (clave ajena), U única y C check. La vista también revela constraints desactivadas (STATUS = 'DISABLED'), un hallazgo frecuente tras cargas heréticas de datos legacy.

Sesiones y bloqueos, con permisos de DBA

En la familia de vistas dinámicas de rendimiento (V$), dos consultas salvan servidores. La primera identifica quién está consumiendo los procesos del sistema; la segunda, quién mantiene bloqueada una tabla. Requerirán privilegios de DBA o roles de lectura del diccionario, pero en desarrollo solemos tenerlos.

SELECT resource_name, current_utilization, max_utilization, limit_value
FROM   v$resource_limit
WHERE  resource_name IN ('processes', 'sessions');

SELECT sid, serial#, username, machine, status, last_call_et
FROM   v$session
WHERE  username IS NOT NULL
ORDER  BY last_call_et DESC;

LAST_CALL_ET mide en segundos cuánto lleva la sesión sin hacer nada: sesiones con minutos de inactividad y estado INACTIVE son candidatas a pools mal dimensionados o aplicaciones que no cierran conexiones. El análisis completo de esa sintomatología, junto con el ORA-12516 que la corona, tiene su propio artículo.

Índices: los grandes olvidados

Las tablas acaparan la atención, pero los índices suelen ocupar más espacio en conjunto y plantear las preguntas más incómodas: ¿existen índices sobre esta columna? ¿Alguno quedó inservible tras una carga? Dos consultas más y el cuadro queda completo:

SELECT index_name, uniqueness, status
FROM   user_indexes
WHERE  table_name = 'FACTURAS';

SELECT index_name, column_name, column_position
FROM   user_ind_columns
WHERE  table_name = 'FACTURAS'
ORDER  BY index_name, column_position;

Un STATUS = 'UNUSABLE' después de operaciones de carga o mantenimiento significa que el índice existe pero el optimizador no lo usará hasta reconstruirlo: el clásico plan que se degrada un lunes sin que nadie haya cambiado nada. Revisar este estado tras cada carga pesada es un hábito de diez segundos que evita sustos de diez horas.

Detalles que marcan la diferencia

• FETCH FIRST n ROWS ONLY (12c+) sustituye al pseudo-bloque ROWNUM <= n y es más legible; en 11g, ROWNUM sigue siendo la única opción para limitar en SQL.
• Las vistas ALL_* muestran lo accesible por tu usuario y las DBA_* toda la base de datos: cambiar de prefijo es la manera rápida de «ver más» sin cambiar de query.
• Si la salida se deforma en SQL*Plus, los SET LINESIZE y COLUMN ... FORMAT del artículo sobre el comando SET arreglan la sesión en diez segundos.

Estas consultas son la materia prima de un panel de control casero o de un script de health-check diario. Combinadas con la capacidad de detectar y desbloquear objetos, forman el kit mínimo para administrar un esquema sin salir de la línea de comandos.