Veamos como podemos recuperar espacio del tablespace SYSAUX, purgando los históricos de los registros de estas tablas.
login as: opc
Authenticating with public key "rsa-key-20200507"
Passphrase for key "rsa-key-20200507":
Last login: Fri Dec 30 15:00:23 2022 from 10.100.128.176
[opc@serverdb_01_uat ~]$ sudo su - oracle
Last login: Fri Dec 30 15:00:26 CST 2022 on pts/2
Primero que todo vamos conectarnos a una base de datos acomplada de mi contenedor.
SQL> connect / as sysdba
Connected.
SQL> alter session set container=***************;
Utilizando un script casero, valido la ocupación de cada uno de mis tablespaces.
SQL> @espacio
Tablespace Name KBytes Used Free Used Largest
--------------------- ------------ ------------ ------------ ------ ------------
FCUBSLIVE 42,053,616 21,048,944 21,004,672 50.1 4,063,232
FCUBSPILOT 18,874,368 20,224 18,854,144 .1 4,063,232
UNDOTBS1 5,268,480 846,976 4,421,504 16.1 2,001,920
USERS 3,408,640 746,944 2,661,696 21.9 1,870,592
SYSAUX 11,315,200 10,772,416 542,784 95.2 515,072
TBS_AUDIT_FGA_DATA 409,600 5,184 404,416 1.3 101,376
OAS_IAS_UMS 102,400 8,320 94,080 8.1 94,080
OAS_MDS 102,400 9,216 93,184 9.0 93,184
TBS_DATA_ETL 614,400 547,840 66,560 89.2 66,560
OAS_IAU 61,440 1,152 60,288 1.9 60,288
OAS_WLS 61,440 1,280 60,160 2.1 60,160
OAS_BIPLATFORM 196,608 156,864 39,744 79.8 39,744
OAS_STB 10,240 1,920 8,320 18.8 8,320
OAS_IAS_OPSS 63,488 56,832 6,656 89.5 6,656
SYSTEM 8,284,160 8,278,400 5,760 99.9 3,072
------------ ------------ ------------
sum 90,826,480 42,502,512 48,323,968
15 rows selected.
Como observan, el tablespace de SYSAUX, tiene un poco más de 10GB de almacenamiento ocupado.
Voy a utilizar otro script casero, para ver cuál es el top de los objetos en dicho tablespaces.
SQL> host ls -la *rank*
-rw-r--r--. 1 oracle oinstall 445 Aug 11 15:51 ranking20_tabla.sql
-rw-r--r--. 1 oracle oinstall 447 Aug 11 15:51 ranking_index.sql
-rw-r--r--. 1 oracle oinstall 506 Aug 11 15:51 ranking_index_sys.sql
-rw-r--r--. 1 oracle oinstall 467 Aug 11 15:51 ranking_schema_index.sql
-rw-r--r--. 1 oracle oinstall 498 Aug 11 15:51 ranking_schema.sql
-rw-r--r--. 1 oracle oinstall 446 Aug 11 15:51 ranking_tabla.sql
-rw-r--r--. 1 oracle oinstall 480 Aug 11 15:51 ranking_tablespace.sql
-rw-r--r--. 1 oracle oinstall 295 Aug 11 15:51 tablas_ranking.sql
SQL> @ranking_tablespace.sql
Enter value for tablespace: SYSAUX
old 3: from dba_segments where segment_type='TABLE' and tablespace_name='&TABLESPACE')
new 3: from dba_segments where segment_type='TABLE' and tablespace_name='SYSAUX')
Ranking Tablas grandes en la base de datos
OWNER SEGMENT_NAME MBYTES RANKING
----------- ------------------------------- ----------- ----------
SYS SCHEDULER$_JOB_RUN_DETAILS 2,615.00 1
SYS SCHEDULER$_EVENT_LOG 1,408.00 2
SYS SCHEDULER$_JOB_OUTPUT 1,280.00 3
SYS PLSCOPE_ACTION$ 10.00 4
MDSYS SDO_CS_SRS 9.00 5
SYS WRI$_OPTSTAT_OPR_TASKS 5.00 6
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_P 4.00 7
SYS EXP_HEAD$ 3.00 8
SYS STATS_TARGET$ 3.00 9
SYS PLSCOPE_IDENTIFIER$ 2.00 10
Como observan en el reporte, tenemos 3 objetos bastantes grandes para ser unas simples tablas de registro de colas de procesos.
El siguiente es el procedimiento para limpiar los registros de dichas tablas.
SQL> SCHEDULER$_JOB_RUN_DETAILSexec dbms_scheduler.purge_log(0, 'JOB_AND_WINDOW_LOG');^C
SQL> exec dbms_scheduler.purge_log(0, 'JOB_AND_WINDOW_LOG');
PL/SQL procedure successfully completed.
Una vez ejecutado el proceso, no se sientan defraudados si vuelven a consultar las tablas y el espacio aún no ha sido liberado.
SQL> @ranking_tablespace.sql
Enter value for tablespace: SYSAUX
old 3: from dba_segments where segment_type='TABLE' and tablespace_name='&TABLESPACE')
new 3: from dba_segments where segment_type='TABLE' and tablespace_name='SYSAUX')
Ranking Tablas grandes en la base de datos
OWNER SEGMENT_NAME MBYTES RANKING
------------ ------------------------------- ----------- ----------
SYS SCHEDULER$_JOB_RUN_DETAILS 2,615.00 1
SYS SCHEDULER$_EVENT_LOG 1,408.00 2
SYS SCHEDULER$_JOB_OUTPUT 1,280.00 3
SYS PLSCOPE_ACTION$ 10.00 4
MDSYS SDO_CS_SRS 9.00 5
SYS WRI$_OPTSTAT_OPR_TASKS 5.00 6
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_P 4.00 7
SYS EXP_HEAD$ 3.00 8
SYS STATS_TARGET$ 3.00 9
SYS PLSCOPE_IDENTIFIER$ 2.00 10
10 rows selected.
Es necesario que ahora hagamos un "TRUNCATE" sobre las siguientes tablas para liberar el espacio.SQL> truncate table scheduler$_job_output;
Table truncated.
Volvemos a validar el top de uso de espacio en el tablespace y ahora si ya hemos logrado recuperar parte de la ocupación que tenían estos objetos.
SQL> @ranking_tablespace.sql
Enter value for tablespace: SYSAUX
old 3: from dba_segments where segment_type='TABLE' and tablespace_name='&TABLESPACE')
new 3: from dba_segments where segment_type='TABLE' and tablespace_name='SYSAUX')
Ranking Tablas grandes en la base de datos
OWNER SEGMENT_NAME MBYTES RANKING
-------- --------------------------------- --------------- ----------
SYS SCHEDULER$_JOB_RUN_DETAILS 2,615.00 1
SYS SCHEDULER$_EVENT_LOG 1,408.00 2
SYS PLSCOPE_ACTION$ 10.00 3
MDSYS SDO_CS_SRS 9.00 4
SYS WRI$_OPTSTAT_OPR_TASKS 5.00 5
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_P 4.00 6
SYS EXP_HEAD$ 3.00 7
SYS STATS_TARGET$ 3.00 8
SYS PLSCOPE_IDENTIFIER$ 2.00 9
SYS WRI$_OPTSTAT_IND_HISTORY 2.00 10
Ahora vamos con las siguientes tablas. Hay un objeto de la base de datos, que guarda estadisticas históricas. Si llegaste a esta publicación, quizás ya antes haz visto publicaciones en juniororacledba.wordpress.com y muchos otros más, en donde sus autores, tratan la misma bronca. El espacio de más utilizado en el tablespace SYSAUX y el incremento en el tamaño del Datapump de tu base de datos.
Es posible disminuir el tamaño de información retenida, aplicando el siguiente procedimiento. Primero puedes obtener, la fecha de referencia desde cuando tienens estadísticas acumuladas en la base de datos.
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
29-NOV-22 10.46.00.871479000 AM -06:00
Bien ahora, puedes con el siguiente procedimiento, borrar las estadísticas acumuladas de los días 11 al 30 de antiguedad, con el siguiente bloque anónimo.
SQL> begin
for i in reverse 10..30
loop
dbms_stats.purge_stats(sysdate-i);
end loop;
end;
/
PL/SQL procedure successfully completed.
Verificas nuevamente la fecha de historial y ya puedes darte cuenta que la fecha ha avanzado hasta los últimos 10 días.
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
20-DEC-22 05.25.19.000000000 PM -06:00
Seguimos revisando los objetos con espacio en el tablespace de SYSAUX y ahora toca las tablas de SCHEDULER.
SQL> @ranking_tablespace.sql
Enter value for tablespace: SYSAUX
old 3: from dba_segments where segment_type='TABLE' and tablespace_name='&TABLESPACE')
new 3: from dba_segments where segment_type='TABLE' and tablespace_name='SYSAUX')
Fri Dec 30 page 1
Ranking Tablas grandes en la base de datos
OWNER SEGMENT_NAME MBYTES RANKING
---------- ---------------------------------------- ----------
SYS SCHEDULER$_JOB_RUN_DETAILS 2,615.00 1
SYS SCHEDULER$_EVENT_LOG 1,408.00 2
SYS PLSCOPE_ACTION$ 10.00 3
MDSYS SDO_CS_SRS 9.00 4
SYS WRI$_OPTSTAT_OPR_TASKS 5.00 5
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_P 4.00 6
SYS EXP_HEAD$ 3.00 7
SYS STATS_TARGET$ 3.00 8
SYS PLSCOPE_IDENTIFIER$ 2.00 9
SYS WRI$_OPTSTAT_IND_HISTORY 2.00 10
************************************************************************
10 rows selected.
Para limpiar estas tablas, vamos a ejecutar el siguiente procedimiento y luego procedemos a TRUNCAR las tablas respectivas.
SQL> exec DBMS_SCHEDULER.PURGE_LOG();
PL/SQL procedure successfully completed.
SQL> truncate table sys.scheduler$_job_run_details;
Table truncated.
SQL> truncate table sys.SCHEDULER$_EVENT_LOG;
Table truncated.
Volvemos a validar y ahora sí, hemos recuperado cerca de 10GB de espacio con estos simples pasos.
SQL> @ranking_tablespace.sql
Enter value for tablespace: SYSAUX
old 3: from dba_segments where segment_type='TABLE' and tablespace_name='&TABLESPACE')
new 3: from dba_segments where segment_type='TABLE' and tablespace_name='SYSAUX')
Ranking Tablas grandes en la base de datos
OWNER SEGMENT_NAME MBYTES RANKING
---------- ----------------------------------- --------- ----------
SYS PLSCOPE_ACTION$ 10.00 1
MDSYS SDO_CS_SRS 9.00 2
SYS WRI$_OPTSTAT_OPR_TASKS 5.00 3
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_P 4.00 4
SYS EXP_HEAD$ 3.00 5
SYS STATS_TARGET$ 3.00 6
SYS PLSCOPE_IDENTIFIER$ 2.00 7
MDSYS EXT_TAB_REF_SYS_1 2.00 8
XDB XDB$ELEMENT 2.00 9
SYS WRI$_OPTSTAT_IND_HISTORY 2.00 10
10 rows selected.
SQL>
Adjuntos 
