Extracion de documentos en Oracle text


Oracle incorpora un tipo de datos llamado BLOB que permite almacenar documentos de cualquier tipo (imágenes, excel, word, access, comprimidos, vídeos, música, etc). En principio admite cualquier tipo de documento y de cualquier tamaño aunque aún sigue manteniendo el tipo de datos LONG RAW que está obsoleto, sustituido por BLOB. Las instrucciones que os mostramos a continuación admiten ambos tipos de datos: BLOB y LOB RAW.

 

Insertar o extraer documentos  desde Visual Basic

Para poder insertar documentos en un campo BLOB de Oracle con Visual Basic necesitaremos una librería (dll) llamada SAFileMgr Module (SAFileMgr.dll), esta librería está disponible de forma gratuita en la web http://www.softartisans.com. Tras descargar este fichero, deberemos copiarlo a la carpeta del sistema (normalmente C:/Windows/System32) y registrarlo con el comando:  regsvr32 C:/Windows/System32/SAFileMgr.dll .  Tras copiar y registrar el fichero SAFileMgr.dll, también necesitaremos disponer de una base de datos Oracle activa

Tras tener la tabla creada con el campo BLOB, procederemos a abrir Visual Basic y a crear un nuevo proyecto para insertar y extraer documentos en Oracle. En la ventana de “Nuevo proyecto” seleccionaremos “EXE estándar” y pulsaremos “Abrir”:Para que la aplicación funcione correctamente deberemos agregar las referencias necesarias. Para ello pulsaremos en el menú “Proyecto ” – “Referencias” de Visual Basic:Seleccionaremos la referencia FileMgr 1.1:Si no aparece en la lista, pulsaremos en Examinar y seleccionaremos el fichero .dll copiado y registrado anteriormente:C:/Windows/System32/SAFileMgr.dll y seleccionaremos también Microsoft ActiveX Data Objects 2.6 Library: Si no aparece en la lista, pulsaremos en Examinar y seleccionaremos el fichero ubicado en: C:/Archivos de programa/Archivos comunes/system/ado/msado26.tlb

 

Ahora veamos el código de cada uno de los tres  botones de un  formulario tipo para lanzar una consulta de inserción o extracción de un documento :

  • Para el botón “Insertar documento“:

Private Sub btInsertarDocumento_Click()

Dim Conn As New ADODB.Connection

Dim Rs As New ADODB.Recordset

Dim FileMgr As New FileManager

Dim SQL As String

On Error GoTo cError

‘ Conexión mediante OLEDB

Conn.Provider = “OraOLEDB.Oracle”

Conn.Open txtServicio.Text, _

txtUsuario.Text, txtContrasena.Text

‘ Ejecutamos una consulta SQL sobre la tabla  para activar el recordset

SQL = “Select * from ” + txtTabla.Text + ” where 1=2″

Rs.Open SQL, Conn, 2, 3

‘ Añadimos un nuevo registro con los datos indicados

Rs.AddNew

Rs(txtCampoTitulo.Text).Value = txtDocumentoTitulo.Text

Rs(txtCampoRutaDocumento.Text).Value = txtDocumento.Text

‘ Insertamos el documento en el campo BLOB/RAW

FileMgr.ExportToBlob txtDocumento.Text, Rs(txtCampoBLOB.Text)

Rs.Update

Rs.Close

Conn.Close

Set Conn = Nothing

MsgBox “Documento insertado correctamente en ” + “la base de datos: ” + vbCrLf + vbCrLf + txtDocumento.Text, vbOKOnly + vbInformation

cSalir:

Exit Sub

cError:

MsgBox Err.Description

GoTo cSalir

End Sub

  • Para el botón “Ejecutar“:

Private Sub btEjecutar_Click()

Dim Conn As New ADODB.Connection

Dim Rs As New ADODB.Recordset

On Error GoTo cError

‘ Conexión a Oracle mediante OLEDB

Conn.Provider = “OraOLEDB.Oracle”

Conn.Open txtServicio.Text,  txtUsuario.Text, txtContrasena.Text

‘ Ejecutamos consulta SQL introducida por el usuario

Rs.Open txtSQL.Text, Conn, 0, 1

txtExtraerTitulo.Text = Rs(txtCampoTitulo.Text)

txtDestino.Text = Rs(txtCampoRutaDocumento.Text)

Rs.Close

Conn.Close

Set Conn = Nothing

MsgBox “Consulta SQL ejecutada correctamente.”,  vbOKOnly + vbInformation

cSalir:

Exit Sub

cError:

MsgBox Err.Description

GoTo cSalir

End Sub

  • Para el botón “Ejecutar“:

Private Sub btExtraer_Click()

Dim Conn As New ADODB.Connection

Dim Rs As New ADODB.Recordset

Dim FileMgr As New FileManager

Dim continuar As Boolean

On Error GoTo cError

‘ Conexión a Oracle mediante OLEDB

Conn.Provider = “OraOLEDB.Oracle”

