Imagina que tienes una alerta de disco lleno.
Imagína ahora que tienes varios tablespaces que tienen mucho espacio libre, pero cuando intentas reducir los ficheros del tablespace, descubres que hay muchos segmentos colocados en los últimos bloques del archivo.
Quizá intentes mover el último segmento del archivo a otro tablespace.
Aquí está te dejo la sentencia:
select file_id, owner, segment_name, segment_type from dba_extents where tablespace_name='&&TABLESPACE_NAME' and (FILE_ID,BLOCK_ID) = (select file_id, max(block_id) from dba_extents where tablespace_name='&TABLESPACE_NAME' group by file_id);
Pero si deseas mover todos los segmentos a un nuevo tablespace vacío, para «desfragmentarlo», tienes que moverlo segmento a segmento.
Yo suelo utilizar sentencias SQL ad hoc con el comando que quiero propagar, como este ejemplo tonto:
select 'alter package '||owner||'.'||object_name||' compile;' sentencia from dba_objects where status='INVALID' and object_type ='PACKAGE BODY';
Por supuesto, en realidad usaría utlrp.sql , pero así ves el ejemplo.
Entonces, como cada tipo de segmento/objeto tiene su propia sintaxis para la operación de «move», hay que adaptar este script al tipo de segmento que queremos mover. Hay que tener en cuenta que las tablas, los índices y los lobs pueden particionarse y aquí, de nuevo, la sintaxis también es diferente.
Necesitamos una sintaxis diferente para mover una tabla sin particiones, una partición de tabla, un índice sin particiones, una partición de índice, un segmento lob sin particiones y una partición lob.
A lo largo de los años, utilicé un script para generar todas esas sentencias SQL automáticamente y hace poco me tocó terminar «manualmente» una desfragmentación de tablespace fallida hecha desde Oracle Enterprise Manager Cloud Control y la opción para Reorganización de tablespace.
Así que me vino a la mente una película donde el dueño de una fábrica le está mostrando a su hijo cómo funciona la fábrica. Un empleado describe con detalle cada paso de la fabricación, como si fuera algo súper complicado, pero el padre le explica «No te preocupes, como nosotros automatizamos la fábrica, ahora todo se hace automáticamente» y el empleado enfadado les dice: «Pero qué pasa si falla el ‘automático’? ¿Quién está aquí para darle al ‘automático’?«.
Pues bien, si ejecutas un proceso automático para desfragmentar, con Enterprise Manager o cualquier otra herramienta, o si tienes que hacerlo manualmente por cualquier motivo, mi script está en el GitHub de Café Database aquí:
https://github.com/CafeDatabase/defrag_tablespace
Aquí encontrarás dos scripts:
Uno es defragmenta_tablespace.sql, y puedes establecer los valores de ORIGIN, SOURCE y INITIAL para el tamaño inicial del segmento en la sección de definición (define), o aceptar los parámetros de entrada en la ejecución de sqlplus: elije las tres líneas de código «define» o las tres «accept» y comenta las restantes:
-- define tablespace_origen=ORIGIN -- define tablespace_destino=SOURCE -- define t_extension_inicial=1 accept tablespace_origen prompt 'Tablespace source: ' accept tablespace_destino prompt 'Tablespace target: ' accept t_extension_inicial default '1' prompt 'Size of initial segment extent [default 1M]:'
El script visualizará la secuencia de comandos para mover tres tipos de segmentos: tablas, índices y segmentos lob, con una sintaxis específica para los particionados, y te recomiendo adaptar la secuencia de comandos de la siguiente manera:
- Puedes añadir o no la cláusula «update indexes» en la sección de tablas/particiones. Aumentará el tiempo de ejecución del move, pero es algo obligatorio si hay que ejecutar este script mientras otros usuarios están en línea.
- También puedes permitir la ejecución del procedimiento «rebuild_unusable» para cada move o dejarlo comentado. Esto hará que las tablas se muevan más rápido y es posible que algunos de los índices se reconstruyan más adelante en la sección de reconstrucción de índices si están en el mismo tablespace que la tabla. Entonces es más rápido mantenerlo comentado y reconstruirlo todo más tarde, sobre todo teniendo en cuenta que en este caso no hay ningún usuario usando las tablas.
- Ajusta o elimina la cláusula para PARALLEL. Si el entorno es todo suyo y tienes muchas CPU a mano, PARALLEL acelerará la reorganización a otro tablespace. Si conoces un buen valor para el paralelismo como 120 (suponiendo que tengas 120 CPU para tí) o algo así, este es el lugar para configurarlo.
- Igual que NOLOGGING, pero luego hay que asegurarse de volver a definir todos los segmentos como LOGGING una vez termine la reorganización y lanzar una copia de seguridad física.
El otro script es rebuild_unusable.sql y estoy seguro de que ya sabes lo que hace.
Sí, reconstruye todos los índices que estén en estado inutilizable.
Aquí puedes adaptar la secuencia de comandos de la siguiente manera:
- Puedes ajustar o eliminar la cláusula PARALLEL. Deja el paralelismo automático o utiliza un valor fijo. Eso a tu gusto.
- La cláusula de REBUILD ONLINE: Si el entorno es todo tuyo, no necesitas agregar esta cláusula para hacer que esta reconstrucción bloquee partes pequeñas de los segmentos de índice y evitar bloqueos a los usuarios. Si no hay nadie más conectado a la base de datos o estás en una sesión restringida, no necesitas usar esta cláusula.
Por último, asegúrate de crear este procedimiento como usuario de DBA para no tener problemas al consultar objetos de otros esquemas, y todo debería funcionar bien.