Revoir la taille de vos fichiers à la hausse et à la baisse

J'avoue que ça semble un peu trivial si vous lisez ce blog mais je n'ai pas vraiment trouvé mon bonheur sur le web et comme on est jamais aussi bien servi que par soi-même, voici quelques requêtes pour aider à faire augmenter et réduire la taille des fichiers de base de données. Voilà pour compléter mes posts précédents à propos de DBMS_REDEFINITION par exemple :

• 2-3 trucs à propos de DBMS_REDEFINITION
• 2-3 trucs (de plus) à propos de DBMS_REDEFINITION
  

La commande en question :

D'abord, il faut noter que changer les paramètres d'un fichier de données, est assez simple. Si vous voulez utiliser une commande indépendante de votre OS et du fait que vous utilisiez ou non OMF, voici comment faire.

D'abord lister les fichiers pour un tablespace donné :

set pages 100
set lines 140
col tablespace format a30
col bl_kb format 99
col management format a10
col segment format a6
col contents format a9
col status format a7
col size_mb format 999,999
col real_mb format 999,999
col max_mb format 999,999
col nfile format 9999
col perfree format 999.99

select t.tablespace_name tablespace,
      t.block_size/1024 bl_kb,
      t.extent_management management,
      t.segment_space_management segment,
      t.contents contents ,
      t.status,
      f.nfile,
      round(f.size_mb-nvl(fs.free_mb,0)) real_mb,
      f.size_mb,
      f.max_mb,
      round((f.max_mb-(f.size_mb-nvl(fs.free_mb,0)))
            /f.max_mb*100,2) perfree
 from dba_tablespaces t,
      (select tablespace_name,
              round(sum(bytes)/1024/1024) size_mb,
              round(sum(
                case when autoextensible='NO' then bytes
                     else greatest(bytes,maxbytes) end)
                         /1024/1024) max_mb,
              count(file_id) nfile
         from dba_data_files
        group by tablespace_name) f,
      (select tablespace_name,
              round(sum(bytes)/1024/1024) free_mb
         from dba_free_space
        group by tablespace_name) fs
where t.tablespace_name=f.tablespace_name
  and t.tablespace_name=fs.tablespace_name(+)
order by size_mb;

Une fois que vous visualisez la liste de vos tablespaces, vous pouvez afficher la liste des fichiers associés en exécutant la requête ci-dessous et en remplaçant la variable &TSNAME par le nom du tablespace que vous observez :

set lines 120
col id format 9999;
col file_name format a80
col size_mb format 999,999
col extens format a3
col max_mb format 999,999

select d.file_id id,
      d.file_name,
      round(d.BYTES/1024/1024) size_mb,
      d.AUTOEXTENSIBLE extens,
      round(d.maxbytes/1024/1024) max_mb
 from dba_data_files d,
      v$datafile df
where tablespace_name='&TSNAME'
  and df.file#=d.file_id
order by df.creation_time;

Et enfin vous pouvez re-dimensionner vos tablespaces ou changer d'autres propriétés avec la commande alter database datafine n où n est le file_id :

alter database datafile 10
       resize 4096M;

Pour plus d'infos, reportez-vous à la documentation du "ALTER DATABASE".

Vous ne pouvez pas réduire votre fichier au deçà de l'extent alloué le plus loin.

D'où l'utilité parfois de toutes ces fonctionnalités dont la plupart ne sont disponibles qu'en version Enterprise Edition que sont : "ALTER INDEX REBUILD ONLINE", "DBMS_REDEFINITION", "ALTER INDEX COALESCE", "ALTER TABLE SHRINK...", "ALTER TABLE MOVE...ONLINE" qui vous permettent de déplacer des données ou index dans un nouveau tablespace

Voici une requête qui permet de générer la liste des ordres SQL pour retailler au plus court l'ensemble des fichiers de données d'un tablespace :

select 'alter database datafile '||
         a.file_id ||' resize ' ||
         ceil(nvl(b.last_block*c.block_size
           /1024/1024,512)) ||'M;' command
 from dba_data_files a,
       (select file_id,
               max(block_id+blocks-1) last_block
          from dba_extents
         group by file_id ) b,
       dba_tablespaces c
where a.file_id = b.file_id(+)
  and c.tablespace_name=a.tablespace_name
  and a.tablespace_name='&TSNAME'
  and ceil(blocks*c.BLOCK_SIZE/1024/1024)-
      ceil((nvl(hwm,1)* c.BLOCK_SIZE)/1024/1024 ) > 0;

