Reclamable Space on Oracle Tables script

Cuando tenemos actividades en la base de datos, con tipo de transacciones que involucran  DML’s del tipo UPDATE, DELETE, derivara con el tiempo realizar tareas de mantenimiento y  reorganización de datos.

De esta manera la TABLA se ira degradando.

La acción requerida, sera compactarla, reorganizarla.

Parte de nuestra tarea consiste en averiguar las TABLAS candidatas a ser reorganizadas, con el método SHRINK.

Por ello, es necesario revisar y contar con un listado.

Ejecutar el siguiente query

*Agradecemos a Lucas Juarez por compartirlo.

set line 200
set pages 200
col OWNER format a10
    select 
	owner,
	table_name,
	mb_total,
	mb_usado,
	mb_total-mb_usado mb_reclamable,
	round((mb_total-mb_usado)/mb_total*100,2) "%_RECLAMABLE"
		from (select owner,table_name,round((blocks*8)/1024,2) mb_total, round((num_rows*avg_row_len/1024)/1024,2) mb_usado
					from dba_tables
						where owner not in ('ANONYMOUS','APEX_PUBLIC_USER','APEX_030200','APPQOSSYS','BI','CTXSYS',
                       'DBSNMP','DIP','DMSYS','EXFSYS','HR','IX','PUBLIC','MDSYS','ORACLE_OCM',
                       'LBACSYS','MDDATA','MDSYS','MGMT_VIEW','ODM','ODM_MTR','OE','OLAPSYS','ORDDATA',
					   'ORDPLUGINS','ORDSYS','OUTLN','PM','PERFSTAT','TSMSYS','SCOTT','SH','SI_INFORMTN_SCHEMA',
					   'SPATIAL_CSW_ADMIN_USR','OWF_MGR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','TRACESRV',
					   'MTSSYS','OWBSYS_AUDIT','WEBSYS','WMSYS','XDB')
		and blocks <> 0 )
			where ( (mb_total > 10   and mb_total < 50 and round((mb_total-mb_usado)/mb_total*100,2) > 50 ) 
			or (mb_total > 50   and mb_total < 200 and round((mb_total-mb_usado)/mb_total*100,2) > 40 )
			or (mb_total > 200 and mb_total < 500 and round((mb_total-mb_usado)/mb_total*100,2) > 30 )
			or (mb_total > 500 and round((mb_total-mb_usado)/mb_total*100,2)  > 15 ) )
		order by mb_total desc;

Como resultado podemos obtener una salida como la siguiente:

OWNER       TABLE_NAME                       MB_TOTAL   MB_USADO MB_RECLAMABLE %_RECLAMABLE
----------- ------------------------------ ---------- ---------- ------------- ------------
REAL_STATE  REAL_STATEPEDIDOSUCURSALITEM     12469.59    7976.53       4493.06        36.03
REAL_STATE  REAL_STATEPEDIDOITEMDETALLE      10319.11    6381.22       3937.89        38.16
REAL_STATE  VENTASUCURSALITEMPROMEDIO         5592.85    3742.67       1850.18        33.08
REAL_STATE  DISPONIBLEITEMDETALLE             5196.11    3827.21        1368.9        26.34
REAL_STATE  REAL_STATEPEDIDOSUCURSALITEM_X    4126.63    3304.58        822.05        19.92
REAL_STATE  STOCK                             1468.77     590.96        877.81        59.76
REAL_STATE  STOCKDISPONIBLE                    414.56     217.03        197.53        47.65
REAL_STATE  REAL_STATECOMPRAITEM                278.1     171.36        106.74        38.38
REAL_STATE  RS_REPRESENTATIVA                   55.25      16.02         39.23           71
REAL_STATE  RS_DISTRIBUCIONESP                  15.73        .04         15.69        99.75
REAL_STATE  RS_ARTICULOUNIDADENVIO              15.73        .38         15.35        97.58
REAL_STATE  PARAMETRODESCRIPCION                15.73          0         15.73          100

Con la lista propuesta, procedemos a la ejecución del SHRINK.