Danger du ALTER TABLE . . . MOVE . . .

	Info TAFORA
	Méthodes Conseil
	Mon credo Oracle
	Nouveautés
	Expertise technique
	Fiches Techniques
	Séminaires

Technique
	FAQ
Domaines
	DBA
	Tuning
	PL/SQL
Divers
	Varia
	Liens
Contact
	TAFORA
	Radu
	Mail

FAQ/DBA
Danger du alter table move …

Oracle 8i permet de déplacer une table d’un tablespace vers un autre (pour les IOTs, nous pouvons employer un alter table … move online). Cette nouvelle possibilité nous épargne beaucoup d’exercice de gymnastique. Sans décrire le procédé, j’attire votre attention sur un danger subréceptif de cette opération. Je reprends le courrier fait en oradu, le mailing list.

D'accord pour la solution 8i, effectivement très cool, seul bémol, vérifier les états des index associés à la table après le move (:-) pour notion, ;-( pour le résultat !). Vu que les rowids bougent, attention également aux effets pervers (pendant l'opération)!

Voici un exercice simple, pere/fils, assez dangereux !

-- nettoyage

SQL> drop table fils;

Table dropped.

SQL> drop table peres;

Table dropped.

SQL> create table peres (
  2    id number (3) primary key,
  3    nom     varchar2(10),
  4    prenom  varchar2(10));

Table created.

SQL> create table  fils (
  2    pereid  number (5) references peres(id),
  3    id      number (5),
  4    nom     varchar2(10),
  5    prenom  varchar2(10));

Table created.

-- un index sur la colonne qui reference

SQL> create index ixfils on fils(pereid);

Index created.

-- un pere, deux momes

SQL> insert into peres values (1,'Caulea', 'Radu');

1 row created.

SQL> insert into fils  values (1,1,'Caulea', 'Stefan');

1 row created.

SQL> insert into fils  values (1,2,'Caulea', 'Andrea');

1 row created.

SQL> commit;
Commit complete.
SQL> select index_name, status from user_indexes where table_name='FILS';

INDEX_NAME STATUS

------------------------------ --------

IXFILS VALID

--bougeons !

SQL> alter table fils move;

Table altered.

SQL> select index_name, status from user_indexes where table_name='FILS';

--aha !

INDEX_NAME STATUS

------------------------------ --------

IXFILS UNUSABLE

SQL> delete from peres;

delete from peres

ERROR at line 1:

ORA-02292: integrity constraint (WWWCTX.SYS_C001528) violated - child record found

-- jusqu'ici, tout se comporte normalement

SQL> insert into peres values (2,'Maitre', 'Celibat');

1 row created.

SQL> insert into fils  values (2,1,'FilsdeM', 'Celibat');

insert into fils values (2,1,'FilsdeM', 'Celibat')

ERROR at line 1:

ORA-01502: index 'WWWCTX.IXFILS' or partition of such index is in unusable

state

-- la merde s'annonce: pendant la production bien maîtrisée, on essaie d'insérer un père et ensuite un fils (sans commit entre les deux, parce qu'on nous a appris qu'il n'est pas sain de commiter à tour de bras ;-)). Que nenni, l'insertion du fils est rollbacké

Bon, on est au courant du comportement de l'index, donc on l'empêche de nous empêcher ;-)

SQL> alter session set skip_unusable_indexes=true;

Session altered.

SQL> insert into fils  values (2,1,'FilsdeM', 'Celibat');

insert into fils values (2,1,'FilsdeM', 'Celibat')

ERROR at line 1:

ORA-02291: integrity constraint (WWWCTX.SYS_C001528) violated - parent key not found

-- normal, on n'a pas de père, la contrainte fonctionne donc (toujours, dit-on)

SQL> insert into peres values (2,'Maitre', 'Celibat');

1 row created.

SQL> insert into fils  values (2,1,'FilsdeM', 'Celibat');

1 row created.

-- caca, la suite fonctionne !!!!!!!!!!!!!!!!

SQL> delete from peres where id=2;

1 row deleted.

-- on n'a qu'a altérer l'index du fils et espérer que ça n'arrivera pas en prod !

Radu Caulea,

Senior Oracle Consultant

www.caulea.com


Copyright © 1998-2012 Radu Caulea, TAFORA	MAJ 19/05/2012
Commentaires et suggestions radu[CHEZ]tafora.fr