Purge des stats sur les bases dans SYSAUX

Purge des stats sur les bases en 10g dans SYSAUX

select sum(bytes/1024/1024) Mb, segment_name,segment_type, tablespace_name

from dba_segments

where  tablespace_name = 'SYSAUX'

and segment_name like 'WRI$_OPTSTAT%'

and segment_type='TABLE'

group by segment_name,segment_type,tablespace_name order by 1 asc;

select sum(bytes/1024/1024) Mb, segment_name,segment_type,tablespace_name

from dba_segments

where  tablespace_name = 'SYSAUX'

and segment_name like '%OPT%'

and segment_type='INDEX'

group by segment_name,segment_type,tablespace_name

order by 1 asc;

Affichage de la retention des stats:

select dbms_stats.get_stats_history_retention from dual;

Passage de la retention des stats à 10 jours (par défaut 31):

exec dbms_stats.alter_stats_history_retention(10);

Date des stats les plus anciennes:

select dbms_stats.get_stats_history_availability from dual;

Requêtes SQL pour MOVE TABLE concernées:

select 'alter table '||segment_name||'  move tablespace SYSAUX;'

from dba_segments where tablespace_name = 'SYSAUX'

and segment_name like '%OPT%'

and segment_type='TABLE';

Requêtes SQL pour Rebuild Index concernés:

select 'alter index '||segment_name||'  rebuild online parallel (degree 4);'

from dba_segments

where tablespace_name = 'SYSAUX'

and segment_name like '%OPT%'

and segment_type='INDEX';

Vérif de l'état des index:

select  di.index_name,di.index_type,di.status  from  dba_indexes di where  di.tablespace_name = 'SYSAUX' and di.index_name like '%OPT%' order by 1 asc;


 SELECT t.OWNER "OWNER", t.TABLESPACE_NAME "TABLESPACE", SUM(ROUND(t.BLOCKS*p.value/1024/1024,2)) "SIZE (Mo)"
FROM DBA_SEGMENTS t, v$parameter p
WHERE t.TABLESPACE_NAME <> 'SYSTEM'
AND upper(p.name) = upper('db_block_size')
 GROUP BY t.OWNER, t.TABLESPACE_NAME

ORDER BY 3;