Tablespace extract sql

Fiche express
Type	Script SQL*Plus de diagnostic (Tablespaces)
Voir aussi	Tablespace size · Tablespaces infos

Tablespace extract sql génère, à partir des tablespaces existants d'une base, les ordres CREATE TABLESPACE ... DATAFILE ... AUTOEXTEND ON MAXSIZE ... permettant de recréer la même structure de tablespaces (mêmes tailles et mêmes limites d'autoextension) sur une autre base — utile pour préparer un environnement cible avant un import/restore, sans dépendre d'un export DBMS_METADATA complet.

Le script exclut par défaut les tablespaces systèmes (SYSTEM, SYSAUX, UNDO*) et quelques tablespaces génériques (USERS, tablespaces temporaires nommés TEMP_*) : à adapter selon la convention de nommage de l'environnement. La version ci-dessous cible un stockage ASM (pas de chemin de fichier explicite dans le DATAFILE) ; une variante en commentaire permettait de générer un chemin de fichier explicite pour un stockage classique.

Script

-- Génère les ordres CREATE TABLESPACE (taille + autoextend) à partir des
-- tablespaces existants, pour reconstituer la même structure ailleurs.
-- Version ASM (pas de chemin de datafile explicite).

host rm -f create_tbs_dat.sql
SET echo off
SET lines 150
SET feedback off
SET pages 0

SPOOL create_tbs_dat.sql

-- Variante fichier système (hors ASM), à adapter au point de montage cible :
-- select 'create tablespace '||a.tablespace_name||' datafile '''
--        ||'/u01/app/oracle/oradata/<SID>/'||lower(a.tablespace_name)||'01.dbf'''
--        ||' size '||nvl(round(sum(b.bytes)/1024/1024),0)||'M autoextend on maxsize '
--        ||nvl(round(sum(b.maxbytes)/1024/1024),0)||'M;'
-- ...

SELECT
    'create tablespace ' || a.tablespace_name
    || ' datafile size ' || nvl(round(sum(b.bytes)/1024/1024),0) || 'M'
    || ' autoextend on maxsize ' || nvl(round(sum(b.maxbytes)/1024/1024),0) || 'M;'
FROM
    dba_tablespaces a
  , ( SELECT tablespace_name
           , SUM(bytes)                      bytes
           , COUNT(*)                        count_files
           , SUM(GREATEST(maxbytes,bytes))   maxbytes
      FROM   dba_data_files
      GROUP BY tablespace_name
      UNION ALL
      SELECT tablespace_name
           , SUM(bytes)
           , COUNT(*)
           , SUM(GREATEST(maxbytes,bytes))
      FROM   dba_temp_files
      GROUP BY tablespace_name
    ) b
  , ( SELECT tablespace_name, SUM(bytes) free_bytes
      FROM   dba_free_space
      GROUP BY tablespace_name
      UNION ALL
      SELECT tablespace_name, SUM(bytes_free) free_bytes
      FROM   v$temp_space_header
      GROUP BY tablespace_name
    ) c
WHERE
    a.tablespace_name = b.tablespace_name (+)
AND a.tablespace_name = c.tablespace_name (+)
AND a.tablespace_name NOT IN ('SYSAUX','SYSTEM','TEMP','UNDOTBS1','UNDOTBS2','USERS')
GROUP BY
    a.tablespace_name
  , a.contents
  , a.extent_management
  , a.allocation_type
  , a.segment_space_management
  , a.bigfile
  , a.status
ORDER BY
    a.tablespace_name;

SPOOL off

Voir aussi

• Tablespace size — taille globale (data + temp + redo) en un coup d'œil
• Tablespaces infos — vue d'ensemble détaillée par tablespace