Discussion :

[AlternantOracle]

Bonjour, j'ai le probleme suivant:

Je dois effacer plus de 300.000 enregistrements dans une table et ca va generer trop de redo logs ( pas assez d'espace disponible ). Alors vous me direz pas grave tu te mets en NO LOGGING et ca passe.

Oui mais voila le probleme si je ne me trompe pas, les redologs passent dans le processe d'archivage et ces archivelog sont envoyees sur la standby qui ne va pas pouvoir recuperer les modifications.

Je me trompe ? avez vous une technique de sioux a me donner ? :p

je suis a cours d'idees

[fred_04510]

Bonjour,
Peut être créer une table as select (de structure identique, de nom différent) qui contiendrait les enregistrements à garder.
Puis drop ancienne table et renommage de la nouvelle.

Frédéric.

[AlternantOracle]

L'idee n'est pas mauvaise mais la table a 36 milions de lignes donc le create table as select va etre assez ... consequent niveau espace disque.

Par rapport a notre contract nous n'avons pas le droit de modifier la structure car ca sera une belle breche dans laquelle ils vont s'enfiler si ca ne fonctionne plus.

J'ai envoye un ticket au support je vais vous tenir au courant.

[PxPar]

Si on reprend son idée de créer une autre table et de supprimer celle qui existe actuellement sauf que cette fois-ci tu fais un dump de ta table à archiver où tu veux , disque cassette ou autres et tu supprimes celle que as actuellement.

[Vincent Rogier]

Bonjour,
Peut être créer une table as select (de structure identique, de nom différent) qui contiendrait les enregistrements à garder.
Puis drop ancienne table et renommage de la nouvelle.

Frédéric.

cela n'est possible que si aucune contrainte référençant cette table n'existent...

[pachot]

Bonjour,

cela n'est possible que si aucune contrainte référençant cette table n'existent...

Dans ce cas, il est possible de désactiver ces contraines puis de les réactiver NOVALIDATE RELY vu qu'on est sur de ne pas avoir perdu de données en route.
Cordialement,
Franck.

[pifor]

cela n'est possible que si aucune contrainte référençant cette table n'existent...

De toute façon si la table concernée est une table parent référencée par une contrainte d'intégrité référentielle, on ne peut y supprimer des lignes que si ces lignes ne sont pas référencées dans une table enfant.

On peut faire cette opération DELETE avec un CTAS en supprimant la contrainte sur la table enfant avant renommage de la nouvelle table parent et en recréant la contrainte sur la table enfant pour référencer la nouvelle table. Exemple avec Oracle XE:

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
76
77
78
79
80
81
SQL> create table p(px int primary key, py int check (py > 0), pz varchar2(10) n
ot null);
 
Table created.
 
SQL> create table e(ex int primary key);
 
Table created.
 
SQL> alter table e add constraint efk foreign key(ex) references p;
 
Table altered.
 
SQL>
SQL> insert into p values(1,1,'un');
 
1 row created.
 
SQL> insert into p values(2,2,'deux');
 
1 row created.
 
SQL>
SQL> insert into e values(2);
 
1 row created.
 
SQL>
SQL> commit;
 
Commit complete.
 
SQL>
SQL> create table pp as select * from p where px >=2;
 
Table created.
 
SQL> alter table e drop constraint efk;
 
Table altered.
 
SQL> drop table p;
 
Table dropped.
 
SQL> alter table pp rename to p;
 
Table altered.
 
SQL> alter table p add primary key(px);
 
Table altered.
 
SQL> alter table e add constraint efk foreign key(ex) references p;
 
Table altered.
 
SQL>
SQL> set long 500
SQL> set pagesize 99
SQL> select dbms_metadata.get_ddl('TABLE', 'P', user) as ddl from dual;
 
DDL
--------------------------------------------------------------------------------
 
 
  CREATE TABLE "HR"."P"
   (    "PX" NUMBER(*,0),
        "PY" NUMBER(*,0),
        "PZ" VARCHAR2(10) NOT NULL ENABLE,
         PRIMARY KEY ("PX")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147
 
 
SQL>
SQL>

Il ne faut pas oublier de recréer les contraintes différentes de NOT NULL ainsi que les GRANT et les index.

[Vincent Rogier]

je ne parlais pas du delete mais du drop.

ta table a purger peut tres bien avoir une PK et d'autres tables des FK avec un cascade delete sur cette PK.

Le delete marche bien sur mais le drop, dans la proposition effectuée, est impossible car des FK référencent la table.
C'est l'objet de ma remarque.

De toute façon si la table concernée est une table parent référencée par une contrainte d'intégrité référentielle, on ne peut y supprimer des lignes que si ces lignes ne sont pas référencées dans une table enfant.

On peut faire cette opération DELETE avec un CTAS en supprimant la contrainte sur la table enfant avant renommage de la nouvelle table parent et en recréant la contrainte sur la table enfant pour référencer la nouvelle table. Exemple avec Oracle XE:

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
76
77
78
79
80
81
SQL> create table p(px int primary key, py int check (py > 0), pz varchar2(10) n
ot null);
 
Table created.
 
SQL> create table e(ex int primary key);
 
Table created.
 
SQL> alter table e add constraint efk foreign key(ex) references p;
 
Table altered.
 
SQL>
SQL> insert into p values(1,1,'un');
 
1 row created.
 
SQL> insert into p values(2,2,'deux');
 
1 row created.
 
SQL>
SQL> insert into e values(2);
 
1 row created.
 
SQL>
SQL> commit;
 
Commit complete.
 
SQL>
SQL> create table pp as select * from p where px >=2;
 
Table created.
 
SQL> alter table e drop constraint efk;
 
Table altered.
 
SQL> drop table p;
 
Table dropped.
 
SQL> alter table pp rename to p;
 
Table altered.
 
SQL> alter table p add primary key(px);
 
Table altered.
 
SQL> alter table e add constraint efk foreign key(ex) references p;
 
Table altered.
 
SQL>
SQL> set long 500
SQL> set pagesize 99
SQL> select dbms_metadata.get_ddl('TABLE', 'P', user) as ddl from dual;
 
DDL
--------------------------------------------------------------------------------
 
 
  CREATE TABLE "HR"."P"
   (    "PX" NUMBER(*,0),
        "PY" NUMBER(*,0),
        "PZ" VARCHAR2(10) NOT NULL ENABLE,
         PRIMARY KEY ("PX")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147
 
 
SQL>
SQL>

Il ne faut pas oublier de recréer les contraintes différentes de NOT NULL ainsi que les GRANT et les index.

[pifor]

je ne parlais pas du delete mais du drop.

ta table a purger peut tres bien avoir une PK et d'autres tables des FK avec un cascade delete sur cette PK.

Le delete marche bien sur mais le drop, dans la proposition effectuée, est impossible car des FK référencent la table.
C'est l'objet de ma remarque.

OK mais dans mon exemple je supprime bien cette FK et je la recrée sur la nouvelle table. Ceci doit fonctionner si la table enfant n'est pas en cours d'utilisation.

[pachot]

Bonjour,

Je dois effacer plus de 300.000 enregistrements dans une table

... dans une table de combien d'enrgistrements en tout ?

Parce que l'import du dump va générer lui du redo log pour les enregistrements à garder.

Et la taille des enregistrements va jouer aussi.Parce que le redo d'un delete, c'est la taille du ROWID seulement, alors que le redo d'un insert, c'est toutes els colonnes de l'enregistrement.

Donc si c'est 300.000 enregistrements parmi 600.000 ce n'est pas sûr que tu génères moins de log.

Pour le 'create as select', c'est un peu mieux. il est en mode direct, donc il ne génère pas d'undo (qui lui aussi est source de redo).

Cordialement,
Franck