Discussion :

[domiq44]

Bonjour,

J'ai une base de données Oracle 11g.

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Les types XMLType et CLOB sont très présents dans ma base et consomment beaucoup de place disque.
Il a donc été demandé d'écrire un programme ou une requête SQL de purge qui supprimerait uniquement le contenu des colonnes typées XMLType ou CLOB en fonction de certaines conditions.

J'ai cru voir que le fait de mettre à null ces colonnes ne suffisait pas.

Quelqu'un peut-il me donner la démarche à suivre ?

Merci.

[domiq44]

Après quelques recherches et essais, j'ai trouvé ceci.
Mais tout cela reste à vérifier !

Tout d'abors, créer une table avec 2 champs : 1 XMLTYPE et 1 CLOB :

Code :

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

CREATE TABLE matable (a XMLTYPE, b CLOB);

Oracle crée un segment LOB et un index LOB pour chaque colonne LOB.

Pour obtenir les informations du segment LOB relatives à la table "MATABLE", on peut exécuter la requête suivante :

Code :

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

1
2
3
SELECT table_name, column_name, segment_name, index_name
  FROM user_lobs
 WHERE table_name = 'MATABLE';

et on obtient par exemple :

Code :

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

1
2
3
4
TABLE_NAME   COLUMN_NAME    SEGMENT_NAME                INDEX_NAME
----------   ------------   -------------------------   ------------------------
MATABLE      SYS_NC00002$   SYS_LOB0000142615C00002$$   SYS_IL0000142615C00002$$
MATABLE      B              SYS_LOB0000142615C00003$$   SYS_IL0000142615C00003$$

Remplissons cette table :

Code :

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

1
2
3
INSERT INTO matable
   SELECT XMLTYPE (DBMS_XMLQUERY.getxml ('SELECT * FROM all_objects WHERE ROWNUM <= 1000')), 'SELECT * FROM all_objects WHERE ROWNUM <= 1000'
     FROM DUAL;

et regardons la place mémoire occupée par ces 2 colonnes :

Code :

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

1
2
3
SELECT segment_name, segment_type, BYTES
  FROM user_segments
 WHERE segment_name IN ('SYS_LOB0000142615C00002$$', 'SYS_LOB0000142615C00003$$');

ou encore :

Code :

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

1
2
3
SELECT segment_name, segment_type, BYTES
  FROM user_segments
 WHERE segment_name IN (SELECT segment_name FROM user_lobs WHERE table_name = 'MATABLE');

ce qui donne :

Code :

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

1
2
3
4
SEGMENT_NAME                SEGMENT_TYPE   BYTES
-------------------------   ------------   -------- 
SYS_LOB0000142615C00002$$   LOBSEGMENT       589824
SYS_LOB0000142615C00003$$   LOBSEGMENT        65536

Pour libérer la place occupée par la valeur d'un CLOB ou d'un XMLTYPE il faut lui affecter la valeur NULL.
Par exemple :

Code :

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

1
2
UPDATE matable SET a = NULL, b = NULL;
COMMIT;

Mais cela ne suffit pas comme on le voit ci-après :

Code :

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

1
2
3
4
SEGMENT_NAME                SEGMENT_TYPE   BYTES
-------------------------   ------------   -------- 
SYS_LOB0000142615C00002$$   LOBSEGMENT       589824
SYS_LOB0000142615C00003$$   LOBSEGMENT        65536

Il faut aussi reconstruite le segment LOB comme ceci (pour le XMLType par exemple) :

Code :

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

1
2
ALTER TABLE matable MOVE LOB (SYS_NC00002$) STORE AS SYS_LOB0000142615C00002$$;
ALTER TABLE matable MOVE LOB (B) STORE AS SYS_LOB0000142615C00003$$;

Cela semble donc gagné à regarder le résultat :

Code :

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

1
2
3
4
SEGMENT_NAME                SEGMENT_TYPE   BYTES
-------------------------   ------------   -------- 
SYS_LOB0000142615C00002$$   LOBSEGMENT        65536
SYS_LOB0000142615C00003$$   LOBSEGMENT        65536

Est-ce que quelqu'un peut me confirmer cela ?
Merci.

[domiq44]

Bonjour,

Je vais partir sur cette base et je verrai bien.

Si d'ici là, quelqu'un à des informations sur le sujet, je reste prenneur.