Discussion :

[apad]

Bonjour

Je rencontre l'erreur ORA-01652 (impossible d'étendre le segment temporaire de 8192 dans le tablespace TBS_IDX) lors de la création d'un index.
J'ai parcouru nombre de forums et partout il est indiqué qu'il faut augmenter la taille du tablespace. Jusque là OK.

Pourtant, il y a plusieurs points que je ne comprends pas :
1) le tablespace TBS_IDX n'est pas un tablespace temporaire, c'est le tablespace que j'utilise pour stocker mes index sur une table particulièrement grosse. Pourquoi Oracle veut-il créer un segment temporaire dans un tablespace qui ne l'est pas ?

2) Le tablespace TBS_IDX est composé de 10 fichiers de 30 Go chacun. Chaque fichier est en autoextent avec un next de 400 Mo. Il reste presque 18 Go sur mon disque. Pourquoi le tablespace ne s'est-il pas agrandi automatiquement alors que la place nécessaire est disponible ?

3) Actuellement, le tablespace TBS_IDX dispose d'environ 80 Go d'espace libre. Quand bien même je rajouterais un datafile à mon tablespace, quelle taille faut-il lui donner ? Existe-t-il un moyen de calculer (même à la louche) la taille requise pour l'index (et son calcul) ?

Pour info, mon index concerne un champ TIMESTAMP sur une table de 1,5 To de données (plus de 4 milliards de lignes) donc comme ça prend un peu de temps, j'aimerais bien comprendre les points précédents avant de me lancer dans les manipulations.

Cordialement

[pifor]

Le segment temporaire représente l'index en cours de création.

Quelle est la version exacte d'Oracle utilisée ?

[apad]

10.2.0.1

[pifor]

Je n'arrive pas à reproduire le problème avec Oracle XE.
Les tables et les index sont-ils partitionnés ?
Y-a-il un autre message d'erreur dans l'alert.log ?

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
SQL>
SQL> create tablespace test datafile 'd:\oracle\oradata\xe\test.dbf' size 4m;
 
Tablespace created.
 
SQL> alter database datafile 'd:\oracle\oradata\xe\test.dbf' autoextend on next 1m;
 
Database altered.
 
SQL> select file_name, autoextensible from dba_data_files where tablespace_name ='TEST';
 
FILE_NAME
--------------------------------------------------------------------------------
AUT
---
D:\ORACLE\ORADATA\XE\TEST.DBF
YES
 
 
SQL> select file_name, bytes/(1024*1024) from dba_data_files where file_name like '%TEST%';
 
FILE_NAME
--------------------------------------------------------------------------------
BYTES/(1024*1024)
-----------------
D:\ORACLE\ORADATA\XE\TEST.DBF
                4
 
 
SQL> create table t(x int, y varchar2(50), z varchar2(50)) tablespace test;
 
Table created.
 
SQL>
SQL> begin
  2  for i in 1..100000
  3  loop
  4   insert into t values(i, to_char(i) || to_char(i), to_char(i) || to_char(i));
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL>
SQL>
SQL> select file_name, bytes/(1024*1024) from dba_data_files where file_name like '%TEST%';
 
FILE_NAME
--------------------------------------------------------------------------------
BYTES/(1024*1024)
-----------------
D:\ORACLE\ORADATA\XE\TEST.DBF
                5
 
 
SQL>
SQL> create index i1 on t(x,y,z) tablespace test;
 
Index created.
 
SQL>
SQL>
SQL> select file_name, bytes/(1024*1024) from dba_data_files where file_name like '%TEST%';
 
FILE_NAME
--------------------------------------------------------------------------------
BYTES/(1024*1024)
-----------------
D:\ORACLE\ORADATA\XE\TEST.DBF
               10
 
 
SQL>
SQL>

[apad]

La table est effectivement partitionnée (partition logique) par liste. Il existe 32 partitions. Disons pour faire simple une partition par jour dans un mois (de 1 à 31) et une partition poubelle pour les enregistrements qui n'auraient pas de date correcte.

L'index est partitionné et local. Il a été créé avec la clause PARALLEL. C'est l'un des process parallèles qui renvoit l'erreur ORA-01652.

En refaisant des calculs (à la louche) sur la taille de l'index, il semble que l'index fasse aux alentours de 100 Go. J'ai 80 Go disponible dans le tablespace et 17 Go disponible sur le serveur. Donc il est possible que la création de l'index soit impossible à cause d'un manque de place (à quelques Go près)

Cependant, il reste un point sur lequel j'ai un doute :
Lorsque les datafiles sont en autoextent, si mon instruction CREATE INDEX provoque une première extension d'un datafile, puis une seconde, etc et que la cinquième extension échoue par manque de place sur mon disque, est-ce que le datafile retrouve sa taille initiale avant l'instruction CREATE ou est-ce que les 4 premières extensions restent allouées ?

En effet, je m'interroge car la taille de mes datafiles n'a pas évolué :
- Soit l'autoextent n'a pas fonctionné et il faut que je comprenne pourquoi.
- Soit l'autoextent a fonctionné mais les fichiers sont revenus à leur taille initiale après l'échec de l'instruction CREATE INDEX.

Quelqu'un peut-il m'éclairer sur la méthode d'allocation successive de l'espace dans le cas d'un échec ?

[apad]

Dans votre exemple, la création de l'index augmente de 5 Mo la taille du datafile. Il serait intéressant de savoir ce qu'il se passerait si vous ne disposiez que de 4 Mo. Les 4 premiers Mo seraient-ils toujours alloués après l'échec de la 5ème extension ?

Pour répondre au point sur le journal d'alerte : Non, je n'ai pas d'autres messages que la cascade d'erreurs provoquée par l'echec d'allocation :
ORA-12801: erreur signalée dans le serveur d'interrogation parallèle P001
ORA-01652: impossible d'étendre le segment temporaire de 8192 dans le tablespace TBS_IDX.