Conn.Open txtServicio.Text, _

txtUsuario.Text, txtContrasena.Text

‘ Ejecutamos consulta SQL introducida por el usuario 

Rs.Open txtSQL.Text, Conn, 0, 1

‘ Comprobamos si existe ya un fichero destino

‘ con el mismo nombre

continuar = False

If Dir(txtDestino.Text) <> “” Then

continuar = MsgBox(“Ya existe un documento ” + “con este nombre ¿desea reemplazarlo?”, vbYesNo + vbQuestion) = vbYes

Else

continuar = True

End If

‘ Extraemos el documento del primer   registro de la consulta SQL  en el destino especificado por el usuario

If continuar Then

FileMgr.ImportFromBlob Rs(txtCampoBLOB.Text), txtDestino.Text

MsgBox “Fichero extraído correctamente en: ” + vbCrLf + vbCrLf +  txtDestino.Text, vbOKOnly + vbInformation

End If

Rs.Close

Conn.Close

Set Conn = Nothing

cSalir:

Exit Sub

cError:

MsgBox Err.Description

GoTo cSalir

End Sub

 

Extracción desde java

 

Ahora  veamos  usando el lenguaje   Java un ejemplo de como podemos extraer  documentos almacenados en  CLOB’s   en una base de Datos Oracle Text 9 en Java

Necesitaremos tener instalado el cliente Oracle instalado en la maquina, las credenciales de acceso  ,    asi como referenciar en el proyecto desde el Eclipse las siguientes librerías

 

 

 

Y ahora veamos  un ejemplo de una clase de extracción de campos clob’s

La clase principal es Main , la cual debería seleccionarse al exportar el proyecto desde Eclipse

Se puede ejecutar en local  o crear un pequeño script para invocar el java

En este ejemplo es  obligatorio usar tres argumentos y en este orden  para llamar al programa:

  • nodo= número del nodo a exportar
  • tope = numero de orden donde se quedo la última exportación empezando por cero
  • topefinal= umbral donde termina

 

