Nettoyage du tablespace SYSTEM

[CinePhil]

Bonjour et bonne année à tous les oracliens.

Cadeau de début d'année, je me retrouve ce matin avec une jolie erreur ORA-01653 sur le tablespace SYSTEM !

J'ai pu augmenter la taille du data_file à 900 000 000 mais y aurait-il des choses à nettoyer dans les tables de SYSTEM, SYS ou autres qui utilisent ce tablespace.

Est-ce que cette taille vous semble normale ou trop grande ?

[jkofr]

Hello,

Peu-etre aud$?

jko

[Alderick]

Une petite requête rapidement écrite pour voir éventuellement les objets qui n'auraient pas du être créés dans ce tablespace :

Code :

SELECT OWNER||'.'||SEGMENT_NAME FROM dba_segments WHERE TABLESPACE_NAME='SYSTEM' AND OWNER NOT IN ('SYSTEM', 'SYS');

Cela ne doit pas être ton cas mais ça peut toujours aider un débutant désespéré qui tombe sur ce post.

Sinon, en tout rigueur ... il est difficile de dire si la taille de system est correcte sans connaitre la volumétrie de ta base.
Si tu n'as que deux schémas et trois tables ... c'est inquiétant ... si tu répertories (à leur insu bien sur) les informations confidentielles de tous les braves utilisateurs du réseaux social le plus en vogue ... ça se discute.

Je t'inviterais d'abord à regarder quels sont tes objects les plus volumineux :

Code :

SELECT owner, segment_name, segment_type, bytes/1024/1024 FROM dba_segments WHERE tablespace_name = 'SYSTEM' ORDER BY 4;

[CinePhil]

Pas bête en effet !

Grâce au premier résultat affiché ici, et après avoir jeté un œil à la structure et au contenu de la table, je comprends qu'il s'agit de la table qui stocke l'audit de la BDD.
Y figurent plus de 1 400 000 lignes, les premières datant de 2010 ! Autrement dit, elle n'a jamais été purgée depuis l'installation d'Oracle.

Je lis aussi sur le lien donné plus haut :

la table SYS.AUD$ doit être surveillée et purgée par un DELETE (ou mieux un TRUNCATE) explicite de Mr SYS si nécessaire

Que préconisez-vous comme méthode de purge ? Quelle fréquence ? Conserver quelle ancienneté de lignes d'audit ?

[jkofr]

Hello,

pour la période de rétention, cela dépend des besoins du business...

Avant cela n'était pas supporté de déplacer cette table vers un autre tablespace.
C'était possible, mais NON supporté.

Depuis la 11g il existe une procédure pour déplacer cette table AUD$ vers un tablespace dédié.

Package:
DBMS_AUDIT_MGMT
Procédure
SET_AUDIT_TRAIL_LOCATION

Bonne soirée.
jko

[CinePhil]

Un problème en entraîne un autre...

La base de prod sur laquelle j'ai rencontré le problème objet de cette de discussion est dupliquée 3 fois sur un serveur de test. Du coup, la partition qui héberge les fichiers des bases de test est pleine.
Comme j'étais connecté via SQL Developer sur l'une des bases de test quand on m'a signalé que la connexion au serveur de test était impossible, j'ai voulu purger un peu la fameuse table sys;aud$ de tout ce qui est plus ancien que 2012, grâce à cette requête :

Code SQL :

1
2
DELETE FROM sys.aud$
WHERE ntimestamp# < '31/12/11 23:59:59'

Un COUNT(*) préalable avec le même WHERE donnait quand même plus de 600 000 lignes à supprimer.

Dans SQL Developer, la requête tourne depuis au moins un quart d'heure. À votre avis, elle est plantée ?

EDIT : La requête a terminé son exécution.

[jkofr]

Envoyé par CinePhil

Un problème en entraîne un autre...

La base de prod sur laquelle j'ai rencontré le problème objet de cette de discussion est dupliquée 3 fois sur un serveur de test. Du coup, la partition qui héberge les fichiers des bases de test est pleine.
Comme j'étais connecté via SQL Developer sur l'une des bases de test quand on m'a signalé que la connexion au serveur de test était impossible, j'ai voulu purger un peu la fameuse table sys;aud$ de tout ce qui est plus ancien que 2012, grâce à cette requête :

