Oracle archivos - alexlatorre

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.

Hace un par de semanas estuve haciendo unas pruebas consultando una base de datos de Oracle y se me pasó por la cabeza la brillante idea de usar Symfony (un framework de php) para montar las consultas.

Mi sorpresa llegó cuando vi que la instalación por defecto de php no trae un driver para oracle y que al activarlo, apache no arranca.

Después de googlear un poco, todo el mundo recomienda usar el instant client de oracle (que viene a ser como un cliente ligero y portable de oracle) y todos te dicen: «descomprímelo en «X» directorio y añádelo al PATH, con eso te funcionará». Todo mentiras, y si estas aquí probablemente hayas llegado a la misma conclusión.

En primer lugar te diré que yo he optado por usar un XAMMP en mi entorno de desarrollo, por lo que mi manual se basará en configurar XAMMP + PHP + ORACLE, así que si no quieres usar XAMMP, puedes seguir el manual, pero probablemente tengas que instalar manualmente el plugin de oracle para php/apache (hay bastantes manuales por ahí que lo explican).

Dicho esto, empecemos!

1 - Descarga todos los archivos necesarios

*Fíjate que la versión del instant client es la 12.1 (no descargues la 12.2 ya que no te va a funcionar !!!!

2 - Descomprime el Instant Client 12.1

En primer lugar, crea la siguiente ruta y descomprime dentro las tres descargas del instant client 12.1 «C:\php-sdk\oracle\x86\instantclient_12_1» par que queden así:

3 - Instala XAMMP

Esta paso es muy simple, siguiente siguiente y aceptar!
Una vez lo tengas instalado, crea en la raiz de htdocs un documento «phpinfo.php» que contenga en siguiente código:

 <?php phpinfo(); ?>

Ahora accederemos con el navegador a http://localhost/phpinfo.php y nos cargará una pagina con toda la información de php. Aqui buscaremos el apartado «Configure Command»

Si te fijas, en este apartado aparece la ruta que hemos creado antes (si, no me la he sacado de la manga, la creé porque el comando que arranca apache especifica esta ruta en concreto. Podría ser que en versiones posteriores de XAMMP la ruta cambiara, si es así, modifica el directorio para que se adapte.

4 - Crea las variables de entorno

Para que el sistema sepa donde se encuentra el instantclient, tenemos que decírselo en las variables de entorno.

Para ello modificaremos el PATH añadiéndole al inicio la ruta en la que hemos puesto el instant client

Y crearemos una variable nueva que se llame «ORACLE_HOME» con la ruta.

5 - Editamos php.ini

Buscaremos la línea en la que se encuentra el plugin OCI8 de oracle y le quitaremos el comentario (;) del inicio de la línea

Después de esto, deberemos reiniciar, ya que normalmente las variables del PATH no se refrescan de manera correcta.

6 - Arrancamos apache

Si todo ha ido bien y no nos hemos saltado ningún paso, apache arrancará correctamente y podremos conectar a Oracle sin problemas!

Aquí os dejo un código para verificar que funciona:

 <?php 
 $conn = oci_connect('user', 'pass', 'ip:1521/service_name');
 $query = "sql_string";
 $stid = oci_parse($conn, $query);
 oci_execute($stid, OCI_DEFAULT);

 echo "<table>";
 while ($row = oci_fetch_array($stid, OCI_ASSOC)) { 
   echo "<tr>";
   foreach ($row as $item) { 
      echo "<td> " . $item . " </td>";
   } echo "</tr>"; }
   echo "</table>";

 oci_free_statement($stid);
 oci_close($conn);
?>

Si os encontrarais con un error del tipo «Global variable undefined» o «Constant undefined» o similar, es que vuestro sistema no encuentra el instant client, revisa la configuración o renicia si no lo has hecho!

7 - Caracteres españoles

Después de conseguir acceder a oracle, me encontré con que el cliente me retornaba valores «?» en lugar de los caracteres españoles como la «ñ» o los acentos.

Esto es debido a que el instant client parece estar preparado para funcionar a la inglesa (vaya, como todo en la informática). Así que si necesitas acceder a cadenas con texto en castellano, deberás especificarle el NLS_LANG.

Para ello, vamos a tirar de nuevo de las variables del sistema, y al igual que hemos definido el ORACLE_HOME, crearemos una que se llame «NLS_LANG» y le daremos como valor «SPANISH_SPAIN.WE8ISO8859P1»

Con esto ya tendríamos el problema resuelto, pero en muchos foros recomiendan añadir la siguiente línea en el httpd.conf de apache justo al final:

 SetEnv NLS_LANG "SPANISH_SPAIN.WE8ISO8859P1"

En mi caso no fue necesario, pero no está demás comentarlo por si a ti no te funciona. Después de esto, como hemos modificado variables del sistema, reiniciamos de nuevo y a funcionar!

Debes tener en cuenta que cuando desees presentar cualquier texto, no vendrá como UTF8, por lo que deberás pasarle la función utf8_encode() para que lo printee correctamente por pantalla.

Si tienes una array, y quieres convertirla entera, puedes usar la siguiente función

public static function array_utf8_encode($dat)
{
 if (is_string($dat))
   return utf8_encode($dat);
 if (!is_array($dat))
   return $dat;
 $ret = array();
 foreach ($dat as $i => $d)
   $ret[$i] = self::array_utf8_encode($d);

 return $ret;
}

Bonus track

Si has estado atento en la intro, he comentado que usaba symfony para conectar a oracle. Si simplemente quieres lanzar consultas contra oracle, con esto te funcionaría, pero si llevas idea de usar Doctrine (el ORM de symfony) hay unos cuantos pasos que deberás hacer para que funcione correctamente. En el siguiente post, explicaré como se hace !

Categoría: Oracle

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

Conectar a Oracle con PHP