Experiences of a DBA
If you need to send an email from the command line or a script that contains both a mail body and an attachment you can use the below command:
mailx -s "Subject of this email " -r "sent-from@somewhere.com" -a file_name.txt sent-to@somewhere.com << EOF This will be the first line of the body of the email. 2nd line. 3rd line. EOF
The SYSAUX tablespace on one of our production RACs had grown to over 28 GB and was continuing to alarm for space. I ran the below report:
@?/rdbms/admin/awrinfo.sql
which indicated that the space was being used by “SM/AWR”:
######################################################## (I) AWR Snapshots Information ######################################################## ***************************************************** (1a) SYSAUX usage - Schema breakdown (dba_segments) ***************************************************** | | Total SYSAUX size 29,967.0 MB ( AUTOEXTEND ON for 1 out of 2 files ) | Fixed limit 499.0 MB | Auto Extent limit 32,768.0 MB | | Schema SYS occupies 29,684.9 MB ( 99.1% ) | Schema SYSTEM occupies 94.2 MB ( 0.3% ) | ******************************************************** (1b) SYSAUX occupants space usage (v$sysaux_occupants) ******************************************************** | | Occupant Name Schema Name Space Usage | -------------------- -------------------- ---------------- | SM/AWR SYS 28,805.8 MB | SM/OPTSTAT SYS 522.6 MB | SM/ADVISOR SYS 234.1 MB | LOGMNR SYSTEM 92.8 MB
Since we were keeping the AWR data for 30 days, we tried purging a few days at a time to see if that would make a difference with the following SQL:
exec DBMS_STATS.PURGE_STATS(SYSDATE-29);
But that did not seem to reduce the space used either. Purging all the data did not seem to make any difference:
exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL)
I suspect that we were encountering the issue documented in “Excessive AWR Growth From Partitioned Objects Such as SYS.WRH$_EVENT_HISTOGRAM Causing Sysaux to Grow (Doc ID 1912201.1)”.
I then decided to look at the sizes of the individual objects with the SQL:
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments where tablespace_name = 'SYSAUX' group by segment_name,segment_type order by 1 desc ;
This indicated that objects such as WRH$_EVENT_HISTOGRAM, WRH$_LATCH etc. were using up a lot of space.
Before you proceed with the following, please understand that you will be losing AWR data that could be needed for performance tunning etc.
Based on the output from the above SQL, I started truncating the largest objects (tables) with SQL such as:
truncate table WRH$_EVENT_HISTOGRAM; truncate table WRH$_LATCH; truncate table WRH$_SQLSTAT; truncate table WRH$_SYSSTAT; truncate table WRH$_ACTIVE_SESSION_HISTORY;
After this completed the space utilized was significantly reduced:
(1a) SYSAUX usage - Schema breakdown (dba_segments) ***************************************************** | | Total SYSAUX size 12,036.6 MB ( AUTOEXTEND ON for 1 out of 2 files ) | Fixed limit 499.0 MB | Auto Extent limit 32,768.0 MB | | Schema SYS occupies 11,754.4 MB ( 97.7% ) | Schema SYSTEM occupies 94.2 MB ( 0.8% ) | ******************************************************** (1b) SYSAUX occupants space usage (v$sysaux_occupants) ******************************************************** | | Occupant Name Schema Name Space Usage | -------------------- -------------------- ---------------- | SM/AWR SYS 11,394.5 MB | SM/ADVISOR SYS 234.1 MB | LOGMNR SYSTEM 92.8 MB