21C - XE - SYSAUX01.DBF trop important

**Soticca** · 12/12/2022, 15h32

Bonjour à tous,

Je viens de m'apercevoir que j'ai le message d'erreur suivant sur certaines requêtes :

ORA-12954: La demande dépasse la taille maximale de base de données autorisée (12 Go)

Nous utilisons Oracle XE 21c et je pense que le soucis provient du fichier SYSAUX01.DBF qui a atteint quasi 10 Go et qui, couplé avec le fichier de donnée à 2,5 Go, fait que XE dépasse les 12Go.

Est-ce qu'il existe un moyen de réduire la taille de ce fichier ? N'étant pas administrateur de bdd mais développeur, je préfère prendre des infos avant de faire des tests.

Voici les chiffres actuels :

Nom : 2022-12-12_15h25_25.png
Affichages : 307
Taille : 105,2 Ko

Nom : 2022-12-12_15h25_25.png
Affichages : 307
Taille : 105,2 Ko

**Mohamed.Houri** · 12/12/2022, 19h00

Exécutez la requête suivante et postez le résultat ici afin que je puisse voir qui occupe quoi dans sysaux

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
col occupant_name for a50
compute sum label 'Total Size' of MB on report
break on report
select 
     occupant_name
    ,occupant_desc
    ,round(space_usage_kbytes/power(1024,1),2) MB
from  
    v$sysaux_occupants
where
   space_usage_kbytes >0
order  by space_usage_kbytes desc;
 
 
SQL> @sysOccup
 
OCCUPANT_NAME                                      OCCUPANT_DESC                                                            MB
-------------------------------------------------- ---------------------------------------------------------------- ----------
SDO                                                Oracle Spatial                                                       168,94
SM/AWR                                             Server Manageability - Automatic Workload Repository                    126
XDB                                                XDB                                                                   68,38
SM/OPTSTAT                                         Server Manageability - Optimizer Statistics History                   65,44
SM/OTHER                                           Server Manageability - Other Components                               50,81
AO                                                 Analytical Workspace Object Table                                     45,81
SM/ADVISOR                                         Server Manageability - Advisor Framework                              28,75
JOB_SCHEDULER                                      Unified Job Scheduler                                                 13,69
LOGMNR                                             LogMiner                                                              10,81
WM                                                 Workspace Manager                                                      6,56
SQL_MANAGEMENT_BASE                                SQL Management Base Schema                                             3,75
SMON_SCN_TIME                                      Transaction Layer - SCN to TIME mapping                                3,38
PL/SCOPE                                           PL/SQL Identifier Collection                                           2,88
TEXT                                               Oracle Text                                                            2,81
STREAMS                                            Oracle Streams                                                         1,69
LOGSTDBY                                           Logical Standby                                                        1,56
AUDSYS                                             AUDSYS schema objects                                                  1,25
AUTO_TASK                                          Automated Maintenance Tasks                                             ,56
EM_MONITORING_USER                                 Enterprise Manager Monitoring User                                       ,5
                                                                                                                    ----------
Total Size                                                                                                              603,57
 
19 rows selected.

Bien à vous
Mohamed Houri

**Soticca** · 13/12/2022, 09h01

Bonjour,

Voici le résultat de la requête. Il semblerait que SM/ADVISOR soit bien rempli (par contre, cela ne me parle pas plus que ça)

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
 
OCCUPANT_NAME                                      OCCUPANT_DESC                                                            MB
-------------------------------------------------- ---------------------------------------------------------------- ----------
SM/ADVISOR                                         Server Manageability - Advisor Framework                            6170,31
SM/OTHER                                           Server Manageability - Other Components                             2745,81
SM/OPTSTAT                                         Server Manageability - Optimizer Statistics History                     208
SM/AWR                                             Server Manageability - Automatic Workload Repository                 108,19
SDO                                                Oracle Spatial                                                        91,88
XDB                                                XDB                                                                   91,75
AUDSYS                                             AUDSYS schema objects                                                 53,88
AO                                                 Analytical Workspace Object Table                                     46,81
WM                                                 Workspace Manager                                                      7,31
JOB_SCHEDULER                                      Unified Job Scheduler                                                  5,81
SMON_SCN_TIME                                      Transaction Layer - SCN to TIME mapping                                3,38
 
