LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon