Archivo de la etiqueta: PLSQL

Escribir la salida de un sql en un fichero con UTL_FILE

Posted on por

Vamos con otra entrada básica para dummies.

Supongamos que queremos hacer implantar algún tipo de sonda que nos haga una pregunta a la base de datos y nos deje el resultado en un directorio.
Vamos a crear un pequeño procedimiento que, a modo de ejemplo nos saque algunos parámetros del v$session y nos lo deje en un directorio previamente definido llamado SONDA

CREATE OR REPLACE PROCEDURE proc_a_file AS
     fichero utl_file.file_type;
     nombre_fichero  VARCHAR2(255);
     linea_resul  VARCHAR2(255);
        BEGIN
  -- Definimos el nombre del log de salida
     select  'sonda_'||
              to_char(TO_CHAR(sysdate, 'YYYYMMDDHHmiss'))||
             '.log'
              into nombre_fichero  from dual  ;
  -- Abrimos fichero      
       fichero := utl_file.fopen('SONDA', nombre_fichero, 'A');
  -- Añadimos encabezado   
           UTL_FILE.PUT_LINE ( fichero ,'SID, SERIAL,TERMINAL,OSUSER');
  -- Lanzamos consulta        
                     FOR ciclo IN (
                         SELECT
                              select sid SID,
                              serial#  SERIAL,
                              terminal TEMINAL,
                              osuser  USER  
                         from v$session
                               )
                  LOOP  --Bucle de scritura de datos 
                 UTL_FILE.PUT_LINE ( fichero ,ciclo.SID||
                                              chr(44)|| 
                                             ciclo.SERIAL||
                                              chr(44)|| 
                                            ciclo.TERMINAL||
                                            chr(44)||
                                            ciclo.USER);
                 END LOOP;         
-- Limpiamos y cerramos
          utl_file.fflush(fichero);
        utl_file.fclose(fichero);
-- Excepciones de error en el alert.log
    EXCEPTION
    WHEN UTL_FILE.WRITE_ERROR THEN
    dbms_system.ksdwrt(2, 'ORA-XXXX  Error en procedure proc_a_file escrbiendo en fichero de log.');
    WHEN others THEN
       dbms_system.ksdwrt(2, 'ORA-XXXX  Error genérico en procedure   proc_a_file. '||SQLERRM);
      END proc_a_file; -- En del procedure

Ahora ya podemos lanzar nuestro procedimiento, bien desde el código de aplicación, cron, task manager, trigger o desde el lugar que deseemos

Cuando se usan los indices en una consulta con LIKE

Posted on por

Hoy vamos ha hacer una pequeña entrada de tunning.

Muchas veces vemos a la hora de hacer ajustes de rendimiento como las consultas que se ejecutan la base de datos tienen la clausula LIKE, pero su comportamiento es distinto segun como se ejecuta.
¿Cuando se hace un INDEX RANGE y cuando un FULL SCAN? , esto es principalmente lo que veremos hoy

Lo primero,¿ que es lo que hace la claúsula LIKE ?
La clausula LIKE mira si una cadena determinada coincide con un determinado patrón. Mediante el carácter % podemos hacer búsquedas genéricas con esa cadena .
Hemos de destacar que, LIKE contempla la búsqueda de longitud cero, es decir ,que la búsqueda de LIKE ‘PEPE% ‘ incluye tanto PEPESI como PEPE.

Una vez tenemos claro que hace la clausula LIKE, veamos de que manera podemos utilizarlo:

  • CADENA%
  • %CADENA
  • %CADENA%
  • CADENA1%CADENA2

Vamos a ver el comportamiento en cada una de las consultas. Tenemos una tabla llamada Imagen con un campo varchar NOMBRE 

SQL> desc imagen
 Name                 Null?    Type
 ------------------ -------- ---------------
 ID                  NOT NULL NUMBER
 NOMBRE                       VARCHAR2(256)
 RUTA                         VARCHAR2(256)

Sobre este campo NOMBRE hemos creado un y un UNIQUE INDEX llamado IMAGEN_NOMBRE_UNI, vamos ha hacer los 4 tipos de búsqueda y comparar los planes:

