OracleDBAComandos SQL
Juan Maldonado27 de marzo de 20195 min lectura

Comandos Básicos para administración de Base de Datos Oracle

Una de las tareas fundamentals que todo DBA Oracle debe conocer es saber detectar, diagnosticar y dar una pronta solución a los problemas suscitados dentro de una Base de Datos Oracle  con el fin de mitigar la afectación a la disponibilidad del servicio que se está ofreciendo. Por tal motivo el DBA debe ser capaz llegar a la solución del problema sin depender de herramientas gráficas tales como TOAD, SQL Developer, PLSQL Developer u Oracle Enterprise Manager.

El motivo por qué un admnistrador de base de datos Oracle  no debe depender de herramientas gráficas es sencillo, y es porque estas  herramientas suelen demora  unos minutos en reflejar los inconvenientes que suceden dentro de la Base de Datos.

Con la siguiente recopilación de sentencias SQL el administrador de base de datos podrá obtener información de:

  • Objetos Inválidos
  • Archivelogs Generados por hora
  • Tiempo restante para tareas de respaldo/recuperación por medio RMAN
  • Eventos de espera o bloqueos dentro de la Base de Datos
  • Índices en estado UNUSABLE y REBUILD de los mismos
  • Sentencias Largas o demoradas en la Base de Datos
  • Sentencia ejecutada por una sesion proporcionando el SID
  • Uso de UNDO Segments

A continuación presentamos las sentencias para lograr detectar los puntos antes mencionados:

Objetos Inválidos

Sentecia SQL

-- Objetos inválidos por esquema y tipo de objeto
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

-- Resumen: cantidad de objetos inválidos por esquema
SELECT owner,
       object_type,
       COUNT(*) AS total_invalid
FROM   dba_objects
WHERE  status = 'INVALID'
GROUP BY owner, object_type
ORDER BY total_invalid DESC;

-- Recompilar objetos inválidos (ejecutar como SYSDBA)
-- Procedimientos y funciones
ALTER PROCEDURE <esquema>.<nombre_procedimiento> COMPILE;
ALTER FUNCTION  <esquema>.<nombre_funcion>      COMPILE;

-- Paquetes (spec y body)
ALTER PACKAGE  <esquema>.<nombre_paquete> COMPILE;
ALTER PACKAGE  <esquema>.<nombre_paquete> COMPILE BODY;

-- Triggers
ALTER TRIGGER  <esquema>.<nombre_trigger> COMPILE;

-- Vistas
ALTER VIEW     <esquema>.<nombre_vista>   COMPILE;

-- Recompilar todos los objetos inválidos de un esquema con DBMS_UTILITY
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'MI_ESQUEMA', compile_all => FALSE);

Archivelog generados por Hora

SENTENCIA SQL

-- Archivelogs generados por hora (cantidad y tamaño total en MB)
SELECT TO_CHAR(first_time, 'YYYY-MM-DD') AS dia,
       TO_CHAR(first_time, 'HH24')       AS hora,
       COUNT(*)                           AS cantidad,
       ROUND(SUM(blocks * block_size) / 1024 / 1024, 2) AS tamano_mb
FROM   v$archived_log
WHERE  dest_id = 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD'),
         TO_CHAR(first_time, 'HH24')
ORDER BY dia DESC, hora DESC;

-- Archivelogs generados por día (vista resumen)
SELECT TO_CHAR(first_time, 'YYYY-MM-DD') AS dia,
       COUNT(*)                          AS cantidad,
       ROUND(SUM(blocks * block_size) / 1024 / 1024, 2) AS tamano_mb
FROM   v$archived_log
WHERE  dest_id = 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD')
ORDER BY dia DESC;

Tiempo restante para tareas de respaldo/recuperación RMAN

SENTENCIA SQL

-- Tiempo estimado restante para operaciones RMAN en curso (backup / recover)
SELECT sid,
       serial#,
       opname,
       context,
       ROUND(sofar / DECODE(totalwork, 0, 1, totalwork) * 100, 2) AS porcentaje,
       time_remaining AS segundos_restantes,
       ROUND(time_remaining / 60, 2)                              AS minutos_restantes,
       ROUND(time_remaining / 3600, 2)                            AS horas_restantes,
       message
FROM   v$session_longops
WHERE  sofar < totalwork
  AND  opname LIKE 'RMAN%'
ORDER BY sid;

-- Alternativa: monitorear canales RMAN activos
SELECT s.sid,
       s.serial#,
       p.spid  AS os_pid,
       s.program,
       s.status,
       s.event,
       s.seconds_in_wait
FROM   v$session s
   LEFT JOIN v$process p ON s.paddr = p.addr
WHERE  s.program LIKE 'rman%'
ORDER BY s.sid;

Eventos de espera o bloqueos dentro de la Base de Datos

SENTENCIA SQL

-- Sesiones bloqueando a otras (blocking sessions)
SELECT s1.sid  AS sesion_bloqueante,
       s1.serial#,
       s1.username AS usuario_bloqueante,
       s1.status,
       s2.sid  AS sesion_bloqueada,
       s2.username AS usuario_bloqueado,
       s2.status AS status_bloqueado,
       l.type,
       l.lmode,
       l.request
FROM   v$lock    l
   JOIN v$session s1 ON l.sid = s1.sid
   JOIN v$session s2 ON l.id1 = s2.sid
