[PLSQL]Suppression de plus de 100000 enregistrements

Version imprimable

[RESOLU][PLSQL]Suppression de plus de 100000 enregistrements

Bonjour,

Afin de créer une fonction d'archivage sur ma base, j'essaie de créer un script d'ajout dans un autre schéma et de suppression dans le schéma de production. Mais, voilà, j'ai un premier problème qui me dit que mes rollbacks segments sont trop petits. Ok, j'essaie donc de créer un mini-script pour contourner le problème et supprimer les lignes petit à petit. Je ne sais ps s'il est judicieux sur tous les points mais le voici quand même :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 declare cursor CURS_PICKDETAIL is select count(*) from PICKDETAIL where EXPORTFLAG = 'E'; NbPickDetail VARCHAR2(50); begin LOOP Open CURS_PICKDETAIL; Fetch CURS_PICKDETAIL into NbPickDetail; close CURS_PICKDETAIL; Exit when NbPickDetail = 0; set transaction use rollback segment RBSPEC; delete from WMSP.PICKDETAIL where EXPORTFLAG = 'E' and rownum<1000; commit; END LOOP; End;
Mais, hélas, j'ai maintenant un nouveau problème. J'ai le message suivant :
Code:

1 2 3 ORA-08102: index key not found, obj# 21869, dba 50406478 (2) ORA-06512: at line 13
J'ai cherché à savoir quel était cet index. C'est un index existant sur ma table PICKDETAIL. Je ne comprends pas pourquoi j'ai ce message. Est-ce dû à une mauvaise gestion des rollback segments, ou encore au rownum que j'ai mis dans ma requête mais dont je ne suis pas très sûre qu'il soit très adapté ?
J'espère que vous pourrez me filer un coup de main et des idées en prime ! :wink:

Et en passant par un create as select ?

Code:

1
2
3
4
5
6
7
8
9
create table PICKDETAIL_NEW as select * from PICKDETAIL where EXPORTFLAG != 'E';
 
create PICKDETAIL_OLD as select * from PICKDETAIL;
 
truncate table PICKDETAIL drop storage;
 
insert into PICKDETAIL select * from PICKDETAIL_NEW;
 
commit;

22/10/2004, 11h32
Félia
Juste une précision après de nouveaux tests. J'ai lancé la même chose sur une autre table (même ordre de grandeur). Là, pas de problème, le script a bien tourné. Les suppressions ont été correctement faites.
Je l'ai alors lancé sur une troisième table (pour confirmation :!: ). Et là, j'ai eu une erreur différente de sur la première :
Code:

1 2 3 ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [] ORA-06512: at line 13
Quel est votre avis ?
Merci de votre aide
22/10/2004, 11h43
getupa

Redémarre ta base ...
LEs internal error c'est jamais très bon ...
22/10/2004, 11h57
CD

Oui, ce n'est jamais très bon.

Tu as une note sur Metalink concernant ton erreur:
http://metalink.oracle.com/metalink/...T&p_id=39553.1
22/10/2004, 12h12
Félia

Ok, je redémarre ma base puis je teste la méthode de CD qui m'a l'air franchement mieux que la mienne.
Je vous tiens au courant. Merci. :P

Citation:
Envoyé par CD
Code:

1 2 3 4 create table PICKDETAIL_NEW as select * from PICKDETAIL where EXPORTFLAG != 'E'; create PICKDETAIL_OLD as select * from PICKDETAIL; (...)
Oupsss... Erreur d'inattention, il faut lire :

Code:

create table PICKDETAIL_OLD as select * from PICKDETAIL;

22/10/2004, 12h40
lalystar
Personnellement, je choisirais un gros rollback segment comme tu le fais, et pour la suppression je ferais directement :
Code:

1 2 3 delete from WMSP.PICKDETAIL where EXPORTFLAG = 'E'
Laly.
22/10/2004, 14h12
txouki

bonjour,
ce qui serait interessant de savoir dans ton cas est :
Est-ce un traitement regulier?
Tu surprimes 10.000 lignes sur combien?

Ta table a des index et peut etre des stats il faudrait penser à remettre cela d'aplon si ton traitement est regulier.
A faire trop de delete sur une table on modifie sa HWM.
Perso sur de la forte volumetrie(>1 M lignes) je prefere recréer la table.
En dessous de 100.000 j'utilise delete
A+
22/10/2004, 14h23
thomasjcj

Citation:

Envoyé par txouki

A faire trop de delete sur une table on modifie sa HWM.

