Consejos Útiles de SQL para Usuarios Avanzados y no tan avanzados en Oracle

Alex Latorre Bases de datos, Oracle, Sql

En este artículo, escribo una serie de consejos útiles de SQL para usuarios avanzados que trabajan con la base de datos Oracle. Estas consultas y técnicas te ayudarán a optimizar tus operaciones y obtener información valiosa de tu base de datos.

No hay una temática concreta sobre los consejos o snippets de codigo que comparto, más bien son un manojo de herramientas que he ido acumulando con el tiempo y que creo que pueden resultarte útil😆

Consejo 1: Formatear una Fecha a Cadena

/* Formatted on 26/10/2023 9:01:36 (QP5 v5.360) */
-- FORMAT DATE TO STR

select TO_DATE('05/06/1987 16:30','DD/MM/YYYY HH24:MI') AS FechayHora from dual;
/* Formatted on 26/10/2023 9:01:36 (QP5 v5.360) */
-- FORMAT DATE TO STR
SELECT to_char(col_name, 'YYYY-MM-DD HH24:MI:SS') from tab_name;
-- CONVERT VARCHAR COL TO DATE
SELECT TO_DATE (col_name, 'YYYY/MM/DD HH24:MI:SS') from tab_name;
-- CONVERT STR TO DATE
SELECT TO_DATE (str, 'YYYY/MM/DD HH24:MI:SS') from DUAL;

Esta consulta te permitirá formatear una fecha como una cadena en el formato deseado.

Consejo 2: Seleccionar Datos de una Columna JSON en Oracle

-- EXAMPLE TABLE STRUCTURE:
CREATE TABLE LDAP_USERS.API_LOG
(
  DATETIME  	DATE,
  JSONCOLUMN    CLOB
)

-- JSON STRUCTURE:
{ keyname1: value1, keyname2: value2, ... }

-- SELECT EXAMPLE
SELECT a.*, j.*
  FROM YOURSCHEMA.yourtable a, 
  JSON_TABLE ( a.JSONCOLUMN,
               '$'  COLUMNS(
                     keyname1 VARCHAR(10) PATH '$.keyname1',
                     keyname2 VARCHAR(10) PATH '$.keyname2'
                     )
            ) as j

Esta consulta te permite seleccionar datos específicos de una columna JSON en Oracle.

Consejo 3: Calcular el Hash MD5 en Oracle

/* Formatted on 26/10/2023 9:07:00 (QP5 v5.360) */
SELECT standard_hash ('foo', 'MD5') FROM DUAL;

Con esta consulta, puedes calcular el hash MD5 de una cadena en Oracle.

Consejo 4: Ver el Tamaño de los Usuarios en Oracle

/* Formatted on 26/10/2023 9:07:30 (QP5 v5.360) */
  SELECT owner, SUM (BYTES) / 1024 / 1024
    FROM DBA_EXTENTS MB
GROUP BY owner

Esta consulta te mostrará el tamaño de los usuarios en Oracle.

Consejo 5: Analizar el Uso de la Memoria Cache en Oracle

select sum(pins) Executions, sum(reloads) cache_fails,
  trunc(sum(reloads)/sum(pins)*100,2) AS "Match%"
from v$librarycache
where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');

Aprende a analizar el uso de la memoria cache en Oracle y mejorar el rendimiento.

Consejo 6: Monitorear Cursores Abiertos en Oracle

/* Formatted on 26/10/2023 9:09:14 (QP5 v5.360) */
-- Sessions by user 

  SELECT b.sid, a.username, b.VALUE Cursores_Abiertos
    FROM v$session a, v$sesstat b, v$statname c
   WHERE     c.name IN ('opened cursors current')
         AND b.statistic# = c.statistic#
         AND a.sid = b.sid
         AND a.username IS NOT NULL
         AND b.VALUE > 0
ORDER BY 3

Esta consulta te permitirá monitorear y rastrear la cantidad de cursores abiertos en tu base de datos Oracle.

Consejo 7: Analizar el Último SQL Ejecutado en Oracle

