My Oracle Support Banner

ZDLRA: SM/ADVISOR using more space in SYSAUX (Doc ID 2730556.1)

Last updated on JANUARY 08, 2021

Applies to:

Zero Data Loss Recovery Appliance Software - Version 19.2.1.1.1 to 19.2.1.1.1
Information in this document applies to any platform.

Symptoms

SQL> connect <UserName>/<Password> as sysdba
SQL> SELECT m.tablespace_name,ROUND((m.tablespace_size)*t.block_size/1024/1024, 3) total_used,     

         ROUND((m.used_space)*t.block_size/1024/1024, 3) mb_used, ROUND((m.tablespace_size - m.used_space)*t.block_size/1024/1024, 3) mb_free, ROUND(m.used_percent, 2)
       FROM  dba_tablespace_usage_metrics m, dba_tablespaces t, v$parameter p
       WHERE  p.name='statistics_level' and p.value!='BASIC'  and t.contents NOT IN ('TEMPORARY', 'UNDO') and t.tablespace_name = m.tablespace_name and t.tablespace_name ='SYSAUX' ;
SQL> connect <UserName>/<Password> as sysdba
SQL> SET TRIMSPOOL ON TAB OFF LINES 1000 TERMOUT ON VERIFY OFF PAGES 1000
SQL> COLUMN OCCUPANT_NAME FORMAT A30
SQL> SELECT occupant_name, space_usage_kbytes

     FROM  v$sysaux_occupants
     ORDER BY space_usage_kbytes DESC;
SQL> connect <UserName>/<Password> as sysdba
SQL> SET TRIMSPOOL ON TAB OFF LINES 1000 TERMOUT ON VERIFY OFF PAGES 1000
SQL> COLUMN TASK_NAME FORMAT A30
SQL> COLUMN PARAMETER_NAME FORMAT A30
SQL> COLUMN PARAMETER_VALUE FORMAT A30
SQL> select task_name,parameter_name, parameter_value

     FROM DBA_ADVISOR_PARAMETERS
     WHERE task_name='AUTO_STATS_ADVISOR_TASK' and parameter_name='EXECUTION_DAYS_TO_EXPIRE';
SQL> connect <UserName>/<Password> as sysdba
SQL> SET TRIMSPOOL ON TAB OFF LINES 1000 TERMOUT ON VERIFY OFF PAGES 1000
SQL> COLUMN TASK_NAME FORMAT A30
SQL> SELECT  task_name, count(*) CNT

     FROM DBA_ADVISOR_OBJECTS
     GROUP BY TASK_NAME ORDER BY CNT DESC; 

  Note: This query can take some minutes.

Changes

 Upgrading  ZDLRA to  version 19.2

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.