Non, c'est justement le probleme, un DELETE ne modifie pas la HWM; seul un TRUNCATE remet la HWM a zero.
22/10/2004, 14h43
txouki

Ouais , je me suis mal exprimé(desolé) , delete ne modifie HWM de la table alors que la HWM de la table est modifiée d'ou plantage possible sur explain plan

Je comprends pas bien tout ce que vous me dites alors je préfère vous donner des précisions.
Dans mon cas, il s'agit là d'un premier archivage sur cette bdd. J'ai donc quelque 100 000 enregistrements sur plusieurs tables. Ensuite, il sera lancé automatiquement une fois par semaine donc je n'aurais plus beaucoup de lignes. Toutefois, je préfère me baser sur ce premier archivage afin d'assurer une certaine pérennité à mon script.
On a une appli qui est sensé faire cet archivage suite à un dév spécifique qu'on a demandé à notre éditeur. Mais, hélas, c'est une catastrophe parce qu'ils sont pas foutu de bosser correctement.
Conclusion, j'essaie de m'y coller à partir d'un de leur script et de shunter l'appli en complétant leur propre script.
Le premier script (celui fourni par l'éditeur) met un flag sur chacune des lignes à archiver en tenant compte des contraintes ... Dans cette partie du script, j'ai également affaire à des analyses régulières des tables modifiées.
Mon propre script n'a donc plus à s'occuper que de passer les lignes à archiver d'un schéma à un autre en se basant exclusivement sur le flag. Je viens de lancer le script en test en utilisant la méthode de CD adaptée à ma sauce, soit :
Code:

1 2 3 4 5 6 7 8 9 10 11 set transaction use rollback segment RBSPEC; insert into ARCHIVE.TMP_PICKDETAIL select * from WMSP.PICKDETAIL where EXPORTFLAG <> ‘E’; insert into ARCHIVE.PICKDETAIL select * from WMSP. PICKDETAIL where EXPORTFLAG = 'E'; truncate table WMSP.PICKDETAIL drop storage; insert into WMSP.PICKDETAIL select * from ARCHIVE.TMP_PICKDETAIL; truncate table ARCHIVE.TMP_PICKDETAIL; commit;
J'attends de voir les résultats car j'ai ré-importé ma base de test et complètement relancé le script initial avant cette petite partie de code.
Je vous tiens au courant. Si vous avez un avis à donner sur la question, n'hésitez pas à me le faire connaître. Merci pour tout. :wink:

Au fait, ça veut dire quoi HWM ?
Et une autre question, outre les analyses, txouki parle de faire quelque chose sur les indexes : peux-tu me dire quoi et comment ? :lol:

Merci.

22/10/2004, 15h38
orafrance

HWM = High Water Mark ;)

http://oracle.developpez.com/guide/a...lespaces/#L1.4
22/10/2004, 15h53
Félia

Merci pour le renseignement.
Concernant l'évolution de mes tests, j'ai dû mettre un nouveau commit au milieu de mon petit script. Pas très propre mais je ne vois pas comment faire autrement.
De plus, j'en suis à la ligne finale qui fait un truncate sur ma table mais là, il ne veut pas le faire pour soucis de contrainte. Je voudrais shunter ce point puisque l'intégrité de mes données est prise en compte par l'ensemble de mon script mais pas forcément table par table. Je cherche un moyen d'y arriver.
A+.
22/10/2004, 16h49
txouki
Si j'ai bien tout compris
1- tu recupres tes lignes d'archive dans ton nv schema
Code:

1 2 3 insert into ARCHIVE.PICKDETAIL select * from WMSP. PICKDETAIL where EXPORTFLAG = 'E'
Apres il faut que tu fasse un maj des ligne dans la base prod des tes données archivé
1-update sur le flag
cela evite de toucher au indexes sur les autre columne qui de plus possede a priori des contraintes

2- drop/create suivant la methode CD
Code:

1 2 3 create table PICKDETAIL_NEW as select * from PICKDETAIL where EXPORTFLAG != 'E'; DROP TABLE PICKDETAIL CASCADE CONSTRAINT ALTER TABLE PICKDETAIL_NEW rename to PICKDETAIL
il te reste plus qu'a remettre les index + les contraintes
Peut etre un peu lourd pour 10.000 lignes!
23/10/2004, 05h56
lalystar

Félia,