Vous pouvez empêcher les fichiers de continuer à s'étendre dans un système de fichiers.

Pour réduire l'espace occupé par une base de données, il est fréquent d'utiliser des fichiers en mode d'extension automatique. Bien sur avec ASM il est aisé d'ajouter des disques à un point de montage mais pour des raisons qui me dépassent, certaines personnes n'utilisent pas encore ASM , même en 10g. Pour répondre à cette dernière préoccupation qui consiste à savoir combien d'espace restera disponible lorsqu'on aura arrêté l'extension automatique des fichiers situés sur un point de montage, voici une première requête :

accept mount2stop prompt "Mount to stop autoextend from (e.g./u03/oradata/ORCL) :"
/u03/oradata/ORCL

set pages 100
set lines 140
col tablespace format a30
col bl_kb format 99
col management format a10
col segment format a6
col contents format a9
col status format a7
col size_mb format 999,999
col max_mb format 999,999
col nfile format 9999
col perfree format 999.99

select t.tablespace_name tablespace,
      t.block_size/1024 bl_kb,
      t.extent_management management,
      t.segment_space_management segment,
      t.contents contents ,
      t.status,
      f.nfile,
      f.size_mb,
      f.max_mb,
      round((f.max_mb-(f.size_mb-nvl(fs.free_mb,0)))
             /f.max_mb*100,2) perfree
 from dba_tablespaces t,
      (select tablespace_name,
              round(sum(bytes)/1024/1024) size_mb,
              round(sum(
                case when autoextensible='NO' then bytes
                     when instr(file_name,'&mount2stop')=1 then bytes
                     else greatest(bytes,maxbytes) end)
                        /1024/1024) max_mb,
              count(file_id) nfile
         from dba_data_files
        group by tablespace_name) f,
      (select tablespace_name,
              round(sum(bytes)/1024/1024) free_mb
         from dba_free_space
        group by tablespace_name) fs
where t.tablespace_name=f.tablespace_name
  and t.tablespace_name=fs.tablespace_name(+)
order by size_mb;

Ou si vous voulez faire un focus plus particulier sur les fichiers actuellement en mode auto extend et les tablespaces associés

col size_mb format 999,999
col max_mb  format 999,999
col free_mb format 999,999

select tablespace_name,
      f.file_name,
      round(f.bytes/1024/1024) size_mb,
      round(f.maxbytes/1024/1024) max_mb,
      round((f.maxbytes-f.bytes)/1024/1024) free_mb
      from dba_data_files f
where instr(f.file_name,'&mount2stop')=1
  and f.autoextensible='YES' and f.maxbytes!=f.bytes
order by tablespace_name, f.file_id

Une fois que vous avez déterminé à quels tablespaces il faut ajouter des nouveaux fichiers pour éviter de tomber à court d'espace lorsque vous aurez bloqué l'extension automatique, affichez les fichiers actuels avec la requête ci-dessous :

set lines 120
col id format 9999;
col file_name format a80
col size_mb format 999,999
col extens format a3
col max_mb format 999,999

select d.file_id id,
      d.file_name,
      round(d.BYTES/1024/1024) size_mb,
      d.AUTOEXTENSIBLE extens,
      round(d.maxbytes/1024/1024) max_mb
 from dba_data_files d,
      v$datafile df
where tablespace_name='&TSNAME'
  and df.file#=d.file_id
order by df.creation_time;

Vous pouvez alors ajouter des fichiers à l'aide de la commande "alter tablespace add datafile" comme par exemple ci-dessous (Changez les noms et paramètres selon vos besoins) :

alter tablespace X add datafile '/u04/oradata/ORCL/x02.dbf'
       size 1024M autoextend on next 16M maxsize 4096M;

Enfin une fois que vous êtes assuré de ne pas manquer d'espace, bloquez l'extension automatique à l'aide de la requête qui suit et qui génère le SQL pour bloquer les fichiers concernés :

accept mount2stop prompt "Mount to stop autoextend from (e.g./u03/oradata/ORCL) :"
/u03/oradata/ORCL

set lines 140
col tablespace_name format a15
col command format a120

select tablespace_name,
      'alter database datafile '||
      f.file_id||' autoextend off;' command
 from dba_data_files f
where instr(f.file_name,'&mount2stop')=1
  and f.autoextensible='YES'
order by tablespace_name, f.file_id;

Exécutez les ordres générés par la dernière requête...