Dba free space frag

Fiche express
Type	Script SQL*Plus de diagnostic
Domaine	Administration (fragmentation d'espace)
Voir aussi	Dba file space usage · Dba db growth

Dba_free_space_frag mesure la fragmentation de l'espace libre au sein de chaque fichier de données, en s'appuyant sur DBA_FREE_SPACE. Il crée temporairement une vue de calcul (free_space) exposant, par fichier, le nombre de « morceaux » d'espace libre, ainsi qu'un indice de fragmentation (FSFI — Free Space Fragmentation Index) qui combine la taille du plus grand morceau contigu et le nombre total de morceaux.

Un espace libre très fragmenté (beaucoup de petits morceaux plutôt qu'un ou deux gros) peut empêcher l'allocation d'un extent de grande taille même si l'espace libre total semble suffisant, et provoquer une erreur ORA-01653/ORA-01654 malgré un tablespace apparemment non plein.

Doit être exécuté connecté AS SYSDBA (accès nécessaire à DBA_FREE_SPACE et création d'une vue).

Script

CONNECT / AS SYSDBA

SET TERMOUT OFF
COLUMN current_instance NEW_VALUE current_instance NOPRINT
SELECT RPAD(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON

PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Rapport   : Fragmentation de l'espace libre                            |
PROMPT | Instance  : &current_instance                                         |
PROMPT +------------------------------------------------------------------------+

CREATE OR REPLACE VIEW free_space (
    tablespace
  , pieces
  , free_bytes
  , free_blocks
  , largest_bytes
  , largest_blks
  , fsfi
  , data_file
  , file_id
  , total_blocks
)
AS
SELECT
    a.tablespace_name
  , COUNT(*)
  , SUM(a.bytes)
  , SUM(a.blocks)
  , MAX(a.bytes)
  , MAX(a.blocks)
  , SQRT(MAX(a.blocks) / SUM(a.blocks)) * (100 / SQRT(SQRT(COUNT(a.blocks))))
  , UPPER(b.file_name)
  , MAX(a.file_id)
  , MAX(b.blocks)
FROM
    dba_free_space a
  , dba_data_files b
WHERE
    a.file_id = b.file_id
GROUP BY
    a.tablespace_name, b.file_name
/

CLEAR COLUMNS

SET ECHO      OFF
SET FEEDBACK  OFF
SET HEADING   ON
SET LINESIZE  180
SET PAGESIZE  50000
SET VERIFY    OFF

BREAK ON tablespace SKIP 2 ON report

COMPUTE SUM OF total_blocks ON tablespace
COMPUTE SUM OF free_blocks  ON tablespace
COMPUTE SUM OF free_blocks  ON report
COMPUTE SUM OF total_blocks ON report

COLUMN tablespace    HEADING 'Tablespace'    FORMAT a30
COLUMN file_id       HEADING 'N° fichier'    FORMAT 99999
COLUMN pieces        HEADING 'Morceaux'      FORMAT 9999
COLUMN free_bytes    HEADING 'Octets libres'
COLUMN free_blocks   HEADING 'Blocs libres'  FORMAT 999,999,999
COLUMN largest_bytes HEADING 'Plus gros bloc contigu (octets)'
COLUMN largest_blks  HEADING 'Plus gros bloc contigu (blocs)' FORMAT 999,999,999
COLUMN data_file     HEADING 'Fichier'       FORMAT a75
COLUMN total_blocks  HEADING 'Total blocs'   FORMAT 999,999,999

SELECT
    tablespace
  , data_file
  , pieces
  , free_blocks
  , largest_blks
  , file_id
  , total_blocks
FROM
    free_space
/

DROP VIEW free_space
/

Colonnes / sortie

• Morceaux : nombre d'extents libres distincts dans le fichier — un chiffre élevé signale une fragmentation importante.
• Plus gros bloc contigu : taille du plus grand extent libre disponible d'un seul tenant — c'est cette valeur qui détermine si une opération demandant un gros extent (import, création d'index, réorganisation) pourra aboutir.
• FSFI (colonne calculée dans la vue, non affichée par défaut dans la requête finale) : indice combinant les deux informations précédentes ; plus il est proche de 100, moins l'espace libre est fragmenté. Il peut être ajouté à la liste de colonnes sélectionnées si besoin.

Voir aussi

• Dba file space usage — occupation globale de chaque fichier (complémentaire, sans le détail de fragmentation).
• Dba db growth — tendance de croissance de la base dans le temps.