Dba tablespaces

Fiche express
Type	Script SQL*Plus de diagnostic
Domaine	Administration
Voir aussi	Dba tablespaces 7 · Dba tablespaces 8 · Dba top segments

Dba tablespaces liste tous les tablespaces d'une instance avec leur taille, l'espace utilisé et le pourcentage d'occupation. Conçu pour Oracle9i et plus récent, il gère aussi bien les tablespaces classiques (extents locaux ou par dictionnaire) que les vrais tablespaces TEMPORARY basés sur des tempfiles.

Pour les instances plus anciennes, voir les variantes Dba tablespaces 7 (Oracle7/8) et Dba tablespaces 8 (Oracle8i).

Script

Le script combine deux requêtes en UNION ALL : la première couvre les tablespaces permanents (via dba_data_files / dba_free_space), la seconde les tablespaces temporaires basés sur des tempfiles (via dba_temp_files / v$temp_extent_pool), ce que les versions plus anciennes du script ne savent pas faire.

-- Rapport sur les tablespaces (taille, utilisation, % occupé)
-- Compatible Oracle9i et plus récent (gère les tempfiles TEMPORARY)

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 | Report   : Tablespaces                                                 |
PROMPT | Instance : &current_instance                                           |
PROMPT +------------------------------------------------------------------------+

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN status      FORMAT a9                 HEADING 'Status'
COLUMN name        FORMAT a30                HEADING 'Tablespace Name'
COLUMN type        FORMAT a15                HEADING 'TS Type'
COLUMN extent_mgt  FORMAT a10                HEADING 'Ext. Mgt.'
COLUMN segment_mgt FORMAT a10                HEADING 'Seg. Mgt.'
COLUMN ts_size     FORMAT 9,999,999,999,999  HEADING 'Tablespace Size'
COLUMN used        FORMAT 9,999,999,999,999  HEADING 'Used (in bytes)'
COLUMN free        FORMAT 9,999,999,999,999  HEADING 'Free (in bytes)'
COLUMN pct_used    FORMAT 999                HEADING 'Pct. Used'

BREAK ON report

COMPUTE sum OF ts_size  ON report
COMPUTE sum OF used     ON report
COMPUTE sum OF free     ON report
COMPUTE avg OF pct_used ON report

-- Tablespaces permanents
SELECT
    d.status                                            status
  , d.tablespace_name                                   name
  , d.contents                                          type
  , d.extent_management                                 extent_mgt
  , d.segment_space_management                          segment_mgt
  , NVL(a.bytes, 0)                                     ts_size
  , NVL(a.bytes - NVL(f.bytes, 0), 0)                   used
  , NVL(f.bytes, 0)                                     free
  , NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used
FROM
    sys.dba_tablespaces d
  , ( SELECT tablespace_name, SUM(bytes) bytes
      FROM dba_data_files
      GROUP BY tablespace_name
    ) a
  , ( SELECT tablespace_name, SUM(bytes) bytes
      FROM dba_free_space
      GROUP BY tablespace_name
    ) f
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND NOT (
        d.extent_management LIKE 'LOCAL'
    AND d.contents           LIKE 'TEMPORARY'
      )
UNION ALL
-- Tablespaces TEMPORARY (tempfiles)
SELECT
    d.status                          status
  , d.tablespace_name                 name
  , d.contents                        type
  , d.extent_management               extent_mgt
  , d.segment_space_management        segment_mgt
  , NVL(a.bytes, 0)                   ts_size
  , NVL(t.bytes, 0)                   used
  , NVL(a.bytes - NVL(t.bytes, 0), 0) free
  , NVL(t.bytes / a.bytes * 100, 0)   pct_used
FROM
    sys.dba_tablespaces d
  , ( SELECT tablespace_name, SUM(bytes) bytes
      FROM dba_temp_files
      GROUP BY tablespace_name
    ) a
  , ( SELECT tablespace_name, SUM(bytes_cached) bytes
      FROM v$temp_extent_pool
      GROUP BY tablespace_name
    ) t
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.extent_management LIKE 'LOCAL'
  AND d.contents           LIKE 'TEMPORARY'
ORDER BY
    2
/

Note : la colonne free de la première requête était commentée dans la version d'origine (calculée mais non affichée) ; elle a été réactivée ici pour garder les deux branches de l'UNION ALL cohérentes entre elles.

Voir aussi

• Dba tablespaces 7 — variante pour Oracle7/8, sans gestion des tempfiles TEMPORARY
• Dba tablespaces 8 — variante Oracle8i, sans le détail de gestion des segments
• Dba top segments — détail des plus gros segments par tablespace