En el proyecto se han referenciado dos versiones de la MV Java porque puede ocurrir que la MV usada en local ( 1.6 ) sea diferente a la del host  donde se ejecute ( por ejemplo  la 1.4.2_08, razon por la habria que copiar tambien esa version 1.4.2.08  dedseel host  a una ruta local

También se hace referencia al driver de oracle classes12.jar , ruta que también existe en la maquina

 

 

Este proyecto debería exportarse situándose en la raiz del proyecto como un jar sin incluir tanto los ficheros .classspath como el de .project

Antes de compilar debe eliminar manualmente el war existente desde el propio eclipse asi como el log de ejecución (manualmente habría que ubicarse en c:\ users\xxx\workspace\application)

Asimismo por ultimo , al   compilarlo  no debemos incluir el classpath

 

Veamos el código completo;

 

/*
Main.java
*
* Created on 20 de Marzo de 20174, 11:43
*

*/

import java.io.DataOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import oracle.jdbc.driver.OracleDriver;

/**
* @author CRN
*
* EXTRACTOR DOCUMENTOS
*/

public class Main {

private static int nodo;

private static int tope;

private static int topefinal;

public static void main(final String[] args) {

System.out.println(“Inicializando programa …”);

Connection conn = null;

GestorDeConexiones gc = null;

try{

gc = new GestorDeConexiones(“xxxx”, “yyyyyy”); // credenciales de  acceso a la BBDD con el user y pwd de acceso 

conn = gc.getConnection();

// Comprueba si hay argumentos en la linea de comandos

final int nnodo=1;

if(args.length != 0)
//CON ARGUMENTOS

{
for(int counter = 0; counter < args.length; counter++)
{
System.out.println(“Argument index ” + counter + “: ” + args[counter]);
}

System.out.println(” fin argumentos”);

nodo = Integer.parseInt(args[0]); // primer argumento ( el nodo)

tope = Integer.parseInt(args[1]); //segundo argumento (inicio

topefinal = Integer.parseInt(args[2]); //tercer argumento ( tope)

System.out.println(“Argumento nodo= ” + nodo);
System.out.println(“Argumento conteo incial= ” + tope);
System.out.println(“Argumento numero de documentos= ” + topefinal);

}

else

//SIN ARGUMENTOS

{
System.out.println (“No se ha incluido ningún argumento”);
System.exit(0);
}

System.out.println(“NODO” + nodo);
System.out.println(“Valor inicial= ” + tope);
System.out.println(“Valor final= ” + topefinal);

final String path1 =”d:\”;

final String nfichero= path1+”previa_carga_jd_”+nodo+”tope“+tope+”topefinal_”+ topefinal;

//generacion del fichero de salida

File theDir = new File(nfichero);

if (!theDir.exists())
{
System.out.println(“..creando directorio: ” + theDir.getName()); // SI EL DIRECTORIO NO EXISTE ,. SE CREA
boolean result = false;
try{
theDir.mkdir();

result = true;
}
catch(SecurityException se){   //handle it
}
if(result) {
System.out.println(“Directorio creado”);
}
}

//Intentamos cambiar los permisos al directorio creado
System.out.println(“Cambiamos permisos 777 a “+nfichero);
Process theProcess = null;
try
{
theProcess = Runtime.getRuntime().exec(“chmod 777 “+ nfichero);
}
catch(IOException e)
{
System.out.println(“Error en el método exec()”);
}

//creacion fichero de log

final String log1=path1;

final String nfichero1= log1+”directorio_”+nodo+”tope“+tope+”topefinal_”+ topefinal+”.log”;

System.out.println(“Fichero Log de proceso :[” +nfichero1+”]”);
System.out.println(“+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++”);

//generación del fichero de salida

File fichero = new File(nfichero1);

 

if (!fichero.exists())
{

// SI NO EXISTE , SE CREA
System.out.println(“..creando fichero de log: ” + fichero.getName());
boolean result = false;
try{
fichero.createNewFile();

result = true;
}
catch(SecurityException se){ //handle it
}
if(result) {
System.out.println(“Fichero creado”);
}
}

//abre fichero de log
File TextFile = new File(nfichero1);
FileWriter TextOut = new FileWriter(TextFile, true);
TextOut.write(“********************************************************************\r\n”);
TextOut.write(“INICIANDO PROCESO DE EXTRACCIÓN DE DOCUMENTOS r\n”);
TextOut.write(“Fichero Log de proceso :[” +nfichero1+”]\r\n”);
TextOut.close();

String path=nfichero +”/”;

System.out.println (“RUTA DESTINO FINAL”+ path);

//extraemos los objetos de texto
RecuperadorBD2.RecuperarBD2(conn,path, tope,topefinal,nodo,nfichero1);

}
catch (final SQLException sqle) {
System.out.println
(“Error de acceso a BD:” + sqle.getMessage());
sqle.printStackTrace();
}
catch (final IOException ioe){
System.out.println
(“Error de acceso a disco:” + ioe.getMessage());
ioe.printStackTrace();
}

try{
if (gc != null && conn != null)
gc.closeConnection();
}

catch (final SQLException sqle)
{
System.out.println
(“Error de acceso a BD:” + sqle.getMessage());
sqle.printStackTrace();
conn = null;
gc = null;
}
System.out.println(” **** Fin extraccion ****”);
}
}

class RecuperadorBD2
{
public static void RecuperarBD2 (final Connection cn, final String path , final int topec, final int topex, final int nodo ,final String path1)
throws SQLException, IOException
{

 

String topex1= String.valueOf(topex);

String topec1= String.valueOf(topec);

String nnodo= String.valueOf(nodo);

int contador=0;
FileOutputStream fos = null;

Statement st = null;
ResultSet rs = null;

 

final String sql=” SELECT “+
“FROM(SELECT to_number (rownum) cuenta,”+
” p.
“+
” FROM “+
” ( “+
“SELECT c.nodo_co_nodo, “+
” c.infi_nu_infi, “+
” c.docu_co_documento, “+
” d.docu_no_documento, “+
” d.docu_no_extension ,”+
” d.docu_nu_version, “+
” d.DOCU_FX_MODIFICACION fecha,”+
” e.doct_do_documento doc “+
” FROM tablametadatos c, “+
tablaauxiliar d, “+
tabladocumentos e “+
” WHERE c.nodo_co_nodo = ” + nnodo +
” AND c.docu_co_documento =d.docu_co_documento “+
” AND d.docu_co_documento =e.docu_co_documento “+
” AND e.docu_nu_version =d.docu_nu_version “+
” AND c.docu_nu_version =e.docu_nu_version “+
” ) “+
” where (cuenta >”+topec1 + ” and cuenta <” +topex1 +” )”;

 

//abre fichero de log
File TextFile = new File(path1);
FileWriter TextOut = new FileWriter(TextFile, true);

System.out.println(“”);
System.out.println(“********************************************************************”);
System.out.println(“COMENZANDO PROCESO INICIAL”) ;
System.out.println(“sql general=”+sql);
System.out.println(“”);

TextOut.write(“********************************************************************\r\n”);
TextOut.write(“COMENZANDO PROCESO INICIAL\r\n”);
TextOut.write(“sql general=”+sql+”\r\n”);

try
{

st = cn.createStatement();
rs = st.executeQuery(sql);

while (rs.next())
{
++contador;
//para blobs
final String verdocu= rs.getString(“docu_no_documento”) ;
System.out.println(“Ndocu =” + verdocu );

final String verinfi =rs.getString(“infi_nu_infi”);
System.out.println(“Infi =” + verinfi );

final String verloc = rs.getString(“docu_co_documento”);
System.out.println(“Cdocc =” + verloc );

final String vernombre= verinfi+”“+verloc + ““+ verdocu;

System.out.println(“Nombre =” + vernombre );

System.out.println(“+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++”);
System.out.println(“Numero fichero a extraer =” + contador );

TextOut.write(“Numero fichero a extraer =” + contador+”\r\n”);

final String nfichero =vernombre; //LE PASAMOS COMO ARGUMENTO EL NOMBRE YA MONTADO:INFI_DOCU+LOC

System.out.println(” Fichero a extraer: ” +nfichero);
TextOut.write(“Fichero a extraer =” + nfichero+”\r\n”);

final String pathname= path + nfichero ;
System.out.println(” Extrayendo fichero multimedia : “+pathname);
TextOut.write(“Extrayendo fichero multimedia =” + pathname +”\r\n”);

//deberíamos comprobar si existe ese fichero
final File file = new File(pathname);

fos = new FileOutputStream(file);
final Blob bin = rs.getBlob(“doc”);
System.out.println(“…Extrayendo BLOB”);
TextOut.write(“Extrayendo BLOB \r\n”);

final InputStream inStream = bin.getBinaryStream();
final int size = (int)bin.length();
System.out.println(“Tamaño: “+size);
TextOut.write(“Tamaño =” + size+”\r\n”);

final byte[] buffer = new byte[size];
int length = -1;

while ((length = inStream.read(buffer)) != -1)
{
fos.write(buffer, 0, length);
}

if (fos != null)

{

//Intentamos cambiar los permisos al directorio creado
TextOut.write(“Fichero extraido ok \r\n”);

permisos777(pathname);
fos.close();
System.out.println(” Fichero extraido ok.”);
System.out.println(“+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++”);
TextOut.write(“+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\r\n”);

}
}
}
catch (final IOException ioe)
{
throw new IOException(ioe.getMessage());

}
finally
{
if (fos !=null)
{
fos.close();
TextOut.write(“FIN DEL PROCESO \r\n”);
TextOut.close();

}
if (rs !=null)
{
rs.close();

TextOut.close();
}
rs=null;
st=null;

 

 

System.out.println(“Fin RecuperaBD2”);

System.out.println(“FIN PROCESO”) ;

TextOut.close();

}
}

 

//funcion para añadir permisos 777 a los ficheros extraidos 
public static void permisos777 (final String pathname)
{

Process theProcess = null;

//intentamos cambiar los permisos del fichero recién creeado
System.out.println(“Cambiamos permisos 777 a “+pathname);
try
{
theProcess = Runtime.getRuntime().exec(“chmod 777 “+ pathname);
}
catch(IOException e)
{
System.out.println(“Error en el método exec()”);
}
//fin de cambio de permisos

}

}

//clase para gestionar las conexiones con la BBDD

class GestorDeConexiones
{
private final String user;
private final String password;
private Connection conn = null;
private boolean conectado = false;

public GestorDeConexiones(final String usr, final String pwd){
user = usr;
password = pwd;
}

public void closeConnection() throws SQLException{
if (conectado)
conn.close();
}

private void conectar() throws SQLException {

String url;
DriverManager.registerDriver(new OracleDriver());

url = “jdbc:oracle:thin:@x.x.x.x:yyy:cadena”; ///ojo PROD

 

conn = DriverManager.getConnection(url,user, password);
System.out.println(“Conexion correcta”);
conectado = true;

}

public Connection getConnection() throws SQLException
{
if (!conectado)
conectar();
return conn;
}
}

 

 

 

 

Anuncios

¿Cómo se puede probar la conectividad de Oracle?


Conectarse a una base de datos de Oracle mediante ODBC requiere dos cosas:

  • Un Nombre de servicio Oracle (conocido como un Alias en las versiones anteriores de Oracle 8.0) el cual define la ubicación (nombre del servidor y puerto) y la base de datos (instancia) para la conexión.
  • Un Origen de datos ODBC define simplemente el nombre del servicio debe utilizarse para la conexión ODBC.

Cada uno de estos elementos debe configurarse correctamente para que una conexión funcione  asi que cuando se producen problemas de conexión, es mejor probar cada parte de la conexión para determinar dónde reside el problema.

La utilidad TNSPING determina si se puede llegar con éxito al oyente de un servicio en una red de Oracle Net. Si puede conectarse con éxito de un cliente a un servidor (o de un servidor a otro servidor) utilizando la utilidad TNSPING, entonces muestra una estimación del tiempo de viaje de ida y vuelta (en milisegundos) que se necesita para alcanzar el servicio Oracle Net.

Si falla, entonces muestra un mensaje que describe el error que ocurrió. Esto le permite ver el error de red que está ocurriendo sin la sobrecarga de una conexión de base de datos.

Use el siguiente comando para probar la conectividad:

 tnsping net_service_name count

En el comando anterior, se utilizan los siguientes argumentos:

  • net_service_name debe existir en el archivo tnsnames.ora o en el servicio de nombres en uso, como NIS.
  • count determina cuántas veces el programa intenta alcanzar el servidor. Este argumento es opcional.

Si el nombre de servicio de red especificado es un nombre de base de datos, TNSPING intentará comunicarse con el oyente correspondiente.En realidad no determina si la base de datos se está ejecutando. Utilice SQL * Plus para intentar una conexión a la base de datos.

 

Probar el nombre del servicio Oracle

La utilidad TNSPING puede utilizarse para probar un nombre de servicio de Oracle. Uso:

1. Abra un símbolo del sistema (haga clic en Inicio, haga clic en Ejecutar, escriba cmd y, a continuación, haga clic en Aceptar).

2. Escriba tnsping < nombre servicio > (para Oracle 7.3 o Oracle 8i y versiones posteriores) o tnsping80 < nombre servicio > (para Oracle 8.0), y, a continuación, presione ENTRAR.

La utilidad de Ping TNS producirá un “Aceptar” o un mensaje de “Error de conexión”. En el caso de un “Error al conectar” del mensaje, una causa o motivo se incluirá (por ejemplo, “12545 de TNS: conexión fallado porque el objeto o el host de destino no existe”). Si falla el comando Ping TNS, hay un problema con el nombre del servicio Oracle. La configuración para el nombre del servicio debe comprobarse mediante el Ayudante Net de Oracle o el Asistente para configuración de red. DBA Oracle que necesite proporcionar la información de conexión correcta.

 

Probar el origen de datos ODBC

La utilidad de prueba ODBC de Oracle puede utilizarse para probar un origen de datos ODBC. Uso:

  1. Haga clic en Inicio, seleccione programas y, a continuación, seleccione la carpeta de programa de Oracle (el nombre de esta carpeta puede variar).
  2. En versiones anteriores de Oracle, el programa de prueba ODBC de Oracle puede mostrarse en la carpeta de programa de Oracle. Si es así, haga clic en él. Si no es así, seleccione la carpeta de Administración de la red y, a continuación, haga clic en Prueba ODBC de Oracle. Aparecerá una ventana de prueba ODBC de Oracle de 32 bits .
  3. Haga clic en el botón Conectar . Aparecerá el cuadro de diálogo Seleccionar origen de datos.
  4. Haga clic en la ficha Machine Data Source y, a continuación, seleccione el origen de datos que desea probar y haga clic en Aceptar.
  5. En el cuadro de inicio de sesión, escriba el Nombre de usuario y la contraseña en los cuadros apropiados y, a continuación, haga clic en Aceptar.

  6. Tras pulsar en Aceptar, el botón Conectar está disponible y se deben habilitar los botones Desconectar, Todas las tablas, Las tablas de usuarioy Execute . De lo contrario, se producirá un error.
  7. Si el Ping TNS tuvo éxito, pero se produce un error en la prueba de ODBC, hay un problema con el origen de datos ODBC o con el inicio de sesión y la contraseña proporcionados para realizar la conexión. Compruebe que ambos son correctos y vuelva a intentarlo.

¿Que se puede hacer para mejorar el rendimiento de las consultas en Oracle 11?


Tradicionalmente para mejorar el rendimiento de las consultas en SQL contra una BBDD Oracle , una vez optimizada la consulta con técnicas  de Tuneling ( por ejemplo usando Hints si procede) ,   siempre pasamos a dos técnicas clásicas :

  • Reconstruyendo los  índices existente en base de datos ,para lo cual previamente comprobaremos si esos índices deberían de ser reconstruidos  
  • Actualizando estadísticas sobre las tablas que realizamos las consultas.

Veamos mas despacio de que estamos hablando: 

 

Reconstrucción de indices

El índice de una base de datos es una estructura de datos que mejora la velocidad de las operaciones, permitiendo un rápido acceso a los registros de una tabla por lo que se suelen usar sobre aquellos campos sobre los cuales se vayan a realizar búsquedas frecuentes dado que su  funcionamiento es similar al índice de un libro: guardando duplas de elemento que se desea indexar junyo a su posición en la base de datos, de modo que para buscar un elemento que esté indexado, sólo necesitamos que buscar en el índice de dicho elemento para, una vez encontrado, devolver el registro que se encuentre en la posición marcada por el índice.

Los índices pueden ser creados usando una o más columnas, preparando la base de datos tanto para búsquedas rápidas al azar como para ordenaciones eficientes de los registros.

Los índices son construidos sobre árboles B, B+, B* o sobre una mezcla de ellos, funciones de cálculo u otros métodos.

El espacio en disco requerido para almacenar el índice es típicamente menor que el espacio de almacenamiento de la tabla (puesto que los índices generalmente contienen solamente los campos clave de acuerdo con los que la tabla será ordenada, y excluyen el resto de los detalles de la tabla), lo que da la posibilidad de almacenar en memoria los índices de tablas que no cabrían en ella. En una base de datos relacional un índice es una copia de parte de una tabla.

La siguiente consulta SQL mostrará el tamaño en megabytes de un índice determinado, en nuestro caso del índice PK_FACTURACION_CODIGO perteneciente a la tabla FACTURACION y el campo CODIGO del ejemplo. La consulta SQL para mostrar el tamaño ocupado por un índice es la siguiente:

select segment_name, sum(bytes)/1024/1024 MB
from dba_extents
where segment_name = ‘INDICE DE EJEMPLO’
group by segment_name

 

 

Es importante periódicamente examinar y determinar qué índices son susceptibles de ser reconstruidos. Cuando un índice está descompensado puede ser porque algunas partes de éste han sido accedidas con mayor frecuencia que otras dando como resultado problemas de contención de disco o cuellos de botella en el sistema.

Normalmente reconstruimos un índice con el comando ALTER INDEX  (esta sentencia se utiliza para cambiar o reconstruir un índice existente en la base de datos).

Para poder ejecutar este comando el índice debe de estar en el propio esquema donde intentes ejecutarlo o deberías de tener el privilegio alter any index. También tenemos que tener en cuenta que para realizar la reconstrucción de un índice deberíamos de tener cuota suficiente sobre el tablespace que lo lanzamos.

Para reconstruir un índice bastaría con lazar la siguiente sentencia

ALTER INDEX <index_name> REBUILD;

Para reconstruir una partición de un índice podríamos hacer lo siguiente

ALTER INDEX <index_name> REBUILD PARTITION <nb_partition> NOLOGGING;

Nota: En algunos casos cuando alguno de los índices tiene algún tipo de corrupción no es posible reconstruirlo. La solución en este caso es borrar el índice y recrearlo

 

 

 

Actualización de estadísticas

Cuando una base de datos Oracle recibe una sentencia “SQL” para resolver una consulta, se llevan a cabo diversas acciones para lograr la entrega del resultado.

Dentro de los diversos pasos uno de los más importantes es el llevado a cabo por el optimizador basado en costos “Cost Based Optimizer o CBO”. Para que el “CBO” pueda determinar de forma exacta el plan de ejecución de para un “SQL Query” debe disponer de la información de las estadísticas de las tablas e índices que participan en el “SQL Query”, esta información comúnmente es conocida como “Optimizer statistics” “Estadisticas del optimizador”, la misma describe como esta compuesto y distribuido internamente el objeto.

Estas estadísticas son utilizadas por el optimizador para elegir el mejor plan de ejecución para cada instrucción SQL.

El tiempo necesario para colectar las estadísticas en algunos casos puede ser de gran medida. En el manejador se pueden utilizar diversos métodos para tratar de reducir el tiempo de esta tarea en la mayor proporción posible.

 

Es importante tener por tanto actualizadas las estadísticas de la base de datos. Para saber si las estadísticas se están lanzando correctamente podemos hacer una consulta sobre la tabla ALL_INDEXES en oracle 11  (en Oracle 10  es  dba_indexes )y ver el campo last_analyzed para observar cuando se ejecutaron sobre ese índice las estadísticas.

Nota: la columna “LAST_ANALYZED” la cual puede ser encontrada en vistas tales como: “DBA_TABLES”, “DBA_INDEXES”, “DBA_TAB_COL_STATISTICS” indica la fecha en que fue calculada la estadística para dicho objeto por ultima vez.

 

Como ejemplo  , si queremos  saber cuando fue la ultima vez que se ejecutaron estadísticas sobre todas las tablas perteneciente a un determinado esquema de BBDD  lanzaremos la siguiente consulta:

SELECT LAST_ANALYZED,table_name FROM ALL_INDEXES ;

 

 

Como vemos con las fechas podemos  hacernos una idea , de lo actualizado que están las estadisticas   sobre cada tabla   

Para actualizar las estadísticas de forma global para  un  esquema de BBDD,  podemos utilizar  el paquete DBM_STATS  de la la siguiente forma:

Execute DBMS_STATS.gather_schema_stats(‘Esquema’);

 

Una vez actualizadas las estadísticas de los índices de la base de datos lanzamos la siguiente consulta:

SELECT LAST_ANALYZED,table_name FROM ALL_INDEXES ;
SELECT index_name, blevel,
DECODE(blevel,0,'OK BLEVEL',1,'OK BLEVEL',2,
'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
FROM dba_indexes where table_owner='DBPROD08';

 

Con esta sentencia obtendremos el nombre nombre del índice, el blevel y si es correcto este indice.

Los índices que deberíamos de reconstruir son los que en la columna ok aparecen como BLEVEL HIGH.

Blevel (branch level) es parte del formato del B-tree del índice e indica el número de veces que ORACLE ha tenido que reducir la búsqueda en ese índice. Si este valor está por encima de 4 el índice debería de ser reconstruido.

.

Diferencias entre DBA_FREE_SPACE y DBA_TABLESPACE_USAGE_METRICS


Resultado de imagen de tablespaces

 

 

He estado durante  muchísimo  tiempo  realizando consultas nivel de ocupación de los tablespaces en Oracle 9  que utilizan las vistas del sistema DBA_FREE_SPACE y DBA_DATA_FILES   , lo cual esta muy bien,  pero recientemente he sabido que  hay personas que  en  Oracle 11  están usando  otra vista del sistema para conocer  el  nivel de ocupación de los tablespaces   que es muy rápida: DBA_TABLESPACE_USAGE_METRICS .

Esta  vista contiene información sobre espacios de tablas permanentes, temporales y de deshacer, y tiene solo cuatro columnas:

  • TABLESPACE_NAME TABLESPACE_NAME
  • USED_SPACE ESPACIO USADO
  • TABLESPACE_SIZE TABLESPACE_SIZE
  • USED_PERCENT USED_PERCENT

Hay tres cosas importantes acerca de esas columnas:

  1. Los números de USED_SPACE y TABLESPACE_SIZE están en bloques , no en bytes, por lo que necesitamos unir   esta vista a la vista del sistema DBA_TABLESPACES para terminar con números en bytes. ( ellos la ofrecen  tal cual sin hacer la  conversión a  bytes , de modo que  lo que nos están dando son el nº de bloques y no el de bytes)
  2. La columna TABLESPACE_SIZE muestra el tamaño total posible , no el tamaño actual  
  3. USED_PERCENT muestra el uso del tamaño total posible de un espacio de tabla.

Es decir ,    la información de esta vista no es la que muestra el tamaño real , sino el posible  tamaño   máximo lo cual  explica  la gran diferencia   que nos encontraremos  entre  cálculos realizados sobre la vista  DBA_FREE_SPACE   o sobre la  vista  DBA_TABLESPACE_USAGE_METRICS

Algo muy importante es que la vista  DBA_TABLESPACE_USAGE_METRICS nos ofrece la volumetría en bloques lo cual no es muy útil , por lo que para traducirlo a bytes  tendremos que saber  el tamaño del bloque . Con esta consulta podemos ver el tamaño del bloque:

show parameter block_size

Ahora , lo mejor es hacer la conversión  usando el valor de Block_size de modo que obtengamos  en la  consulta el  tamaño de los Tablespaces  en MB y no en bloques

select * from(
SELECT
a.tablespace_name,
ROUND((a.used_space * b.block_size) / 1048576, 2) AS “Used space (MB)”,
ROUND((a.tablespace_size * b.block_size) / 1048576, 2) AS “Tablespace size (MB)”,
ROUND(a.used_percent, 2) AS “Used %”
FROM dba_tablespace_usage_metrics a
JOIN dba_tablespaces b ON a.tablespace_name = b.tablespace_name) where TABLESPACE_NAME LIKE ‘%EJEMPLO_TB%’

 

También podemos usar el método “antiguo ” basado en las vista   DBA_FREE_SPACE  uniéndolas con las vistas DBA_DATA_FILES  y s.DBA_TABLESPACES  tanto en oracle 9, 10 y 11:, 

select ‘EJEMPLO2_TB’, usados, total ,100*usados/total used_percent,libre from

(select sum(total)total , sum(usados) usados, sum(libre) libre

from (
Select t.tablespace_name Tablespace, t.status Estado,
ROUND(MAX(d.bytes)/1024/1024,2) Total,
ROUND((MAX(d.bytes)/1024/1024) –
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) Usados,
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) Libre,
t.pct_increase “% incremento”,
SUBSTR(d.file_name,1,80) “Fichero de datos”
FROM  sys.DBA_FREE_SPACE f, sys.DBA_DATA_FILES d, sys.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 )

where Tablespace=’EJEMPLO2_TB’)

 

 

El resultado de ambos consultas sobre la  vista    DBA_TABLESPACE_USAGE_METRICS  o sobre la  vista  DBA_FREE_SPACE  con datos de ejemplo   las podemos ver reflejados en la siguiente tabla:

TABLESPACE_NAME Used space (MB) Tablespace size (MB) Usado % Esta consulta usa dba_tablespace_usage_metrics
dba_tablespaces
EJEMPLO1_ 454310 572776,94 79,3
  234853 295259,47 79,5
EJEMPLO2 454246 459200,94 98,9 Esta consulta  solo usa dba_fee_space
  234853,3 241833,53 97,1

Es decir,   en los cálculos anteriores    teniendo en cuanta  que la vista  dba_tablespace_usage_metric solo muestra el tamaño total posible ,  como vemos como   la información anterior , solo nos están dando  la volumetría  máxima   posible  y no la real dba_tablespace_usage_metrics

Como  puede  apreciar querido lector en este ejemplo las diferencias son  notables  entre los resultados arrojados por  ambas consultas   :para el caso del primer da una ocupación del 79’3% para el  TB de datos  y 79,5 % de ocupación  para los  TB de los indices  usando la vista dba_tablespace_usage_metric, lo cual da resultados esperanzadores ( aun quedaría  mas del 20% por ocupar) mientras en el segundo caso la query  da una ocupación del 98’9 para el  TB de datos  y 97,1 % de ocupación  para los  TB de los indices  usando la vista dba_fee_space, lo cual son resultados  reales   bastantes alejados de los resultados obtenidos en la primer vista

 

 

 

Espero que encuentre útil la consulta. Aquí está el enlace de la documentación de Oracle sobre la vista:  Oracle Documentation – DBA_TABLESPACE_USAGE_METRICS

 

Como crear un tunel ssh para acceder a una BBDD Oracle


En una presentación de krisrice y thatjeffsmith en KScope15, Jeff señaló algunas nuevas características, incluyendo una para crear túneles SSH usando el programa gratuito que ofrece Oracle para manejar cualquier BBDD ORacle :  el   SQL Developer.

SQL Developer es un entorno de desarrollo de uso general (y gratuito) para la base de datos Oracle. Esta característica parece que debería funcionar independientemente de si realmente desea conectarse a una base de datos Oracle

Sí, puede crear túneles SSH de la forma tradicional utilizando su cliente de línea de comandos SSH favorito   tambien deberia poderse hacer de un ambiente mas potente   como es el entorno de SQL Developer. Este post no es un pros / contras, es sólo una introducción a la función.

Si usted necesita a veces crear túneles de SSH  para lanzar  SQL entonces usted puede encontrar esto interesante.

En esta  introducción se hacen los siguientes supuestos::

  • Tiene un servidor remoto que tiene SSH en ejecución y admite la autenticación basada en claves.
  • Ya tiene sus claves públicas y privadas SSH generadas y correctamente instaladas en el servidor remoto.
  • Debe enviar las solicitudes a cualquier servicio que se ejecute en un puerto del servidor remoto que pueda estar bloqueado por un servidor de seguridad o, de lo contrario, sólo es accesible una vez que haya iniciado sesión en ese servidor remoto. Para este último punto,  se supone que se cuenta con una instancia de una base de datos Oracle que se ejecuta detrás de un firewall en un servidor remoto y el único puerto accesible a los hosts externos es el puerto TCP 22 (predeterminado para SSH). Usando un túnel SSH con reenvío de puertos, puede obtener SQL Developer para arriba y corriendo en su PCl para que pueda conectarse a su instancia de base de datos remota de Oracle. 

Aquí está el  modo  de hacerlo:

  • En primer lugar, si no lo tiene todavía tendrá que descargar SQL Developer versión 4.1 o posterior (en la versión 4.0  esta característica aún no estaba disponible)

 

  • A continuación, abra SQL Developer y elija View > SSH que abrirá el panel de conexiones SSH.
  • En el panel SSH, haga clic con el botón derecho en Hosts SSH y seleccione Nuevo host SSH …
  • Rellene el cuadro de diálogo con sus datos de conexión según sea necesario y pulse OK. Nota: si especifica el puerto incorrecto (como lo hice) en el servidor remoto o desea especificar el puerto TCP para enlazar sus conexiones locales, puede editar las conexiones posteriormente.

 

 

 

  • En este ejemplo,se  esta utilizando claves SSH para conectarse al servidor remoto. Esta es la práctica recomendada y también eliminará el paso de tener que ingresar una contraseña en el futuro. Nota: La clave que especifique en este cuadro de diálogo debe ser su clave privada y su clave pública ya debe estar configurada en el servidor remoto con los permisos correctos para que esto funcione correctamente.
  • El primer puerto especificado es el puerto del servicio (Oracle DB) que se ejecuta en el servidor remoto y el segundo (puerto local) puede ser cualquier puerto disponible en su máquina que desee utilizar. Este túnel enviará el tráfico de su máquina en el puerto local al servidor remoto. En este caso, probablemente recomendaría usar el mismo puerto que el servidor remoto a menos que tenga varios túneles ejecutándose a servidores remotos al mismo tiempo.

 

  • En el panel SSH debe aparecer su nueva conexión. Haga clic con el botón derecho en la conexión y elija Connect .
  • En este punto, si su conexión tiene éxito usted no necesariamente recibe ninguna retroalimentación positiva. Puede comprobar si está conectado al pasar el ratón sobre el túnel SSH con su ratón o haciendo clic con el botón Disconnect en él de nuevo para ver si se le pide que Disconnect .
  • Por último, debe tener un túnel SSH que se ejecute en su máquina local que puede utilizar para acceder a su base de datos remota de Oracle mediante la conexión a localhost:<local port> que reenvía su solicitud de conexión y todos los otros SQL Developer TCP tráfico Al servidor remoto en el puerto TCP que ha especificado. Excepto para el nombre de host y el número de puerto TCP, debe especificar todos los demás detalles de conexión como de costumbre.

 

 

Desde  luego era  una característica muy demandada por la comunidad de usuarios de BBDD Oracle que necesitan conectarse  en un ambiente de host .  Espero que haya encontrado esta visión general de la creación de túneles SSH en Oracle SQL Developer  útil y que le ayude en el camino.