OCCUPANT_NAME                                      OCCUPANT_DESC                                                            MB
-------------------------------------------------- ---------------------------------------------------------------- ----------
TEXT                                               Oracle Text                                                            2,94
SQL_MANAGEMENT_BASE                                SQL Management Base Schema                                             1,63
PL/SCOPE                                           PL/SQL Identifier Collection                                     ,75       
AUTO_TASK                                          Automated Maintenance Tasks                                      ,69       
EM_MONITORING_USER                                 Enterprise Manager Monitoring User                               ,19       
LOGSTDBY                                           Logical Standby                                                  ,13       
STREAMS                                            Oracle Streams                                                   ,06       
                                                                                                                    ----------
Total Size                                                                                                             9539,52

**dell68** · 13/12/2022, 10h31

Selon le résultat de ta requête, voici un article lié à la cause du problème de remplissage du tablespace sysaux qui pourra t'interésser:
http://dayananddba.blogspot.com/2018...dly-after.html

Good luck !

**Soticca** · 13/12/2022, 11h33

Merci dell68, je vais regarder cela.

**Mohamed.Houri** · 13/12/2022, 16h31

Très probablement que c'est le statistic advisor qui occupe les 9GB

Code :

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

1
2
3
4
5
6
7
8
9
10
col task_name for a50
compute sum label 'Total Size' of MB on report
break on report
select 
     task_name, count(1) cnt
from  
    dba_advisor_objects
group by
   task_name
order by 2 desc;

Essayez de voir avec la requête ci-dessus. Et si c'est le cas je vous conseille de le "disabler" puisqu'il ne sert à rien. Ou pour être plus juste je n'ai pas le souvenir d'avoir vu quelqu'un dire qu'il en a tiré quelque chose d'intéressant

Bien à vous
Mohamed Houri

**Soticca** · 14/12/2022, 09h12

Bonjour,

Voici le résultat de la requête (je n'ai mis que les premières lignes). On voit bien que c'est AUTO_STATS_ADVISOR_TASK qui prends de la place. Par contre, 2 questions se posent à moi :

- Comment on désactive cela ?
- Ensuite, comment on réduite la taille de SYSAUX01.dbf ? (sauf si cela se modifie automatiquement après désactivation de l'AUTO_STATS)

Code :

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

1
2
3
4
5
6
7
8
TASK_NAME                                                 CNT
-------------------------------------------------- ----------
AUTO_STATS_ADVISOR_TASK                              25128589
SYS_AUTO_SPCADV103000603092022                              4
SYS_AUTO_SPCADV103001403092022                              4
SYS_AUTO_SPCADV105000627092022                              4
SYS_AUTO_SPCADV104000629092022                              4
SYS_AUTO_SPCADV107001401102022                              4

Encore merci

**Mohamed.Houri** · 14/12/2022, 18h10

Si vous êtes en version 19 et avez appliqué le One-off path 26749785 vous pouvez "disabler" cet advisor tout simplement comme suit

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
BEGIN dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE'); END;
 
*
ERROR at line 1:
ORA-20001: Invalid input values for pname
ORA-06512: at "SYS.DBMS_STATS", line 52489
ORA-06512: at "SYS.DBMS_STATS", line 52737
ORA-06512: at line 1

Ce qui n'est pas le cas dans ma base de test

Sinon vous devez opter pour le contournement suivant

Code :

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

1
2
3
4
5
6
 
 begin 
  dbms_advisor.set_task_parameter('AUTO_STATS_ADVISOR_TASK','_AUTO_MMON_INTERVAL',2147483647);
  dbms_advisor.set_task_parameter('AUTO_STATS_ADVISOR_TASK','_AUTO_STATS_INTERVAL',2147483647);
end;
/

Oui Oracle va purger les historiques selon la configuration du parametre EXECUTION_DAYS_TO_EXPIRE

Bien à vous
Mohamed Houri

**Soticca** · 15/12/2022, 16h19

Bonjour,

Je n'ai pas eu de soucis pour exécuter

Code :

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

exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

Par contre, je ne vois pas la taille de SYSAUX01.DBF diminuer

Il n'est pas possible de dire au tablespace SYSAUX d'aller pointer sur un nouveau fichier ?

Comme notre version est 21C XE, on est limité à 12 Go.

21C - XE - SYSAUX01.DBF trop important

Administration Oracle

Discussions similaires

Partager

Partager