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.