Code SQL :

Un COUNT(*) préalable avec le même WHERE donnait quand même plus de 600 000 lignes à supprimer.

Dans SQL Developer, la requête tourne depuis au moins un quart d'heure. À votre avis, elle est plantée ?

En général c'est assez long :-)

Tu peux spider un peux avec du paralèlisme si tu es en enterprise edition:

Code SQL :

1
2
DELETE /*+ PARALLEL (t, 4) */ FROM sys.aud$ T
WHERE T.ntimestamp# < '31/12/11 23:59:59'

Tu peux allez faire un tour dans v$session et suivre les waits associés à ta session...

jko

[Waldar]

Faudrait regarder le plan d'exécution de la requête pour voir s'il y a utilisation d'un index ou non.
Il faut aussi regarder sur la table combien elle possède d'index et combien elle possède de lignes.

En plus les données sont mal typées, j'imagine que ntimestamp# est de type date ou timestamp.
Mais comme elle est comparée à du varchar, Oracle va convertir toute la colonne avant de faire son filtre.
Il faut vérifier pour le format de conversion implicite soit correct.
Et dans ce cas l'index - s'il existe - n'est pas utilisé.

Donc si l'index n'est pas utilisé, Oracle va lire toute la table, effacer une ligne, et recommencer cette opération 600.000 fois. Ça va prendre du temps !

Il vaut mieux tuer la transaction, vérifier la présence de l'index afin de le créer s'il n'existe pas, vérifier le type de la colonne ntimestamp# et passer avec un des deux where :

Code :

1
2
3
4
5
6
7
8
9
10
CREATE INDEX ix_aud_ntimestamp
ON sys.aud$ (ntimestamp#)
tablespace <tbs_idx>
parallel 4;
 
DELETE FROM sys.aud$
 WHERE ntimestamp# < to_date('01/01/2012', 'dd/mm/yyyy');
 WHERE ntimestamp# < to_timestamp('01/01/2012', 'dd/mm/yyyy');
 
DROP INDEX ix_aud_ntimestamp;

Edit : tant pis !

[jkofr]

Envoyé par Waldar

Donc si l'index n'est pas utilisé, Oracle va lire toute la table, effacer une ligne, et recommencer cette opération 600.000 fois. Ça va prendre du temps !

Pas daccord Waldar.
Il ne vas pas scanner la table 600 000 fois mais une seule fois.

Concernant l'index, la modification d'objects appartenant à sys n'est pas supportée par Oracle...

Jko

[Waldar]

Envoyé par jkofr

Il ne vas pas scanner la table 600 000 fois mais une seule fois.

Je me suis emballé !

Envoyé par jkofr

Concernant l'index, la modification d'objects appartenant à sys n'est pas supportée par Oracle...

L'index peut être mis ailleurs que dans sys, comme ça pas de modification sur ce schéma.

[mnitu]

Envoyé par Waldar

...
L'index peut être mis ailleurs que dans sys, comme ça pas de modification sur ce schéma.

Il ne faut pas toucher aux objets appartenant au sys sous menace de châtiment du purgatoire éternel.

[CinePhil]

Envoyé par mnitu

Il ne faut pas toucher aux objets appartenant au sys sous menace de châtiment du purgatoire éternel.

Mais purger sa table AUD$ ne nous précipite pas en enfer j'espère ?

[Waldar]

C'est dangereux Oracle en fait !

[CinePhil]

Ben je suppose qu'un oracle peut donner une bonne nouvelle comme prédire une catastrophe !

[mnitu]

Envoyé par CinePhil

Mais purger sa table AUD$ ne nous précipite pas en enfer j'espère ?

Aucun souci pour ça! J'ai parlai juste de la création des indexes sur des objets appartenant à sys dans des autres schémas que sys.

[Edit]
Quoi que ça sera mieux pour la sauvergarde de nos amês d'utiliser si possible les outils d'Oracle :
Auditing Enhancements (DBMS_AUDIT_MGMT) in Oracle Database 11g Release 2