Oracle : tablespace SYSTEM anormalement gros
Requêtes pour identifier les gros segments du tablespace SYSTEM et récupérer l'espace inutilisé des datafiles.
Quand le tablespace SYSTEM grossit de façon inattendue, il faut d'abord identifier les segments qui occupent l'espace, puis récupérer l'espace inutilisé dans les datafiles. Cette note rassemble les requêtes de diagnostic et un script de redimensionnement.
Lister les datafiles d'un tablespace
col ts_name form a14 word wrap
col file_name form a34 word wrap
set pause on pages 24 pause 'wahey> '
spool dbf_lst.lst
select
file_name file_name
,to_char(file_id,'9999') f_id
,tablespace_name ts_name
,to_char(bytes/(1024*1024),'999,999') size_M
,to_char(blocks/1000,'99,999') thou_blks
from
sys.dba_data_files
where
tablespace_name like upper('&ts_name'||'%')
order by tablespace_name,file_name
/
spool off
clear colu
Trouver les gros segments de SYSTEM
select owner,segment_name,segment_type
,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM'
and bytes/(1024*1024) > 1
order by size_m desc;
Récupérer l'espace inutilisé dans les datafiles
La requête génère une commande alter database datafile ... resize par datafile, jusqu'au high-water mark. &&blksize est une variable de substitution SQL*Plus : elle demande la taille de bloc de la base (en octets) à la première utilisation. Relisez les commandes générées avant de les exécuter.
set pages 0
set lines 300
column cmd format a300 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;
Augmenter le nombre maximal d'extents d'un index du dictionnaire
Un dossier du support Oracle relatif à un tablespace SYSTEM anormalement gros a utilisé cette procédure sur l'index I_H_OBJ#_COL#. Elle doit s'exécuter en mode upgrade :
SQL> shutdown immediate
SQL> startup upgrade
SQL> alter index I_H_OBJ#_COL# storage (maxextents &nnnnn);
Enter value for nnnnn: 10000
Index altered.
SQL> shutdown immediate
SQL> startup
Recréer les objets du dictionnaire après une création manuelle de base
Quand une base est créée manuellement, ces scripts doivent être exécutés ensuite :
| Script | Rôle |
|---|---|
catalog.sql |
crée les vues du dictionnaire de données |
catproc.sql |
crée les procédures stockées système |
pupbld.sql |
crée les rôles et profils par défaut |
sqlplus /nolog
connect / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/pupbld.sql
Remarques
- Les causes fréquentes d'un gros tablespace
SYSTEMsont décrites dans Why is my SYSTEM tablespace so big?. - Le déplacement des datafiles d'un tablespace est traité dans cet article.