WHERE  l.block = 1;

-- Eventos de espera activos por sesión
SELECT sid,
       serial#,
       username,
       status,
       event,
       state,
       wait_class,
       seconds_in_wait
FROM   v$session
WHERE  type = 'USER'
  AND  status = 'ACTIVE'
ORDER BY seconds_in_wait DESC;

-- Top eventos de espera en la instancia (ASH)
SELECT event,
       wait_class,
       COUNT(*) AS esperas,
       ROUND(ROUND(SUM(wait_time_micro) / 1000000), 2) AS segundos_espera
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 1
GROUP BY event, wait_class
ORDER BY esperas DESC;

Índices en estado UNUSABLE y REBUILD de los mismos

SENTENCIA SQL

-- Índices en estado UNUSABLE por esquema y tabla
SELECT owner,
       index_name,
       table_owner,
       table_name,
       status,
       partitioned
FROM   dba_indexes
WHERE  status = 'UNUSABLE'
ORDER BY owner, table_name, index_name;

-- Índices particionados en estado UNUSABLE
SELECT index_owner,
       index_name,
       partition_name,
       status
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE'
ORDER BY index_owner, index_name, partition_name;

-- REBUILD de índices UNUSABLE (no particionados)
ALTER INDEX <esquema>.<nombre_indice> REBUILD ONLINE;

-- REBUILD de una partición específica
ALTER INDEX <esquema>.<nombre_indice> REBUILD PARTITION <nombre_particion> ONLINE;

-- Script para recompilar todos los índices UNUSABLE (generar las sentencias)
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD ONLINE;' AS sentencia
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Sentencias Largas o demoradas en la Base de Datos

SENTENCIA SQL

-- Operaciones de larga duración (v$session_longops)
SELECT sid,
       serial#,
       opname,
       target,
       context,
       ROUND(sofar / DECODE(totalwork, 0, 1, totalwork) * 100, 2) AS porcentaje,
       elapsed_seconds   AS segundos_transcurridos,
       time_remaining    AS segundos_restantes,
       message
FROM   v$session_longops
WHERE  sofar < totalwork
  AND  time_remaining > 0
ORDER BY time_remaining DESC;

-- Sesiones activas con su SQL en ejecución
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.event,
       s.seconds_in_wait,
       sql.sql_text
FROM   v$session s
   JOIN v$sql      sql ON s.sql_id      = sql.sql_id
                     AND s.sql_child_number = sql.child_number
WHERE  s.status   = 'ACTIVE'
  AND  s.type     = 'USER'
ORDER BY s.seconds_in_wait DESC;

Sentencia ejecutada por una sesion proporcionando el SID

SENTENCIA SQL

-- SQL ejecutado por una sesión específica (reemplazar :sid por el SID)
SELECT sql.sql_text,
       sql.sql_fulltext,
       sql.executions,
       sql.first_load_time,
       sql.last_load_time,
       sql.parse_calls,
       sql.disk_reads,
       sql.buffer_gets,
       sql.rows_processed
FROM   v$session s
   JOIN v$sql      sql ON s.sql_id      = sql.sql_id
                     AND s.sql_child_number = sql.child_number
WHERE  s.sid = :sid;

-- Alternativa usando V$SESSION.SQL_ADDRESS
SELECT sql_text
FROM   v$sqltext
WHERE  address = (SELECT sql_address FROM v$session WHERE sid = :sid)
ORDER BY piece;

-- Información completa de la sesión (para diagnóstico)
SELECT sid,
       serial#,
       username,
       machine,
       terminal,
       program,
       process,
       status,
       schemaname,
       logon_time,
       event,
       sql_id,
       sql_child_number
FROM   v$session
WHERE  sid = :sid;

Uso de UNDO Segments

SENTENCIA SQL

-- Uso de los segmentos de UNDO por tablespace
SELECT tablespace_name,
       ROUND(SUM(bytes) / 1024 / 1024, 2) AS usado_mb,
       ROUND(SUM(maxbytes) / 1024 / 1024, 2) AS maximo_mb,
       ROUND(SUM(bytes) / DECODE(SUM(maxbytes), 0, 1, SUM(maxbytes)) * 100, 2) AS porcentaje_uso
FROM   dba_undo_extents
GROUP BY tablespace_name
ORDER BY tablespace_name;

-- Espacio libre en el tablespace de UNDO
SELECT tablespace_name,
       ROUND(SUM(bytes) / 1024 / 1024, 2) AS libre_mb
FROM   dba_free_space
WHERE  tablespace_name LIKE 'UNDO%'
GROUP BY tablespace_name;

-- Transacciones activas consumiendo UNDO
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       t.xid,
       t.used_ublk,
       t.used_urec,
       t.start_time,
       ROUND(t.used_ublk * 8 / 1024, 2) AS undo_mb
FROM   v$session   s
   JOIN v$transaction t ON s.taddr = t.addr
ORDER BY t.used_ublk DESC;

-- Estadísticas de generación de UNDO por sesión
SELECT s.sid,
       s.serial#,
       s.username,
       st.name,
       st.value
FROM   v$session     s
   JOIN v$sesstat     st ON s.sid = st.sid
   JOIN v$statname    sn ON st.statistic# = sn.statistic#
WHERE  sn.name = 'undo change vector count'
ORDER BY st.value DESC;