Juste pour que vous compreniez bien l’intérêt du post précédent à propos de DBMS_REDEFINITION : ALTER TABLE MOVE sur autre chose qu’un IOT nécessite que l’activité soit arrêtée. Voici pourquoi !
Supposons une petite table…
create table demo(
id number,
text varchar2(1),
constraint demo_pk
primary key(id));
insert into demo values (1,'A');
commit;
Déplacer votre segment
Vous pouvez avoir envie de reconstruire/déplacer cette petite table ! Par exemple, pour une sombre histoire de « High Watermark » dans les fichiers de données que vous voulez réduire parce le DBA qui vous a précédé à fait n’importe quoi !
Pourquoi ne pas utiliser ALTER TABLE … MOVE puisque au pire, les sessions devrait attendre quelques secondes ? Enfin, c’est ce qu’on peut imaginer ! Et que l’exemple qui suit semble montrer :
Session 1 :
-----------
insert into demo values (2,'B');
Session 2 :
-----------
--
-- Note : This is a 11g feature and 10g
-- ------ would require you'd use :
-- "lock table ... in exclusive mode"
alter session set
ddl_lock_timeout = 1000;
alter table demo move
tablespace users;
Session 3 :
-----------
update demo
set text='B'
where id=1;
Remarquez que dans cet exemple, la session 1 bloque la session 2 qui elle-même bloque la session 3. Si vous validez ou annulez la première session. la session 2 reconstruit la table immédiatement et la session 3 (Puisqu’il s’agit d’un ordre DDL) est exécutée des que la session 2 termine son operation… soit presque immédiatement ;
Session 1 :
-----------
commit;
Session 2 :
-----------
Table altered.
Session 3 :
-----------
1 row updated.
il n’y a pas d’erreur, victoire ?
Maintenant un cas moins favorable !
En fait, il faut savoir que quand la table est reconstruite avec un « ALTER TABLE … MOVE », les index ne suivent pas deviennent unusable ! La requête suivante permet de s’en persuader :
select index_name, status
from user_indexes
where table_name='DEMO';
INDEX_NAME STATUS
---------- --------
DEMO_PK UNUSABLE
Et par conséquent, les ordres qui doivent valider la contrainte primary key echouent comme ci-dessous :
insert into demo values(3,'C')
*
ERROR at line 1:
ORA-01502: index 'DEMO.DEMO_PK' or partition
of such index is in unusable state
Et alors ? Il suffit de reconstruire l’index, non ?
alter index demo_pk
rebuild;
Index altered.
insert into demo values(3,'C');
1 row created.
Bon et bien facile !
Il suffit de faire le rebuild des index uniques juste après faire le « alter table… move » !
Mais c’est plus facile à dire qu’à faire ; les ordres DDL ne peuvent pas être exécutés dans la même transaction ! La seule manière que j’ai trouvé pour l’instant, c’est bien d’arrêter l’application ou au moins de l’empêcher pendant cette période de faire des opérations DML. La commande « alter table read only » pourrait être utile, si seulement mon application savait gerer :
ORA-12081: update operation not allowed
on table "DEMO"."DEMO"
Bien sur, on peut passer la base de données en mode QUIESCE, ce qui assurera qu’il y a pas de transaction en cours… remarquez que ça reviens au même meme si c’est une operation technique sur la base de données et est sans doute plus rapide (Quand ça marche !).
Conclusions :
- DBMS_REDEFINITION, c’est bien, même pour des petites tables ! Si seulement je pouvais me débarrasser de ces bases 9i
- Je ne vois pas de moyen de s’en sortir élégamment dans ce cas avec ALTER TABLE… MOVE ; et vous ?
Notes pour la suite :
- La raison pour laquelle les index deviennent « unusable » est assez évidente : comme ceux ci contiennent les rowid, le fait que les données de table se déplace invalide de fait toutes ces valeurs dans les index ! Comme je l’ai déjà dit, il faudrait reconstruire les index dans la foulée en garantissant que les ordres au moins DML (Et les selects pour les plans) soient bloqués sur cette table le temps du changement. En dehors d’une syntaxe que je ne connais pas, je ne vois pas comment ça serait possible en 11g Release 1.
- La raison pour laquelle c’est différent avec les IOT, est également évidente : les index sur les IOT ne contiennent pas le ROWID des lignes mais la clé primaire de l’IOT ; Une preuve de plus que les IOT peuvent être un outil intéressant si le TPC-C ne suffisait pas.