CADENA%

Si ejecutamos una búsqueda en la que miramos el final de la cadena, la base de datos intentará utilizar primero el índice 

set autotrace traceonly;
SQL> select * from  imagen where nombre like 'D80%';
|Id | Operation                  | Name           |Rows |Bytes| 
------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                  | 1 |143|3(0)|
| 1 |  TABLE ACCESS BY INDEX ROWID| IMAGEN           | 1 |143|3(0)|
|*2 |   INDEX RANGE SCAN          | IMAGEN_NOMBRE_UNI| 1 |   |2(0)|
-------------------------------------------------------------------

El optimizador conoce como comienza la cadena, con lo que llevará a cabo un INDEX RANGE SCAN para llevar a cabo la consulta con el menor coste posible.

%CADENA

EN este caso estamos buscando el final de la cadena entre el contenido de nuestra columna NOMBRE, si miramos el resultado del plan de ejecución

set autotrace traceonly;
SQL> select * from  imagen where nombre like '%009';
----------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes |
-------------------------------------------------
| 0| SELECT STATEMENT  |       |778 |108K| 93 (2)
|*1|  TABLE ACCESS FULL| IMAGEN|778 |108K| 93 (2)
-------------------------------------------------

El optimizador desconoce como comienza la cadena, con lo que llevará a cabo un TABLE ACCESS FULL de toda la tabla.En este caso no usa el índice ya que no le sirve de nada el indice si debe de comparar la parte final de la cadena

%CADENA%

Que ocurrirá si buscamos la parte central de una cadena?

set autotrace traceonly;
SQL> select * from  imagen where nombre like '%009%';
----------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes |
-------------------------------------------------
| 0| SELECT STATEMENT  |       |778 |108K| 93 (2)
|*1|  TABLE ACCESS FULL| IMAGEN|778 |108K| 93 (2)
-------------------------------------------------

El optimizador desconoce como comienza la cadena, con lo que llevará a cabo un TABLE ACCESS FULL de toda la tabla.
En este caso no usa el índice ya que no le sirve de nada el indice si debe de comparar la parte final de la cadena , es el mismo comportamiento que cuando lanzamos la consulta con %CADENA

CADENA1%CADENA2

Que ocurrirá si buscamos la parte central de una cadena?

set autotrace traceonly;
SQL>  select * from imagen where nombre like 'D80_10%G';

|Id | Operation                  | Name           |Rows |Bytes| 
------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                  | 1 |143|3(0)|
| 1 |  TABLE ACCESS BY INDEX ROWID| IMAGEN           | 1 |143|3(0)|
|*2 |   INDEX RANGE SCAN          | IMAGEN_NOMBRE_UNI| 1 |   |2(0)|
-------------------------------------------------------------------

Este caso es muy similar al de CADENA%, el optimizador si que conoce el comienzo de la cadena, con lo que llevará a cabo un INDEX RANGE SCAN para llevar a cabo la consulta en el menor.
Hay que tener en cuenta que, contra mas restrictiva sea la búsqueda mas rápido será y tendrá menos coste para la base de datos. es decir que, aunque en los dos casos use el índice, el coste de buscar CAD%CADENA2 será mayor que el de buscar CADENA1%CADENA2

Pasar variables de entorno windows a un código PL/SQL

Posted on por

Nos hemos mudado a bloger!
El contenido actualizado de esta entrada lo tienes en:

    http://dba.pamplona.name/2013/12/pasar-variables-de-entorno-windows-un.html

    Hoy vamos a ver algo muy muy sencillo pero que puede generar un quebradero de cabeza cuando,acostumbrado a entornos Unix se trabaja con windows.

    Veamos como pasar parámetros por variables de entorno en unix

    consulta.sh

    #!/bin/bash
export RUTA=/opt/oracle/logs
export ORACLE_SID=miinstancia
sqlplus -s user/pass @consulta.sql

    consulta.sql

    spool ${RUTA}/${INSTANCIA}.log
select sysdate from dual;
spool off 
exit;

    ¿Como podemos conseguir lo mismo en un entorno windows?

    consulta.bat

    SET RUTA=d:\oracle\logs
