A tablespace SYSAUX foi criada para auxiliar a tablespace SYSTEM, sendo criada automaticamente quando criamos um banco de dados. Alguns componentes de banco de dados que anteriormente eram criados e utilizavam a tablespace SYSTEM, agora ficam armazenados na tablespace SYSAUX. Essa mudança feita pela Oracle permitiu que se o espaço da tablespace SYSAUX ficar indisponível, ou atingir seu limite de utilização, o banco de dados permanecerá operacional, diferentemente do que ocorria quando tínhamos apenas a tablespace SYSTEM. Hoje, o que pode ocorrer caso a tablspace SYSAUX atinga seu limite de ocupação, é a falha ou o funcionamento limitado de alguns recursos que estejam armazenados na tablespace SYSAUX, mas não causando indisponibilidade do banco de dados. A tablespace SYSAUX é responsável também por armazenar informações do banco de dados, como por exemplo, os snapshots realizados para a coleta de informações dos relatórios do AWR. Uma retenção muito grande dos dados para esse tipo de relatório e uma grande quantidade de snapshots agendados por dia farão com que a tablespace SYSAUX tenha um tamanho muito grande. Podemos fazer a verificação da utilização da tablespace SYSAUX através dos comandos abaixo: 1) Verificar a utilização da SYSAUX por componenteselect occupant_desc, space_usage_kbytes/1024/1024 as usage_GB from v$sysaux_occupants where space_usage_kbytes > 0 order by space_usage_kbytes desc;OCCUPANT_DESC USAGE_GB -------------------------------------------------------------------------- ----------------- Server Manageability - Automatic Workload Repository 148223 Server Manageability - Other Components 3491,5625 Server Manageability - Advisor Framework 1674,0625 OLAP API History Tables 41,8750 Server Manageability - Optimizer Statistics History 335,1875 Enterprise Manager Repository 130,7500 Unified Job Scheduler 126,2500 Oracle Spatial 65,5625 XDB 57,5625 Analytical Workspace Object Table 41,8750 OLAP Catalog 15,6250 Oracle Text 6,3750 Transaction Layer - SCN to TIME mapping 15,3750 Oracle Multimedia ORDDATA Components 15,3125 LogMiner 10,2500 Enterprise Manager Monitoring User 9,6875 PL/SQL Identifier Collection 9,6250 Workspace Manager 7,3750 SQL Management Base Schema 7,0000 Expression Filter System 3,875 Logical Standby 1,375 Oracle Streams 1,0000 Oracle Multimedia ORDSYS Components 0,5625 Automated Maintenance Tasks 0,3125 Com a informação obtida acima, podemos perceber que o maior consumidor da SYSAUX são os snapshots do AWR, com 148GB de ocupação. 2) Verificar a utilização da SYSAUX por Item Outra forma de obtermos as informações da SYSAUX é: set linesize 120 set pagesize 100 COLUMN "Item" FORMAT A25 COLUMN "Space Used (GB)" FORMAT 999.99 COLUMN "Move Procedure" FORMAT A40 SELECT occupant_name "Item", EM_MONITORING_USER .01 DBSNMP space_usage_kbytes/1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants ORDER BY 1 / Item Space Used (GB) Schema Move Procedure ----------------------------------------------- -------------------------- ----------------- ----------------------------------- AO .04 SYS DBMS_AW.MOVE_AWMETA AUDIT_TABLES .00 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables AUTO_TASK .00 SYS EM .13 SYSMAN emd_maintenance.move_em_tblspc PL/SCOPE .01 SYS EXPRESSION_FILTER .00 EXFSYS JOB_SCHEDULER .12 SYS LOGMNR .01 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE LOGSTDBY .00 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE ORDIM .00 ORDSYS ordsys.ord_admin.move_ordim_tblspc ORDIM/ORDDATA .01 ORDDATA ordsys.ord_admin.move_ordim_tblspc ORDIM/ORDPLUGINS .00 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc ORDIM/SI_INFORMTN_SCHEMA .00 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc ULTRASEARCH_DEMO_USER .00 WK_TEST MOVE_WK SDO .06 MDSYS MDSYS.MOVE_SDO SM/ADVISOR 1.63 SYS SM/AWR 144.75 SYS SM/OPTSTAT .33 SYS SM/OTHER 3.41 SYS SMON_SCN_TIME .02 SYS SQL_MANAGEMENT_BASE .01 SYS STATSPACK .00 PERFSTAT STREAMS .00 SYS TEXT .01 CTXSYS DRI_MOVE_CTXSYS TSM .00 TSMSYS ULTRASEARCH .00 WKSYS MOVE_WK WM .01 WMSYS DBMS_WM.move_proc XDB .06 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE XSAMD .02 OLAPSYS DBMS_AMD.Move_OLAP_Catalog XSOQHIST .04 SYS DBMS_XSOQ.OlapiMoveProc Para podermos diminuir a utilização da tablespace SYSAUX, precisaremos modificar o tempo de retenção das estatísticas do banco de dados. Abaixo, algumas opções que podemos utilizar para que consigamos fazer essa diminuição na tablespace SYSAUX. a) Obter o tempo de retenção das estatísticas no banco de dados SQL> select dbms_stats.get_stats_history_retention from dual; b) Alterar o tempo de retenção SQL> exec dbms_stats.alter_stats_history_retention(10); c) Fazer a limpeza das estatísticas armazenadas na SYSAUX SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-10); ** Aqui estamos limpando as estatísticas com mais de 10 dias. d) Verificar as estatísticas ativas que não podem ser removidas SQL> select dbms_stats.get_stats_history_availability from dual; Obs.: Podemos também alterar apenas o tempo de retenção e a quantidade de snapshots gerados por dia no banco de dados. Dessa forma a tablespace SYSAUX terá seu tamanho diminuído gradativamente, conforme o passar dos dias. Alteração de retenção do AWR
Referências: https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces010.htm https://community.oracle.com/thread/2543974?tstart=0 https://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/ http://remidian.com/2009/08/purging-sysaux-tablespace-purging-awr-reports/