Reconstruir un UNDO

Los tablespaces de UNDO son muy faciles de recrear. Vayamos paso a paso.

  • Nos conectamos como SYS. 
  • Consultamos en la tabla parameters los valores del undo:

     SQL> select name, value from v$parameter where name like '%undo%';

    NAME                    VALUE
    ----------------------- -----------
    undo_management         AUTO
    undo_tablespace         UNDOTBS
     

  • Creamos un tablespace de undo para reemplazar al anterior:

    create undo tablespace UNDOTBS2 datafile /u01/oradata/SID/UNDOTBS2_01.dbf' size 1000m reuse;
     

  • Convertimos este UNDO en el de referencia

    alter system set undo_tablespace=UNDOTBS2;
     

  • Ya podemos eliminar el UNDO original

    drop tablespace UNDOTBS including contents;

With as Select

Recientemente he encontrado una ayuda para escribir consultas complejas, o lo que es mejor, escribir menos para hacer lo mismo.

La nueva estructura es WITH AS SELECT y he aquí un ejemplo:

With dia As
(Select TRUNC (SYSDATE, 'D') d
From DUAL)
Select TO_CHAR (d, 'D'), TO_CHAR (d, 'DAY'), TO_CHAR (d)
From dia;

(Esta consulta te permite ver de un plumazo cual es el primer día de la semana en una bbdd cuya configuración desconoces y no sabes nada de los parametros NLS)

El WITH nos permite definir en la parte superior tantas subconsultas como necesitemos para simplificar el código y utilizar sus alias libremente en la consulta principal. Ideal para quien no le gusta escribir dos veces la misma cosa.

Respecto al rendimiento: Oracle se esfuerza por optimizarla reescribiendola como una vista en linea o una tabla temporal. Es decir, tan bueno o malo como el rendimiento del original.

Otro ejemplo típico viene en los manuales de oracle:

WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name;

DEPARTMENT_NAME DEPT_TOTAL
------------------------------ ----------
Sales 313800
Shipping 156400

Matando sesiones desde el sistema operativo

Como comenté en un post anterior, la mejor manera de matar una sesión de Oracle es desde dentro del servicio.

ALTER SYSTEM KILL SESSION 'sid,serial#' [IMMEDIATE];

Al recibir este Alter, la sesión pasa a modo KILLED, estado en el que intenta liberar los recursos empleados aunque, lamentablemente, no siempre lo consigue.

Para rematar una sesión marcada como KILLED que realmente no muere con el paso del tiempo hay que operar desde el sistema operativo.

El primer paso es identificar el proceso del sistema operativo conociendo el SID:

select p.spid, s.sid, s.serial#, s.username
from v$session s, v$process p
where p.addr = s.paddr and
s.sid= &SID;

La estructura de los procesos Oracle en Windows y Unix es un poco distinta, por ello para Unix se ejecutará la instrucción:

KILL -9 {spid}

Y desde windows:

orakill {S*I*D} {spid}

IMPORTANTE: este ultimo {S*I*D} es el identificador de la bbdd, no de la sesión.

SQL: Minutos entre dos fechas

Es muy rápido averiguar los minutos que separan dos fechas en Oracle.

Como todos sabemos el tipo DATE en Oracle se almacena internamente en números enteros. La unidad es el día, luego para conocer el día de mañana “a la misma hora” basta con sumar (sysdate + 1).

Si tenemos dos date A y B, siendo A>B, los minutos aproximados que las separan se calcula como:

Select ROUND ((A - B) * 24 * 60)
From DUAL;

Alter current schema y los objetos cualificados

Una de las características que se hecha de menos en Oracle es poder cambiar de usuario una vez iniciada la sesión, algo simple al estilo de Unix: ‘su - menganito‘ donde, siendo administrador, puedes llegar a comportarte como cualquier usuario, sin saber ninguna contraseña.

Vale, pensarás que en Oracle basta con abrir una nueva conexión, pero ¿qué pasa si no sabes la contraseña pero sí tienes los permisos necesarios para manipular los objetos de este otro esquema?

Existe una alternativa que permite esto. Al inicial la sesión lanzar:

ALTER SESSION CURRENT_SCHEMA=SCOTT;

