Bonjour,

De temps en temps notre TBS Undo explose car il est sous-dimensionné par rapport à des traitements en cours : nous avons une erreur "ORA-30036: unable to extend segment by 8 in undo tablespace".

On m'a demandé d'identifier la transaction qui pose problème; on ne veut pas dans l'immédiat augmenter la taille du TBS car le problème serait alors masqué alors qu'il faut le résoudre.

Voici ma démarche mais j'ai besoin de votre aide pour valider le process (pour info je travaille avec Toad 9.7.2.5 sans le pack DBA ni aucun outil de monitoring de la base).

Etape 1 : Identifier la taille max du TBS UNDO
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
SELECT TABLESPACE_NAME, SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES))/(1024*1024) “MAX_SIZE in Mo”
FROM   DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'UNDOTBS'
GROUP BY TABLESPACE_NAME;
 
TABLESPACE_NAME                MAX_SIZE in Mo
------------------------------ --------------
UNDOTBS                                 31744
1 row selected.

Etape 2 : Créer une table de test et la remplir avant de modifier ses champs et voir combien de blocs Undo sont utilisés. J'ai inséré un million d'enregistrements. La table n'a aucun index.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
 
CREATE TABLE GSA.ZZZ_TEST_DDU
(
  ID   NUMBER(12),
  LIB  VARCHAR2(200 BYTE)
)
TABLESPACE TBS_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
desc zzz_test_ddu
TABLE zzz_test_ddu
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(12,)                 
 LIB                                                VARCHAR2(200)

Etape 3 : vérifier avant ma modif la taille occupée par les transactions en cours. Mon user est X097933 et je ne suis pas présent.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
SELECT         S.USERNAME AS "SESSION USERNAME", 
S.OSUSER AS "OS USERNAME", 
 (T.USED_UBLK * (select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'UNDOTBS'))/(1024*1024) AS "Taille UNDOTBS used Mo"
FROM V$SESSION S, V$TRANSACTION T
WHERE S.SADDR = T.SES_ADDR;
 
SESSION USERNAME               OS USERNAME                   Taille UNDOTBS used Mo
------------------------------------------------------------------------------------------------
SIR                                      X120341                            .0078125

Etape 4 : modification de ma table
Code : Sélectionner tout - Visualiser dans une fenêtre à part
update zzz_test_ddu set lib = lib||'2';

Etape 5 : vérifier après ma modif la taille occupée par les transactions en cours
Il s'agit bien de mon user X097933 MAIS la taille des segments UNDO prise me semble excessif par rapport à ma table.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
SELECT         S.USERNAME AS "SESSION USERNAME", 
S.OSUSER AS "OS USERNAME", 
 
 (T.USED_UBLK * (select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'UNDOTBS'))/(1024*1024) AS "Taille UNDOTBS used Mo"
FROM V$SESSION S, V$TRANSACTION T
WHERE S.SADDR = T.SES_ADDR ;
 
SESSION USERNAME               OS USERNAME                   Taille UNDOTBS used Mo
-----------------------------------------------------------------------------------------------
GSA                            X097933                                    173.65625

Etape 6 : Vérifier la taille de ma table en base.
Attention pour les VARCHAR(2) : un caractère peut-être stocké sur N octets ! On fera donc une approximation en estimant : 1 caractère = 1 octet.
J'obtiens 21 mégas alors que ma transaction occupe 174 mégas dans le TBS Undo : est-ce normal ce rapport de 7? Il me semble excessif.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
SELECT  COUNT(*), 
        ROUND(AVG(LENGTH(ID))) AS "LONGUEUR ID", 
        ROUND(AVG(LENGTH(LIB))) AS "LONGUEUR LIB",  
        ROUND(COUNT(*) * (ROUND(AVG(LENGTH(ID))) + ROUND(AVG(LENGTH(LIB))))/ (1024*1024), 2) AS "TAILLE TABLE EN MO" 
FROM ZZZ_TEST_DDU;
 
COUNT(*) LONGUEUR ID LONGUEUR LIB TAILLE TABLE EN MO
---------- ----------- ------------ ------------------
    999999           8           14              20.98
1 row selected.

Etape 7 : je commit ma transaction. Les données du user X097933 ont bien disparu de la vue des transactions.
Attention, si je suis en mode Flashback query je sais que le TBS UNDO n'est pas vidé des données de mes transactions mais je n'ai pas trouvé les infos liées à un user et son espace disque occupé dans le TBS UNDO donc je me suis rapatrié sur les données de la vue des trnsactions.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
commit;
 
SELECT         S.USERNAME AS "SESSION USERNAME", 
S.OSUSER AS "OS USERNAME", 
 
 (T.USED_UBLK * (select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'UNDOTBS'))/(1024*1024) AS "Taille UNDOTBS used Mo"
FROM V$SESSION S, V$TRANSACTION T
WHERE S.SADDR = T.SES_ADDR ;
 
SESSION USERNAME               OS USERNAME                   Taille UNDOTBS used Mo
------------------------------ -----------------------------------------------------------------
SIR                            X120341                                       .0078125