/* Formatted on 26/10/2023 9:13:06 (QP5 v5.360) */
  SELECT DISTINCT
         vs.sql_text,
         vs.sharable_mem,
         vs.persistent_mem,
         vs.runtime_mem,
         vs.sorts,
         vs.executions,
         vs.parse_calls,
         vs.module,
         vs.buffer_gets,
         vs.disk_reads,
         vs.version_count,
         vs.users_opening,
         vs.loads,
         TO_CHAR (TO_DATE (vs.first_load_time, 'YYYY-MM-DD/HH24:MI:SS'),
                  'MM/DD  HH24:MI:SS')    first_load_time,
         RAWTOHEX (vs.address)            address,
         vs.hash_value                    hash_value,
         rows_processed,
         vs.command_type,
         vs.parsing_user_id,
         OPTIMIZER_MODE,
         au.USERNAME                      parseuser
    FROM v$sqlarea vs, all_users au
   WHERE     (parsing_user_id != 0)
         AND (au.user_id(+) = vs.parsing_user_id)
         AND (executions >= 1)
ORDER BY buffer_gets / executions DESC

Con esta consulta, podrás obtener detalles sobre la ejecución del último SQL en tu base de datos Oracle, lo que es fundamental para la optimización.

Consejo 8: Identificar Propietarios de Tablespace en Oracle

/* Formatted on 26/10/2023 9:14:02 (QP5 v5.360) */
  SELECT owner,
         DECODE (partition_name,
                 NULL, segment_name,
                 segment_name || ':' || partition_name)    name,
         segment_type,
         tablespace_name,
         bytes,
         initial_extent,
         next_extent,
         PCT_INCREASE,
         extents,
         max_extents
    FROM dba_segments
   WHERE 1 = 1 AND extents > 1
ORDER BY 9 DESC, 3

Utiliza esta consulta para identificar los propietarios de tablespaces en Oracle y comprender la asignación de espacio.

Consejo 9: Visualizar Parámetros de Configuración de Oracle

/* Formatted on 26/10/2023 9:16:36 (QP5 v5.360) */
  SELECT v.name,
         v.VALUE
             VALUE,
         DECODE (ISSYS_MODIFIABLE, 'DEFERRED', 'TRUE', 'FALSE')
             ISSYS_MODIFIABLE,
         DECODE (v.isDefault,  'TRUE', 'YES',  'FALSE', 'NO')
             "DEFAULT",
         DECODE (ISSES_MODIFIABLE,
                 'IMMEDIATE', 'YES',
                 'FALSE', 'NO',
                 'DEFERRED', 'NO',
                 'YES')
             SES_MODIFIABLE,
         DECODE (ISSYS_MODIFIABLE,
                 'IMMEDIATE', 'YES',
                 'FALSE', 'NO',
                 'DEFERRED', 'YES',
                 'YES')
             SYS_MODIFIABLE,
         v.description
    FROM V$PARAMETER v
   WHERE name NOT LIKE 'nls%'
ORDER BY 1

Esta consulta te proporciona información detallada sobre los parámetros de configuración de Oracle.

Consejo 10: Supervisar el Estado de los Tablespace en Oracle

/* Formatted on 26/10/2023 9:17:38 (QP5 v5.360) */
  SELECT t.tablespace_name
             "Tablespace",
         t.status
             "Status",
         ROUND (MAX (d.bytes) / 1024 / 1024, 2)
             "MB Size",
         ROUND (
               (MAX (d.bytes) / 1024 / 1024)
             - (SUM (DECODE (f.bytes, NULL, 0, f.bytes)) / 1024 / 1024),
             2)
             "MB Used",
         ROUND (SUM (DECODE (f.bytes, NULL, 0, f.bytes)) / 1024 / 1024, 2)
             "MB Free",
         t.pct_increase
             "% increase",
         SUBSTR (d.file_name, 1, 80)
             "File"
    FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
   WHERE     t.tablespace_name = d.tablespace_name
         AND f.tablespace_name(+) = d.tablespace_name
         AND f.file_id(+) = d.file_id
