28 octobre 2008
2
28
/10
/octobre
/2008
20:05
Bonsoir,
Ce soir on va mettre un peu de coté notre "big table" (mais c'est promis, on y revient très bientôt).
En revanche, je vais vous parler d'un petit soucis qui peut être agacant.
La fameuse ORA-01653 !
Qu'est ce que le ORA-01653 ?
C'est ce qui arrive lorsqu'un tablespace est défini avec la clause AUTOEXTEND à OFF et que l'on tente d'inserer dans une table alors qu'il n'y a plus de place (ou durant un import).
Et plus exactement, cela donne ORA-01653 : Impossible d'étendre la table .... de 8 dans le tablespace ....
Bref que du bonheur, surtout si c'est un traitement qui a pris plusieurs heures, et qu'il va donc falloir recommencer.
Alors bien sur, on peut mettre en place des systemes d'alerte avec des seuils (remplissage à 80 %) pour éviter d'être pris au dépourvu. N'empêche que cela peut quand même arriver....
Et alors ? Que faire ?
Et bien comme pour dans de nombreux cas, ORACLE à prévu de pouvoir palier (en partie du moins) à ce genre de soucis.
Il existe un paramètre qui permet lorsque que l'on arrive à cette limite de taille de figer le traitement plutôt que de renvoyer une erreur.
Ca paramètre a un nom : RESUMABLE_TIMEOUT
Et par défaut sa valeur est 0. Ce qui signifie que le traitement est stoppé avec la fameuse erreur.
Mais arretons de parler, et passons à la pratique.
/*Creation d'un tablespace de petit taille*/
SQL> CONNECT SYSTEM/xxxxx
SQL> CREATE TABLESPACE TBS_1 DATAFILE 'c:\oracle\product\oradata\oradb\tbs01.dbf' SIZE 1M AUTOEXTEND OFF;
/* On donne les droits à l'utilisateur LAO sur ce nouveau tablespace*/
SQL> ALTER USER LAO QUOTA UNLIMITED ON TBS_1;
/* Creation d'une table */
SQL> CONNECT LAO/LAO
SQL> CREATE TABLE T1 (i number,j number);
/* Remplissage de la table*/
SQL> BEGIN
SQL> FOR l IN 1..1000000 LOOP
SQL> INSERT INTO T1 VALUES (l,l);
SQL> END LOOP;
SQL> END;
SQL> /
Ce qui devait arriver arriva:==> ORA-01653 ......
Modifions donc la valeur du parametre RESUMABLE_TIMOUT pour la session
SQL> ALTER SESSION SET RESUMABLE_TIMEOUT=120 (l'unité est la seconde)
Et ....erreur, une ORA-01031 qui nous indique que nous n'avons pas le droit de faire cela.
Que cela ne tienne, donnons nous les droits.
On se reconnecte en system.
SQL> CONNECT SYSTEM/xxxxx
SQL> GRANT RESUMABLE TO LAO;
On peut donc repartir en user normal.
SQL> CONNECT LAO/LAO
SQL> ALTER SESSION SET RESUMABLE_TIMEOUT=3600;
Session modifée.
Relancons le script de remplissage.
SQL> BEGIN
SQL> FOR l IN 1..1000000 LOOP
SQL> INSERT INTO T1 VALUES (l,l);
SQL> END LOOP;
SQL> END;
SQL> /
Que se passe t-il ? RIEN !
Alors que la première fois le traitement était parti en vrille quasi tout de suite, il semble ne rien se passer.
Et en fait, il ne se passe rien. Et il continuera de ne rien se passer pendant 3600 secondes. Au dela de ce délai, le traitement renverra une erreur.
Comment être sur de cela ?
Plusieurs moyens
ORA-01653 : unable to extend table LAO.T1......
Statement in resumable session 'user LAO(61), session 148 was suspended....
C'est pas beau ! y a plus qu'a aggrandir notre fichier(ou en ajouter un nouveau) et le traitement repart.
L'inconvenient avec ce moyent, c'est qu'il faut avoir accès au fichier alert.log et donc au serveur proprement dit.
USER_RESUMABLE
en se connectant dans une nouvelle session en LAO.
SQL> SELECT Session_ID,Status,Timeout,Sql_Text,Error_msg FROM User_Resumable;
On obtient:
Sessio_ID => 148
Status => SUSPENDED
Timeout => 3600
Sql_Text => INSERT INTO T1 VALUES (:B1,:B1)
Error_Message => ORA-01653
Même punition et même remède.
On se connecte en system
SQL> CONNECT SYSTEM/xxxxx
SQL> ALTER TABLESPACE ADD DATAFILE 'c:\oracle\product\oradata\oradb\tbs02.dbf' SIZE 100M AUTOEXTEND OFF;
Et la, oh magie, le traitement reprends...
Vous pouvez d'ailleurs le constater en refaisant le select sur la table User_Resumable (en LAO/LAO)
Le status aura la valeur NORMAL.
En conclusion, je pense que cette option est sympa et qu'il faut l'exploiter. Plutôt que de modifier la valeur au niveau session on peut également modifier la valeur pour la base.
Dans ce cas, il conviendrait de trouver un moyen d'automatiser l'alerte pour que le dba soit au courant qu'un traitement est en attente (recevoir un mail par exemple). Mais ceci est une autre histoire qui fera peut être l'objet d'un nouvel article.
@ bientôt,
LAO
Ce soir on va mettre un peu de coté notre "big table" (mais c'est promis, on y revient très bientôt).
En revanche, je vais vous parler d'un petit soucis qui peut être agacant.
La fameuse ORA-01653 !
Qu'est ce que le ORA-01653 ?
C'est ce qui arrive lorsqu'un tablespace est défini avec la clause AUTOEXTEND à OFF et que l'on tente d'inserer dans une table alors qu'il n'y a plus de place (ou durant un import).
Et plus exactement, cela donne ORA-01653 : Impossible d'étendre la table .... de 8 dans le tablespace ....
Bref que du bonheur, surtout si c'est un traitement qui a pris plusieurs heures, et qu'il va donc falloir recommencer.
Alors bien sur, on peut mettre en place des systemes d'alerte avec des seuils (remplissage à 80 %) pour éviter d'être pris au dépourvu. N'empêche que cela peut quand même arriver....
Et alors ? Que faire ?
Et bien comme pour dans de nombreux cas, ORACLE à prévu de pouvoir palier (en partie du moins) à ce genre de soucis.
Il existe un paramètre qui permet lorsque que l'on arrive à cette limite de taille de figer le traitement plutôt que de renvoyer une erreur.
Ca paramètre a un nom : RESUMABLE_TIMEOUT
Et par défaut sa valeur est 0. Ce qui signifie que le traitement est stoppé avec la fameuse erreur.
Mais arretons de parler, et passons à la pratique.
/*Creation d'un tablespace de petit taille*/
SQL> CONNECT SYSTEM/xxxxx
SQL> CREATE TABLESPACE TBS_1 DATAFILE 'c:\oracle\product\oradata\oradb\tbs01.dbf' SIZE 1M AUTOEXTEND OFF;
/* On donne les droits à l'utilisateur LAO sur ce nouveau tablespace*/
SQL> ALTER USER LAO QUOTA UNLIMITED ON TBS_1;
/* Creation d'une table */
SQL> CONNECT LAO/LAO
SQL> CREATE TABLE T1 (i number,j number);
/* Remplissage de la table*/
SQL> BEGIN
SQL> FOR l IN 1..1000000 LOOP
SQL> INSERT INTO T1 VALUES (l,l);
SQL> END LOOP;
SQL> END;
SQL> /
Ce qui devait arriver arriva:==> ORA-01653 ......
Modifions donc la valeur du parametre RESUMABLE_TIMOUT pour la session
SQL> ALTER SESSION SET RESUMABLE_TIMEOUT=120 (l'unité est la seconde)
Et ....erreur, une ORA-01031 qui nous indique que nous n'avons pas le droit de faire cela.
Que cela ne tienne, donnons nous les droits.
On se reconnecte en system.
SQL> CONNECT SYSTEM/xxxxx
SQL> GRANT RESUMABLE TO LAO;
On peut donc repartir en user normal.
SQL> CONNECT LAO/LAO
SQL> ALTER SESSION SET RESUMABLE_TIMEOUT=3600;
Session modifée.
Relancons le script de remplissage.
SQL> BEGIN
SQL> FOR l IN 1..1000000 LOOP
SQL> INSERT INTO T1 VALUES (l,l);
SQL> END LOOP;
SQL> END;
SQL> /
Que se passe t-il ? RIEN !
Alors que la première fois le traitement était parti en vrille quasi tout de suite, il semble ne rien se passer.
Et en fait, il ne se passe rien. Et il continuera de ne rien se passer pendant 3600 secondes. Au dela de ce délai, le traitement renverra une erreur.
Comment être sur de cela ?
Plusieurs moyens
- Via votre outil d'administration préféré, vérifier qu'il n'y a plus de place dans le tablespace TBS_1
- Aller jeter un oeil dans le fichier ALERT.log qui se trouve dans ORACLE_HOME\admin\SID\bdump\
- avec une autre session jeter un oeil dans la vue USER_RESUMABLE
Je passe la première méthode.
Alert.log
ORA-01653 : unable to extend table LAO.T1......
Statement in resumable session 'user LAO(61), session 148 was suspended....
C'est pas beau ! y a plus qu'a aggrandir notre fichier(ou en ajouter un nouveau) et le traitement repart.
L'inconvenient avec ce moyent, c'est qu'il faut avoir accès au fichier alert.log et donc au serveur proprement dit.
USER_RESUMABLE
en se connectant dans une nouvelle session en LAO.
SQL> SELECT Session_ID,Status,Timeout,Sql_Text,Error_msg FROM User_Resumable;
On obtient:
Sessio_ID => 148
Status => SUSPENDED
Timeout => 3600
Sql_Text => INSERT INTO T1 VALUES (:B1,:B1)
Error_Message => ORA-01653
Même punition et même remède.
On se connecte en system
SQL> CONNECT SYSTEM/xxxxx
SQL> ALTER TABLESPACE ADD DATAFILE 'c:\oracle\product\oradata\oradb\tbs02.dbf' SIZE 100M AUTOEXTEND OFF;
Et la, oh magie, le traitement reprends...
Vous pouvez d'ailleurs le constater en refaisant le select sur la table User_Resumable (en LAO/LAO)
Le status aura la valeur NORMAL.
En conclusion, je pense que cette option est sympa et qu'il faut l'exploiter. Plutôt que de modifier la valeur au niveau session on peut également modifier la valeur pour la base.
Dans ce cas, il conviendrait de trouver un moyen d'automatiser l'alerte pour que le dba soit au courant qu'un traitement est en attente (recevoir un mail par exemple). Mais ceci est une autre histoire qui fera peut être l'objet d'un nouvel article.
@ bientôt,
LAO
Partager cet article