SET ORACLE_SID=miinstancia
sqlplus -s user/pass @consulta.sql

    consulta.sql

    spool %RUTA%/%INSTANCIA%.log
select sysdate from dual;
spool off 
exit;

    Como podemos ver, la diferencia es bastante pequeña y el funcionamiento final es el mismo, es un simple problema usar las variables de entorno propias de cada sistema operativo.

Consultas sobre tablas particionadas

Posted on por

Nos hemos mudado a bloger!
El contenido actualizado de esta entrada lo tienes en:

    http://dba.pamplona.name/2013/12/consultas-sobre-tablas-particionadas.html

    Hola de nuevo.
    Tras un largo parón, vamos a volver con otra nueva entrada de SQL para dummies
    Vamos a ver algunas consultas interesantes para cuando se trabaja con tablas particionadas.

    Mover una particion de un tablespace a otro 

    ALTER TABLE  MITABLA  MOVE PARTITION   PART_GRANDE 
     TABLESPACE TS_NUEVO NOLOGGING;

    Tamaño de las particiones 

     select  b.TABLE_OWNER,
         b.TABLE_NAME,b.PARTITION_NAME,
         sum(a.bytes)/1024/1024 Mb 
  from
          dba_segments a,
         dba_tab_subpartitions b
  where 
       a.segment_name=b.table_name 
       and a.PARTITION_NAME=b.SUBPARTITION_NAME
  group by b.TABLE_OWNER,b.TABLE_NAME,b.PARTITION_NAME
  order by TABLE_NAME

    Tamaño de las particiones de una determinada tabla 

    select 
   owner,
   segment_name,
   segment_type,
   partition_name,
   bytes/1024/1024 Mb
from
    dba_segments 
where segment_name='TABLA'

Truncate table sin permisos TRUNCATE ANY TABLE

Posted on por

Hoy vamos a ver rápidamente un tema muy sencillo que puede traernos algun que otro dolor de cabeza. Intentar truncar una tabla de otro esquema.

Supongamos que tenemos una aplicación que utiliza dos esquemas:

ADMINISTRADOR-> Propietario de los datos y de todos los objetos del esquema
APLICACION-> Usuario de explotación de los datos que no tiene objetos propios y que accede a los datos de ADMINISTRADOR mediante grants

Este modelo de aplicación es muy común ya que nos garantiza que , el esquema APLICACION nunca va a modificar la estructura del modelo de datos, su funcionamiento se base en dar los grants
DELETE, INSERT, SELECT, UPDATE para todas las tablas del esquema ADMINISTRADOR, lo que permite a el usuario APLICACION borrar todos los datos de una tabla de ADMINISTRADOR, pero no es posible que lleve a cabo una acción de truncado, ya que , para Oracle el truncado es una acción distinta a el borrado (no lo hace sobre los datos sino sobre la propia tabla).

El problema con el que nos encontramos es que, oracle no contempla el otorgar un permiso «TRUNCATE TABLE» a un usuario sobre los objetos de otro usuario, el único permiso que contempla es el de «TRUNCATE ANY TABLE»; lo que es una brecha de seguridad ya que permitiría al usuario APLICACION el truncar cualquier tabla de cualquier otro esquema de la base datos.

¿Como se soluciona el problema?

La solución es muy sencilla, y pasa por hacer una funcion en el esquema ADMINISTRADOR que trunque la tabla que le pasamos por parámetro.
EL procedure sería tal que 


create or replace procedure truncartabla(tabla_a_truncar varchar2) 
is
 begin
    execute immediate 'truncate table ' || tabla_a_truncar ;
 end;

Despues de eso , deberemos de dar permisos de ejecución sobre este procedimiento al usuario APLICACION (seguimos como usuario ADMINISTRADOR)


 grant execute on truncartabla to APLICACION

Ahora ya podremos truncar tablas desde el usuario expfin, el único cambio que habrá que hacer es, en el código SQL donde pone


TRUNCATE TABLE ADMINISTRADOR.TABLA1

por 


execute ADMINISTRADOR.truncartabla('TABLA1');