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 :
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…
3 réflexions sur “Revoir la taille de vos fichiers à la hausse et à la baisse”
Replace ‘hwm’ by ‘last_block’
La 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 me donne le message d’erreur suivant:
ceil((nvl(hwm,1)* c.BLOCK_SIZE)/1024/1024 ) > 0
*
ERROR at line 15:
ORA-00904: « HWM »: invalid identifier
To get the next_extent size of a DMT :
select max(s.NEXT_EXTENT) biggest_extent
, t.tablespace_name
from dba_segments s
, dba_tablespaces t
where t.extent_management=’DICTIONARY’
and t.tablespace_name=s.tablespace_name
group by t.tablespace_name;
Les commentaires sont fermés.