GROUP BY t.tablespace_name,
         d.file_name,
         t.pct_increase,
         t.status
ORDER BY 1, 3 DESC

Utiliza esta consulta para supervisar el estado y el uso de los tablespaces en Oracle.

Consejo 11: Explorar la Estructura de la Base de Datos

select * from dictionary

Una de las primeras tareas al administrar una base de datos es explorar su estructura. Esta consulta te proporciona información sobre los objetos y las vistas disponibles en la base de datos Oracle.

Consejo 12: Monitorear Sesiones de Usuarios en Oracle

/* Formatted on 26/10/2023 9:18:43 (QP5 v5.360) */
-- Sessions by user

  SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones
    FROM v$session
GROUP BY username
ORDER BY Numero_Sesiones DESC

El monitoreo de sesiones de usuarios es esencial para garantizar un rendimiento óptimo de la base de datos. Utiliza esta consulta para conocer cuántas sesiones de usuarios están activas y quiénes son los principales usuarios.

Consejo 13: Ordenar Sesiones por Aplicación en Oracle

/* Formatted on 26/10/2023 9:19:10 (QP5 v5.360) */
-- Order sessions by app

  SELECT program Aplicacion, COUNT (program) Numero_Sesiones
    FROM v$session
GROUP BY program
ORDER BY Numero_Sesiones DESC

Ordenar las sesiones por aplicación te ayuda a identificar las aplicaciones que generan más actividad en la base de datos. Esta información es crucial para la asignación de recursos y la resolución de problemas.

Consejo 14: Supervisar Parámetros de Configuración en Oracle

select * from v$system_parameter

La configuración de la base de datos es fundamental. Utiliza esta consulta para obtener una lista completa de los parámetros de configuración de Oracle y sus valores actuales.

Consejo 15: Verificar el Nombre de la Base de Datos en Oracle

select value from v$system_parameter where name = 'db_name'

Es importante conocer el nombre de la base de datos que estás administrando. Esta consulta te proporciona el nombre de la base de datos actual.

Consejo 16: Revisar Archivos de Control en Oracle

select value from v$system_parameter where name = 'control_files'

Los archivos de control son esenciales para la integridad de la base de datos. Utiliza esta consulta para verificar la ubicación de los archivos de control en Oracle.

Consejo 17: Consultar la Versión de Oracle en Uso

select value from v$system_parameter where name = 'compatible'

Conocer la versión de Oracle en uso es crucial para la administración y la planificación a largo plazo. Esta consulta te proporciona información sobre la compatibilidad de la base de datos.

Consejo 18: Supervisar Archivos de Registro (Redo Logs) en Oracle

/* Formatted on 26/10/2023 9:21:37 (QP5 v5.360) */
-- Show log files

SELECT * FROM v$logfile;

-- Load file
EXECUTE DBMS_LOGMNR.add_logfile('/path/to/file.log');
-- Start Session
EXECUTE DBMS_LOGMNR.START_LOGMNR(
  -- Add dictionary (if not, you will get sql's like "UNKNOWN"."OBJ# and HEXTORAW('45465f4748'))
  options => dbms_logmnr.dict_from_online_catalog
  /*
    Example Options
    STARTTIME => '19-Sep-2003 10:15:00',
    NDTIME => '19-Sep-2003 10:45:00',
    DBMS_LOGMNR.CONTINUOUS_MINE);
  */
);

-- Check your file 

SELECT * FROM v$logmnr_contents;

-- End session
EXECUTE DBMS_LOGMNR.end_logmnr();

El monitoreo de archivos de registro es esencial para garantizar la recuperación de datos en caso de fallos. Utiliza estas consultas para supervisar y administrar los archivos de registro en Oracle.

Consejo 19: Verificar el Estado de la Base de Datos en Oracle

select status from v$instance

Conocer el estado de la base de datos es fundamental. Utiliza esta consulta para obtener información sobre el estado actual de tu base de datos Oracle.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.