Estadísticas de un tablespace Oracle

En muchas ocasiones del día a día de un dba se hace necesario obtener información acerca del uso de un tablespace. A mi al menos me sucede muy a menudo. Una forma de hacerlo es mediante el grid (EMGC), para aquellos que tengan licencia. Otra forma es mediante consultas diversas vistas de la base de datos Oracle. Como todo en la vida, cada opción tiene sus ventajas y sus inconvenientes. Yo soy partidario de la segunda opción; hacer las consultas a través de la línea de comandos. Va a ser más rápido (si se sabe como hacerlo bien) y es mucho más flexible. Y se aprende bastante más.

Hoy pretendo compartir un procedimiento PL/SQL para obtener estadísticas de uso de un tablespace dado.

El funcionamiento es sencillo. Lo ideal es generar un fichero sql para lanzarlo desde sqlplus como usuario SYSTEM. Al ejecutarlo, el script pide el nombre del tablespace que se quiere analizar, y nos devuelve la siguiente información:

  • Tamaño asignado, ocupado y libre (en Mb), así como el porcentaje de tamaño disponible.
  • Nombre de cada uno de los datafiles que componen el tablespace.
  • Top 10 de los segmentos más voluminosos.

Este es el código:

set lines 150
set pages 100
set timing on
set verify off
set serveroutput on

col file_name format a60
col segment_name format a40

ACCEPT tablespace prompt 'Tablespace : '

DECLARE
 mb_asignado NUMBER;
 mb_ocupado NUMBER;
 mb_libre NUMBER;
 por_libre NUMBER;
 tamano NUMBER;
 TB constant varchar2(1):=CHR(9);

 CURSOR v_fichero is SELECT * FROM dba_data_files WHERE tablespace_name='&tablespace';
 fichero v_fichero%ROWTYPE;

 CURSOR v_segmento is SELECT * FROM (SELECT segment_name,sum(bytes)/(1024*1024) Mb from dba_segments WHERE tablespace_name='&tablespace' GROUP BY segment_name ORDER BY Mb DESC) WHERE rownum<=20;
 segmento v_segmento%ROWTYPE;

BEGIN
 dbms_output.put_line ('.');
 dbms_output.put_line ('.');
 dbms_output.put_line ('.');
 dbms_output.put_line ('.');
 dbms_output.put_line ('# Estadísticas del tablespace &tablespace.');
 dbms_output.put_line ('.');
 select sum(bytes)/(1024*1024) into mb_asignado from dba_data_files where tablespace_name='&tablespace';
 dbms_output.put_line (to_char('# Espacio asignado (Mb) --> '||round(mb_asignado,2)));
 select sum(bytes)/(1024*1024) into mb_ocupado from dba_segments where tablespace_name='&tablespace';
 dbms_output.put_line (to_char('# Espacio ocupado (Mb) --> '||round(mb_ocupado,2)));
 mb_libre:=mb_asignado-mb_ocupado;
 por_libre:=round(((mb_libre*100)/mb_asignado),2);
 dbms_output.put_line (to_char('# Espacio libre (Mb) --> '||round(mb_libre,2)));
 dbms_output.put_line (to_char('# porcentaje libre --> '||por_libre||'%'));
 dbms_output.put_line ('.');
dbms_output.put_line ('# Datafiles de &tablespace.');
 dbms_output.put_line ('.');
 dbms_output.put_line ('# '||rpad('Nombre del datafile',60,' ')||TB||rpad('Tamaño (Mb)',12,' '));
 dbms_output.put_line ('# '||rpad('-',60,'-')||TB||rpad('-',11,'-'));
 open v_fichero;
 loop
     fetch v_fichero into fichero;
     exit when v_fichero%NOTFOUND;
     tamano:=fichero.bytes/(1024*1024);
     dbms_output.put_line('# '||rpad(fichero.file_name,60,' ')||TB||lpad(tamano,11,' '));
 end loop;
 close v_fichero;
 dbms_output.put_line ('.');

 dbms_output.put_line ('# Segmentos más voluminosos de &tablespace.');
 dbms_output.put_line ('.');
 dbms_output.put_line ('# Segmento Tamaño (Mb)');
 dbms_output.put_line ('# ---------------------------------------- -----------');
 open v_segmento;
 loop
     fetch v_segmento into segmento;
     exit when v_segmento%NOTFOUND;
     dbms_output.put_line('# '||rpad(segmento.segment_name,40,' ')||TB||lpad(round(segmento.Mb,2),7,' '));
 end loop;
 close v_segmento;
END;
/

One Response to Estadísticas de un tablespace Oracle

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: