Discussion :

[kurkLord]

Bonjour,
j'ai créé un script de purge mensuel qui est le suivant :

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
CREATE OR REPLACE PROCEDURE PURGE
IS
	cnt_transfert_actif PLS_INTEGER;
	mois_courant USER_TAB_PARTITIONS.partition_position%TYPE;
 
	CURSOR partition_cur (mois_courant USER_TAB_PARTITIONS.partition_position%TYPE) IS
		SELECT DISTINCT partition_name, partition_position
		FROM USER_TAB_PARTITIONS
		WHERE partition_position != mois_courant
		ORDER BY partition_position;
 
BEGIN
 
	SELECT TO_NUMBER (TO_CHAR (SYSDATE, 'MM'))
		INTO mois_courant
	FROM DUAL;
 
	FOR partition_rec IN partition_cur (mois_courant)
	LOOP
		DBMS_OUTPUT.put_line ('La purge du mois de ' || partition_rec.partition_name || ' a commence');						
		EXECUTE IMMEDIATE 'ALTER TABLE INSTANCE_PAR_TRANSFERT TRUNCATE PARTITION ' || partition_rec.partition_name || ' REUSE STORAGE';
		EXECUTE IMMEDIATE 'ALTER TABLE TACHE_ELEMENTAIRE TRUNCATE PARTITION ' || partition_rec.partition_name || ' REUSE STORAGE';
		EXECUTE IMMEDIATE 'ALTER TABLE ETAT_PAR_TRANSFERT TRUNCATE PARTITION ' || partition_rec.partition_name || ' REUSE STORAGE';
		EXECUTE IMMEDIATE 'ALTER TABLE TRANSFERT TRUNCATE PARTITION ' || partition_rec.partition_name || ' REUSE STORAGE';
		DBMS_OUTPUT.put_line ('La purge du mois de ' || partition_rec.partition_name || ' termine');
		COMMIT;
	END LOOP;
END PURGE;
/

Lorsque je le lance, j'obtiens ça comme erreur :

begin
purge;
end;
ORA-02266: Les clés primaires/uniques de la table référencées par des clés étrangères
ORA-06512: à "SEB.PURGE", ligne 24
ORA-06512: à ligne 2

Une clef primaire est référencé dans d'autres tables. Je ne vois pas trop
comment faire pour m'en sortir. Je travaille sous Toad & Oracle 9i

[pifor]

D'après:

$ oerr ora 2266
02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"
// *Cause: An attempt was made to truncate a table with unique or
// primary keys referenced by foreign keys enabled in another table.
// Other operations not allowed are dropping/truncating a partition of a
// partitioned table or an ALTER TABLE EXCHANGE PARTITION.
// *Action: Before performing the above operations the table, disable the
// foreign key constraints in other tables. You can see what
// constraints are referencing a table by issuing the following
// command:
// SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
$

il devrait suffire de désactiver les contraintes FK sur la table avant l'exécution de la procédure avec:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
ALTER TABLE <table> MODIFY CONSTRAINT <contrainte> DISABLE;

et de les réactiver après l'exécution avec:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
ALTER TABLE <table> MODIFY CONSTRAINT <contrainte> ENABLE;

Ceci suppose que les données purgées ne sont pas référencées dans <table>, sinon il faut aussi purger les tables "enfant" (celles qui ont les clés étrangères).

[kurkLord]

Oui j'y ai pensé mais quand je le fait j'ai les messages d'erreur suivant :

ORA-00054: resource busy and acquire with NOWAIT specified

La purge que je souhaite faire doit être fait à chaud. On ne peut pas
arrêter l'application pendant la purge.

[pifor]

Je ne vois pas comment vous pouvez purger des partitions de tables ou modifier l'état de contraintes avec des utilisateurs connectés qui peuvent accéder aux données concernées: vous pouvez éventuellement essayer de verrouiller les tables avec LOCK TABLE mais
1. il faudra attendre que plus personnes n'accède aux tables
2. pendant la durée de la purge, toute tentative d'accès aux tables par les utilisateurs leur renverra ORA-00054 ou un message similaire.

[Nick_Holmes]

La purge que je souhaite faire doit être fait à chaud. On ne peut pas arrêter l'application pendant la purge.

Une purge ne peut pas être faite correctement à chaud sans LOCK car on peut se retrouver dans un état incohérent de base. un utilisateur peut modifier une table alors que le process va purger la partie de la table que l'utilisateur modifie. C'est le serpent qui se mord la queue.

@+,
NicK.

[kurkLord]

Locker une table est qd même HYPER restrictif. J'ai peu que mon application
plante à cause de ce genre de chose. Que va-t-il se passer si l'application
essayer justement d'accéder à la table verrouiller par oracle? Ca va planté ?

[j6m]

je ne comprends pas pourquoi purger des données en cours d'utilisation?

[kurkLord]

L'application sur laquelle je travaille doit tout simplement fournir une
haute disponibilité aux clients qui l'utilisents. L'application en question
est un module de service qui a un couplage fort avec la base de
données. Le module fournit des services web à des automates qu'il
ne connait pas. Les automates en questions pilotent des agents qui
executent des taches. Les taches sont en base. J'explicite en gros un
peu le contexte. Je fais court. Sachant que les automates et les agents
travaillent en continu. Le module de services web doit aussi travailler
en continu. Pire. Les autres modules peuvent tomber en panne mais
pas le module de services. Bref... Tout pour te dire que la purge doit
absolument se faire à chaud.

La purge que je dois faire se fait sur des partitions bien précises. Même
si l'on tronque des partitions le LOCK TABLE est nécéssaire voire
obligé dans mon cas ??

[j6m]

et en faisant un delete et commit avant de purger?
mais ça peut être long ça dépend des volumes

[kurkLord]

C'est l'une des premières choses auxquelles j'ai pensé. Mais étant la
vitesse à laquelle se remplissent les tables. Je crains de mobiliser
fortement le serveur à ne faire que des DELETE et plus autre chose.
Etant donné les commentaires, je pense qu'une purge à chaud tel que
je souhaite la faire n'est pas possible ? Et d'ordre général, est-ce que
ce type de purge est préconisé ? Comment un dba qui 'se respecte'
fera donc sa purge ? Est-ce en fonction du type de l'application ?

[j6m]

Bref... Tout pour te dire que la purge doit
absolument se faire à chaud.
Oui, on sait pourquoi elle doit se faire à chaud... Mais pourquoi elle doit se faire tout court? J'ai beau faire, je ne vois pas la raison de vouloir purger les données en cours?
En tout cas, entre le risque éventuel de ralentir l'appli parce que le delete a verrouillé un block, et celui que tu prends en verrouillant la table - alors même que ton appli doit pouvoir y accéder...
Il faudrait peut-être repenser le partitionnement?

[kurkLord]

j6m dans le script de purge que j'ai mis. Tu remarqueras que j'utilise des
partitions. Je ne pense pas avoir répondu à ta question mais je pensais
que le script l'aurai fait. Le but n'est pas de purger les donnée en cours
MAIS de purger les données qui ne sont plus utilisées dans les autres
partitions. Les partitions différentes du mois en cours. En outre avant
de purger de doit vérifier que toutes les tâches ont bien été faites par
un agent. J'espère que tu commences à mieux saisir le 'truc'. Repenser
le partitionnement ? As-tu des pistes ? Peux-tu m'en dire un peu plus ?

[Marc Musette]

pour le faire, tu pourrais retirer l'intégrité référentielle sur les tables (DISABLE des constraints)

en faisant cela, tu rends possible une incohérence si tu permets aux users de continuer à introduire de nouvelles données.

définis la contrainte foreign key ON DELETE CASCADE

et fais les truncate dans le bon ordre

[kurkLord]

Ok Marc! Mais le problème de faire un DISABLE sur la contraînte 'à chaud'
me renvoie toujours la même exception :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ORA-00054: Resource occupée et acquisition avec NOWAIT (pas d'attente) indiquée

ce qui fait que mon disable/enable échoue. Comment faire pour spécifier une attente ? Une autre solution serait
alors de créer mon schéma et de désactiver pour toujours les contraintes concernées ? Un peu brutal non ?

Concernant la modification des données. C'est mon application qui s'en charge. A moins qu'une intervient
extérieure se produise, une incohérence ne peut pas se produire.

[pifor]

Il existe un paramètre DDL_WAIT_FOR_LOCKS qui peut éviter ORA-00054 pour des opérations de ce type là mais selon Metalink ce paramètre ne fonctionne pas pour les versions de 10.1.0.2.0 to 10.2.0.0.0.

[kurkLord]

Etant donné que le gros problème de ma purge est le DISABLE puis l'ENABLE des contraintes sur une
table bien donnée. J'ai donc mis à la fin de mon script de création du schéma des alter table pour
modifier définitivement les contraintes en question. Après plusieurs tests, c'est plutot concluant. La purge
se réalise. Les données désuetes sont purgées et mieux encore, aucun message d'erreur et l'application
ne s'arrête pas. Si personne n'a autre chose à me proposer je vais donc rester sur cette solution. Un grand
merci à tous.

[j6m]

oups vraiment désolé j'vais raté le point d'exclamation dans le code. Donc on veut supprimer ailleurs que sur le mois courant.
Moi, disabler les contraintes je dis oula, surtout avec des sessions dessus, il vaut mieux freezer une table que de permettre qu'on y balance n'improte quoi. Une fois que les données seront corrompues y aura plus qu'à jeter la table.
Après, "repenser le partitionnement" :
pourquoi ces tables sont-elles partitionnées et comment?
apparemment ce sont des partitions au mois , quelle est la périodicité de la purge? Pas mensuelle j'espère ;-)
Si on est dans des partiitons au mois, à purger deux fois par an, il faudrait peut-être avoir deux mois dans la table partitionnée, et le reste dans des tables à part qui seront accessibles à une purge puisque pas en cours d'utilisation.
Enfin, le delete (encore une fois lui au moins ne verrouillera que le block). C'est quoi le voume de données? Quelle est la rapidité de remplissage?