Oracle Database: Mantenimiento espacio para el tablespace AUX

 

Aún cuando consideremos triviales y sin importancia, tenemos que continuar brindando mantenimiento a algunas estructuras de la base de datos.

En ambiente de base de datos como servicio en el OCI, he observado que el tablespace SYSAUX aumenta considerablemente de tamaño, provocado en gran medida por las tablas de seguimiento de colas de trabajos (Jobs).

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 

ranking_tablespace.sql

col segment_name format a60

col owner format a20

set pagesize 20

col MBytes format 999,999,999.00

Ttitle ' Ranking Tablas grandes en la base de datos'

Btitle "************************************************************************"

set linesize 120

select * from

( select owner, segment_name, bytes/1024/1024 MBytes,row_number() over (order by bytes desc nulls last) ranking

from dba_segments where segment_type='TABLE' and tablespace_name='&TABLESPACE')

 where ranking <= 10

espacio.sql

column dummy noprint

set linesize 200

column pct_used format 999.9 heading "%|Used"

column name format a40 heading "Tablespace Name"

column Kbytes format 999,999,999 heading "KBytes"

column used format 999,999,999 heading "Used"

column free format 999,999,999 heading "Free"

column largest format 999,999,999 heading "Largest"

break on report

compute sum of kbytes on report

compute sum of free on report

compute sum of used on report

select nvl(b.tablespace_name,

nvl(a.tablespace_name,'UNKOWN')) name,

kbytes_alloc kbytes,

kbytes_alloc-nvl(kbytes_free,0) used,

nvl(kbytes_free,0) free,

((kbytes_alloc-nvl(kbytes_free,0))/

kbytes_alloc)*100 pct_used,

nvl(largest,0) largest

from ( select sum(bytes)/1024 Kbytes_free,

max(bytes)/1024 largest,

tablespace_name

from sys.dba_free_space

group by tablespace_name ) a,

( select sum(bytes)/1024 Kbytes_alloc,

tablespace_name

from sys.dba_data_files

group by tablespace_name )b

where a.tablespace_name (+) = b.tablespace_name

order by free desc,a.tablespace_name

/

/