Esta sentencia no te concede los permisos que tenga SCOTT, de hecho no te da ningún privilegio que no poseyeras con el esquema original, simplemente te evita tener que preceder cualquier objeto cualificado como “SCOTT.*”.

No es un “su -” pero también puede ser útil.

He comprobado que funciona desde la versión 9i2, pero puede que desde antes.

Forzando un formato de fecha para las conexiones a un esquema

Desgraciadmente, hay por el mundo un monton de aplicaciones aplicaciones mal diseñadas que no hacen ningún tipo de control del formato de fecha (o número) que gestiona la BBDD.

No entraré en detalles de cual considero que es la manera correcta de hacerlo pero si comentaré como garantizar que es capaz de interpretar correcta y fácilmente desde un único punto de todo el sistema.

Con el siguiente trigger somos capaces de forzar el formato, independientemente de dónde se realice la conexión.

CREATE OR REPLACE TRIGGER trg_fuerza_formato
After Logon On Schema
Declare
Begin
Execute Immediate 'alter session set nls_date_format = ''dd/mm/yyyy hh24:mi:ss''';
Exception
When Others
Then
Null;
End trg_fuerza_formato;
/

Al ser un trigger de sesión, este se ejecutará cada vez que realicemos la conexión luego no importa si el cliente es un sqlplus , ODBC, job o lo que sea. Todas las conexiones a este esquema de bbdd se verán afectadas.

Este script o parecidos nos permitirá jugar con formatos de fechas o números para parchear un sistemas pésimamente programado y de dudosa configuración.

Manipulando fechas con Horas en PL/SQL y SQL

La manera correcta de jugar con fechas en Oracle es truncando, sumando y restando. La manipulacion de cadenas puede ser una hábito muy peligroso que ni me molestaré en comentar.

Supongamos que tengo dos fechas (15/2/2007, 28/2/2007 10:12) y quiero que una herede la hora de la otra (15/2/2007 10:12). Vayamos paso a paso:

Select To_date ('15/2/2007 00:00:00', 'DD/MM/YYYY HH24:MI:SS'),
To_date ('28/2/2007 10:12', 'DD/MM/YYYY HH24:MI:SS')
From DUAL;

Como primer paso me quedo con la parte “día entero” de la hora. Es decir las 00:00.00 de ese día con la función TRUNC. La función trunc tiene otros usos mucho más vistosos si empleamos un segundo parámetro de máscara.

Select TRUNC (To_date ('15/2/2007 00:00:00', 'DD/MM/YYYY HH24:MI:SS'))
From DUAL;

Internamente Oracle almacena las fechas como un número. Luego si resto dos fecha recibo un número. Este representa una fracción de día, siendo 1 un día completo.

Select To_date ('28/2/2007 10:12', 'DD/MM/YYYY HH24:MI:SS')
- TRUNC (To_date ('28/2/2007 10:12', 'DD/MM/YYYY HH24:MI:SS'))
From DUAL;

El resultado es la suma de los anteriores resultados.

Select TRUNC (To_date ('15/2/2007 00:00:00', 'DD/MM/YYYY HH24:MI:SS'))
+ ( To_date ('28/2/2007 10:12', 'DD/MM/YYYY HH24:MI:SS')
- TRUNC (To_date ('28/2/2007 10:12', 'DD/MM/YYYY HH24:MI:SS')))
From DUAL;

Quizá queda un poco confuso pero veamos como queda como llamada anónima PL/SQL.

Declare
fecha_sin_hora Date := To_date ('15/2/2007 00:00:00', 'DD/MM/YYYY HH24:MI:SS');
fecha_con_hora Date := To_date ('28/2/2007 10:12', 'DD/MM/YYYY HH24:MI:SS');
fecha_resultado Date;
resto_hora Number;
Begin
fecha_resultado := TRUNC (fecha_sin_hora);
-- convierte a solo dia por si hubiese algun resto de hora
resto_hora := fecha_con_hora - TRUNC (fecha_con_hora);
-- es un numero, concretamente una fraccion de hora
fecha_resultado := TRUNC (fecha_sin_hora) + resto_hora;
-- el resultado sera la suma de la parte entera mas el numero fraccion de dia.
DBMS_OUTPUT.put_line (TO_CHAR (fecha_resultado, 'DD/MM/YYYY HH24:MI:SS'));
End;
/

Tan sencillo como sumar y restar.