Resize oracle datafile

30 oct

 

Cher(e)s ami(e) en oracle

 

Afin de récupérer l’espace  surdimensionné des datafiles oracle avec autoexented yes , voici un script  que j’utilise sur oracle 11g afin de redimensionner

les datafiles.

 

set linesize 1000 pagesize 0 feedback off trimspool on

with

hwm as  (

– get highest block id from each datafiles ( from x$ktfbue as we don’t need all joins from dba_extents )

select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn

),

hwmts as (

– join ts# with tablespace_name

select name tablespace_name,relative_fno,hwm_blocks from hwm join v$tablespace using(ts#)

),

hwmdf as (

– join with datafiles, put 5M minimum for datafiles with no extents

select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes from hwmts right join dba_data_files using(tablespace_name,relative_fno)

)

select

case when autoextensible=’YES’ and maxbytes>=bytes

then — we generate resize statements only if autoextensible can grow back to current size

‘/* reclaim ‘||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

||’M from ‘||to_char(ceil(bytes/1024/1024),999999)||’M */ ‘

||’alter database datafile  »’||file_name|| »’ resize ‘||ceil(hwm_bytes/1024/1024)||’M;’

else — generate only a comment when autoextensible is off

‘/* reclaim ‘||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

||’M from ‘||to_char(ceil(bytes/1024/1024),999999)

||’M after setting autoextensible maxsize higher than current size for file ‘

|| file_name||’ */’

end SQL

from hwmdf

where bytes-hwm_bytes>1024*1024 — resize only if at least 1MB can be reclaimed

order by bytes-hwm_bytes desc

/

voici le résultat attendu

/* reclaim 3986M from 5169M */ alter database datafile ‘/u01/oradata/DB1USV/datafile/o1_mf_undotbs1_o9pfojva_.dbf’ resize 1183M;

/* reclaim 3275M from 15864M */ alter database datafile ‘/u01/oradata/DB1USV/datafile/o1_mf_apcpy_o5pfojni_.dbf’ resize 12589M;

/* reclaim 2998M from 3655M */ alter database datafile ‘/u01/oradata/DB1USV/datafile/o1_mf_cpy_qt_oepfok3n_.dbf’ resize 657M;

/* reclaim 2066M from 2250M */ alter database datafile ‘/u01/oradata/DB1USV/datafile/o1_mf_undotbs2_olpfokc9_.dbf’ resize 185M;

/* reclaim 896M from 4000M */ alter database datafile ‘/u01/oradata/DB1USV/datafile/o1_mf_cpy_ocpfok3n_.dbf’ resize 3105M;

 

Attention :

Ce script ne permet par de récupérer  l’espace libre dans le datafile, il faut un skrink

Oraclement votre

Pas encore de commentaire

Laisser une réponse

Unblog.fr | Créer un blog | Annuaire | Signaler un abus