Et pourquoi tu ne créerais pas des partitions dans ta table. Tu pourrais (si tu es sous 9i) faire une table partitionnée par liste sur la colonne exportflag. Tous les enregistrements avec 'E' serait dans une partition. Quand une ligne pour laquelle le flag est différent de E est mise à E, elle serait déplacé dans la bonne partition...

Pour supprimer les enregistrements pour lesquels exportflag='E', ca reviendrait juste à faire un truncate au niveau de la partition donc très rapide voire instantané.

Tu dis que c'est pour de l'archivage ? Tu peux préciser ? Tu entends pas là historisation ?

Laly.

Bonjour,

Quelques nouvelles pour mon script : j'ai réussi à pas mal avancer sur le sujet. Je vous montrerai son détail lorsqu'il sera complètement terminé. Et, d'ailleurs, je vous remercie de votre aide qui m'a été très précieuse.

Mais, il me reste un dernier détail à régler. Je pense que ma question va vous paraître bête mais si je ne la pose pas, je continuerai à chercher pendant des lustres. Je cherche à mettre l'intégralité de mon script dans une procédure stockée. Mais celle-ci ne peut pas être compilée car elle plante à deux endroits (pour l'instant ...) :
- sur un ALTER ROLLBACK SEGMENT
Code:

1 2 3 4 5 6 7 ... DBMS_STATS.GATHER_TABLE_STATS('WMSP', 'TRANSFERDETAIL', METHOD_OPT => 'FOR COLUMNS SIZE 2 EXPORTFLAG'); ALTER ROLLBACK SEGMENT "RBSPEC" ONLINE; Utl_file.put_line(Fichier, EvolutionFlag||' Ok'); ...
- et sur mes truncate
Code:

1 2 3 4 5 6 7 8 9 10 11 12 set transaction use rollback segment RBSPEC; insert into ARCHIVE.TMP_ORDERDETAIL select * from WMSP.ORDERDETAIL where EXPORTFLAG <> 'E'; insert into ARCHIVE.ORDERDETAIL select * from WMSP. ORDERDETAIL where EXPORTFLAG = 'E'; alter table WMSP.ORDERDETAILXVAS disable constraint FK_ORDERDETAILXVAS02; truncate table WMSP.ORDERDETAIL drop storage; alter table WMSP.ORDERDETAILXVAS enable constraint FK_ORDERDETAILXVAS02; commit;
Le message d'erreur est approximativement le même :
Code:

1 2 3 PLS-00103: Encountered the symbol "ALTER" when expecting one of the following: ( ) - + mod not null others <an identifier>
Du coup, je me demande si le PL/SQL accepte qu'on lui mélange du SQL (sorti des classiques select, insert, update et delete). Quelqu'un peut-il m'aiguiller ? Par quoi dois-je remplacer mes instructuctions ?
Merci à vous tous, 8)

28/10/2004, 12h20
lalystar

C'est parce que ces instructions ALTER, TRUNCATE doivent être "encapsulée" (pourquoi :koi:) dans le PL/SQL en utilisant un execute immediate par exemple.

Laly.

Bonjour,

Je tenais à vous faire part des résultats de mes différents tests (un peu en retard, mais bon !).
J'ai fait deux versions de mon script : une pour le premier lancement (gros archivage) et un pour un lancement régulier. Voici un extrait de chacun. Ceux-ci fonctionnent et sont maintenant en prod :
Gros archivage : Je dois impérativement le lancer hors production
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 set transaction use rollback segment RBSPEC; insert into ARCHIVE.TMP_PICKDETAIL select * from WMSP.PICKDETAIL where EXPORTFLAG <> 'E'; insert into ARCHIVE.PICKDETAIL select * from WMSP.PICKDETAIL where EXPORTFLAG = 'E'; execute immediate 'truncate table WMSP.PICKDETAIL drop storage'; commit; set transaction use rollback segment RBSPEC; insert into WMSP.PICKDETAIL select * from ARCHIVE.TMP_PICKDETAIL; execute immediate 'truncate table ARCHIVE.TMP_PICKDETAIL drop storage'; commit;
Petit archivage : Je peux le lancer pendant que les gens travaillent
Code:

1 2 3 4 5 6 set transaction use rollback segment RBSPEC; insert into ARCHIVE.PICKDETAIL select * from WMSP.PICKDETAIL where EXPORTFLAG = 'E'; execute immediate 'delete from WMSP.PICKDETAIL where EXPORTFLAG = ‘’E’’'; commit;
Et voilà ! Je remercie tout ceux qui m'ont filé un coup de main ! :wink:
A+.