Controler les tablespaces en autoextent [Résolu]

[LBO72]

Bonjour,

Je suis en Oracle 10GR2, cela fait un moment que je patoge. Tous nos tablespaces sont en autoextents, je voudrai écrire une procédure pour soulever des alertes pour chaque TBS dont la max_size est presque atteint.

En vous remerciant,
LBO72.

[Pomalaix]

Quel est le point qui vous pose problème ? Vous voulez une requête pour détecter les tablespaces presque pleins, ou une technique pour émettre une alerte ?

[LBO72]

Bonjour Pomalaix :-)
Mon souci est le suivant :
On a des traitements qui tournent le WE, et quand j'arrive le lundi certains traitements plantent sur l'erreur Oracle :
ORA-01688: unable to extend table owner.matable by 8192 in tablespace Mon_TBS.
Je voudrai faire des controles pour lever des alertes et éviter ces plantages.

Encore merci,

LBO72.

[Pomalaix]

Envoyé par LBO72

...
Je voudrai faire des controles pour lever des alertes et éviter ces plantages.

Pas de souci, l'objectif final est clair depuis le début, ce qui ne l'est pas est de savoir dans quelle partie du processus vous avez besoin d'aide.

Le plus simple à mon avis est d'utiliser Database Control.
Les tablespaces sont automatiquement surveillés, avec, par défaut, un seuil d'avertissement à 85% de remplissage, et un seuil critique à 97%.
S'il y a un dépassement, vous le verrez dans la page d'accueil de database control.

Si en plus vous avez activé l'émission de notifications d'alertes, vous serez prévenu par messagerie lorsqu'une anomalie sera détectée, notamment ce problème d'espace.

Si vous ne disposez pas du "diagnostic pack" (payant), les seuils d'avertissement ne sont pas traités, et il sera alors peut-être utile d'abaisser le seuil critique, pour être alerté assez tôt.

[LBO72]

Merci pour ta réponse Maitre polamaix

Je ne suis pas DBA de prod, je n'ai pas accés à la console. Je suis un pauvre DBAs études et je cherche, par mes propres moyens, à écrire une requête qui me fera ce travail.

Cdlt,
LBO72.

[LBO72]

Oh pardon, quelle bourde :-)

Grand maitre pomalaix

[Pomalaix]

Envoyé par LBO72

... je cherche, par mes propres moyens, à écrire une requête qui me fera ce travail.

Merci pour cette précision, que j'essaye de vous faire cracher depuis le début !

Cela dit, quelque chose n'est pas clair dans votre situation : soit il s'agit d'une base de prod, et dans ce cas c'est bien évidemment au DBA de prod de gérer les problèmes d'espace ; soit c'est une base de dev dont vous êtes responsable, et dans ce cas vous devez avoir les outils adaptés. Je rappelle que Database Control est un outil qui fait partie du produit de base Oracle Database. Vous pouvez donc l'utiliser "gratuitement", seuls les packs optionnels comme Diagnostic pack et Tuning pack exigent une licence complémentaire, mais ils ne sont pas nécessaires pour surveiller les tablespaces.

[marsup077]

Est-ce qu'un truc comme ça répond à ton besoin ?

Code :

1
2
3
4
5
6
7
8
9
10
 
SELECT a.tablespace_name, a.taille_totale, b.taille_libre, round((b.taille_libre/a.taille_totale) * 100) pct_libre 
FROM
(SELECT tablespace_name, sum(bytes)/1024/1024 taille_totale
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes)/1024/1024 taille_libre
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name

[Pomalaix]

Envoyé par marsup077

Est-ce qu'un truc comme ça répond à ton besoin ?

Ca ne va pas convenir, car vous prenez en compte uniquement la taille réelle, et non la capacité d'autoextension.

Voici ma proposition testée vite fait :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH
   libre AS (SELECT tablespace_name Nom_TBS,
                           trunc(sum(bytes)/1024/1024, 2) Mo_libres
                           FROM dba_free_space
                           GROUP BY tablespace_name),
   alloue AS (SELECT tablespace_name Nom_TBS,
                                   trunc(sum(bytes)/1024/1024, 2) Mo_TBS,
                                   trunc(sum(maxbytes)/1024/1024, 2) Mo_autoext,
                                   count(*) nb_fic
                           FROM dba_data_files
                           GROUP BY tablespace_name)
SELECT nom_tbs,
   nb_fic,
   mo_tbs,
   mo_libres,
   mo_autoext,
   trunc((mo_libres/mo_tbs)*100, 2) PCT_libre,
   trunc(((mo_libres + mo_autoext - mo_tbs)/mo_autoext)*100, 2) PCT_libre_autoext
FROM alloue LEFT OUTER JOIN libre USING (nom_tbs)
ORDER BY nom_tbs;

Et un résultat :

Code :

1
2
3
4
5
6
7
8
NOM_TBS                            NB_FIC     MO_TBS  MO_LIBRES MO_AUTOEXT  PCT_LIBRE PCT_LIBRE_AUTOEXT
------------------------------ ---------- ---------- ---------- ---------- ---------- -----------------
EXAMPLE                                 1        100      22,62   32767,98      22,62             99,76
SYSAUX                                  1        270       7,75   32767,98       2,87             99,19
SYSTEM                                  1        550        5,5   32767,98          1             98,33
TBSTEST                                 1         10        ,93         10        9,3               9,3
UNDOTBS1                                1         30      17,25   32767,98       57,5             99,96
USERS                                   1      56,25       1,25   32767,98       2,22             99,83

Les deux dernières colonnes fournissent respectivement le pourcentage d'espace libre par rapport à la taille actuelle du tablespace, et par rapport à sa taille maximale possible compte-tenu de la limite d'autoextension en vigueur.

[LBO72]

Merci à vous tous ...
Et merci au GRAND MAITRE pomalaix

Cdlt,
LBO72.