today I received my report alert regarding one of Oracle database tablespace SYSAUX is over 80% (I created automated job using SSIS package to check tablespace usage daily) following steps to resolve it: /* 2021.07.08 Documentation steps for troubleshooting Sysaux tablespace is huge --85% full */ ---1. see what uses the most select occupant_name,occupant_desc, space_usage_kbytes/1024 MB from v$sysaux_occupants where space_usage_kbytes > 0 order by space_usage_kbytes desc; ---find it is SM/Advisor Server Manageability - Advisor Framework 25GB --now look for top 10 big objects select * from ( select bytes/1024/1024 MB, blocks, s.SEGMENT_NAME, s.partition_name, s.segment_type, s.tablespace_name from dba_segments s where owner='SYS' order by bytes desc ) where rownum <=10 ; ---find it is WRI$_ADV_OBJECTS having 13GB and 1.7 million blocks --now check how many objects in dba_advisor_objects select task_name
Comments
Post a Comment