ORA-01654 sur SYSAUX

Je suis tombée sur une erreur ORA-01654 car le tablespace SYSAUX était plein. Je pense qu'il n'avait pas forcément été bien dimensionné au départ (il faut utiliser le script utlsyxsz pour estimer la taille nécessaire à SYSAUX).

Cependant, n'étant pas propriétaire de la base et n'ayant pas l'autorisation d'augmenter le-dit SYSAUX, j'ai du chercher à grappiller quelques Mo.

Utilisation de SYSAUX

Pour savoir ce qui utilise SYSAUX, il suffit de lancer cette requête :

SELECT  occupant_name "Coupable",
space_usage_kbytes/1048576 "Espace occupé (Go)",
schema_name "Schema"
FROM v$sysaux_occupants
ORDER BY 2 desc;

Dans mon cas, c'était AWR qui occupait plus de la moitié du SYSAUX.

AWR et droit d'utilisation

Pour ce client, AWR était d'autant plus inutile qu'il n'avait pas payé le tuning pack. Il n'avait donc pas le droit de générer et d'aller voir les rapports AWR. Il se trouve qu ele client ne savait même pas ce que c'était. j'ai donc eu l'autorisation de purger les snapshots.

Purge des snapshots

Pour purger les snapshots, il faut savoir quel est le plus ancien et quel est le plus récent. Cette requête donne ces infos.

SELECT snap_id, begin_interval_time, end_interval_time 
FROM SYS.WRM$_SNAPSHOT 
WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
UNION 
SELECT snap_id, begin_interval_time, end_interval_time 
FROM SYS.WRM$_SNAPSHOT 
WHERE snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT); 

Il faut ensuite réutiliser ces valeurs dans le bloc PL/SQL suivant :

BEGIN
  dbms_workload_repository.drop_snapshot_range(low_snap_id => 14241, high_snap_id=>14321);
END;

Récupérer la place libérée

Il faut ensuite shrinker la table pour pouvoir réellement récupérer la place (il faut être connecté en sys as sysdba).

alter table sys.WRH$_ACTIVE_SESSION_HISTORY shrink space cascade;

Et après ?

La procédure ci-dessus n'est qu'une procédure d'urgence. Il faudra prévoir rapidement d'envisager au moins une des actions suivantes :

• Faire tourner le script utlsyxsz  pour évaluer le besoin réel d'espace de SYSAUX
• Ajouter un datafile à SYSAUX (ou augmenter les datafiles existants)
• Diminuer la fréquence et la rétention des snapshots d'AWR

Plan d'exécution

Il existe plusieurs manières de récupérer un plan d'exécution sous Oracle.

Autotrace only

Cette méthode est ma préférée. Elle aurait cependant l'inconvénient de nécessiter un exécution complète de la requête pour afficher le plan d'exécution. Je n'ai jamais rencontré de problème de lenteur avec cette méthode.
Il suffit d'ajouter la commande autotrace only pour qu'au lieu d'exécuter les requêtes, Oracle nous affiche le plan d'exécution. Je vous laisse faire les set linesize qui vont bien avant.

SET AUTOTRACE TRACEONLY EXPLAIN

Plan_table

Avec cette méthode, on demande la création du plan d'exécution (en lui donnant un id, tant qu'à faire). Le plan d'exécution est ensuite stocké dans la table plan_table. On peut donc l'afficher avec du SQL:

EXPLAIN PLAN SET statement_id='MonId' FOR SELECT * FROM maTable;
SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','MonId',null));

Et voilà vous avez votre plan d'exécution!

Oracle et les paramètres cachés...

Oracle est un petit cachottier qui comporte des paramètres "cachés". Il s'agit de tous les paramètres dont le nom commence par _.
En théorie, on ne doit setter ces paramètres que sur demande du support, mais j'ai découvert que parfois une simple note metalink suggère de setter l'un ou l'autre de ces paramètres...
Or, je n'aime pas setter un paramètre si je ne sais pas quelle valeur il a auparavant.

Consulter les paramètres cachés

J'ai trouvé une petite requête sur internet que j'ai améliorée à ma sauce pour donner ceci :

set lines 200
set pages 100
col NAME format a20
col TYPE format 9999
col VALUE format a20
col ISDEFAULT format a20
SELECT ksppinm AS NAME,ksppity AS TYPE,ksppstvl AS VALUE,ksppstdf AS ISDEFAULT
FROM x$ksppi x INNER JOIN x$ksppcv y ON (x.indx = y.indx)

Finalement, le TYPE n'est pas une info très intéressante, ou alors il faudrait refaire une jointure avec la table des types pour savoir ce qu'elle vaut vraiment.
Il n'y a plus qu'à rajouter votre clause 'WHERE ksppinm LIKE '''.
Les noms de paramètres sont stockées en minuscules.

Setter un paramètre caché

Comme pour un paramètre normal, il faut utiliser l'ordre ALTER SYSTEM SET... Il suffit de penser à mettre le nom du paramètre caché entre double quotes.