Manage SYSAUX tablespace in Oracle 11gR2

The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.

If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

Monitoring Occupants :

V$SYSAUX_OCCUPANTS view can be used to monitor the occupants of the SYSAUX tablespace. This view lists the following information about the occupants of the SYSAUX tablespace:

•Name of the occupant
•Occupant description
•Schema name
•Move procedure
•Current space usage

View information is maintained by the occupants.

select occupant_desc, space_usage_kbytes
  from v$sysaux_occupants
  where space_usage_kbytes > 0 order by space_usage_kbytes desc;

output:

OCCUPANT_DESC                                            SPACE_USAGE_KBYTES
---------------------------------------------            ------------------
Server Manageability - Automatic Workload Repository                 734272
Server Manageability - Optimizer Statistics History                  123520
XDB                                                                   90368
Unified Job Scheduler                                                 67776
Analytical Workspace Object Table                                     39168
OLAP API History Tables                                               39168
Server Manageability - Advisor Framework                              28224
LogMiner                                                              12544
Enterprise Manager Repository                                         12480
Oracle Multimedia ORDDATA Components                                   8960
Server Manageability - Other Components                                7104
OLAP Catalog                                                           5248
Oracle Text                                                            3712
Expression Filter System                                               3712
Transaction Layer - SCN to TIME mapping                                3328
Workspace Manager                                                      2496
SQL Management Base Schema                                             2176
PL/SQL Identifier Collection                                           1600
Logical Standby                                                        1408
Oracle Streams                                                         1024
Enterprise Manager Monitoring User                                      896
Automated Maintenance Tasks                                             320

To see the retention:

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31
SQL>

To Purge:
SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-31);
SQL> exec dbms_stats.alter_stats_history_retention(31);

Best Practice: As per the retention, you can keep those data.

To Reset the retention:

SQL> exec dbms_stats.alter_stats_history_retention(14);

Controlling the Size :

The SYSAUX tablespace is occupied by a number of database components (see above output), and its total size is governed by the space consumed by those components.

The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR). The space consumed by the AWR is determined by several factors, including the number of active sessions in the system at any given time, the snapshot interval, and the historical data retention period. A typical system with an average of 10 concurrent active sessions may require approximately 200 to 300 MB of space for its AWR data.

The following table provides guidelines on sizing the SYSAUX tablespace based on the system configuration and expected load.

Another major occupant of the SYSAUX tablespace is the embedded Enterprise Manager (EM) repository. This repository is used by Oracle Enterprise Manager Database Control to store its metadata. The size of this repository depends on database activity and on configuration-related information stored in the repository.

Other database components in the SYSAUX tablespace will grow in size only if their associated features (for example, Oracle UltraSearch, Oracle Text, Oracle Streams) are in use. If the features are not used, then these components do not have any significant effect on the size of the SYSAUX tablespace.

Partial purge of AWR data:

For long term capacity planning, it is always a good idea to run an AWR report that shows the relative amount of data consumed by each AWR table.  If you purge data that is no longer required, then you are going to have a compact set of historical data.  Let's start by getting the Oracle table size information:

SQL> col c1 heading 'table|name' format a30
SQL> col c2 heading 'table size|meg'format 999,999,999
SQL> select segment_name c1, sum(bytes) / (1024 * 1024) c2
  from dba_extents
 where segment_type = 'TABLE'
   and segment_name like 'WR%'
 group by segment_name
 order by c2 desc;
Sample output:

C1                                                                           C2
-------------------------------------------------------------------- ----------
WRM$_SNAPSHOT_DETAILS                                                         80
WRI$_OPTSTAT_HISTGRM_HISTORY                                                  28
WRH$_SQL_PLAN                                                                 20
WRH$_SYSMETRIC_HISTORY                                                        19
WRI$_OPTSTAT_HISTHEAD_HISTORY                                                 10

Purge:

-- For no prod environments:

Now we can simply issue a truncate command to purge all SQL data:

truncate table dba_hist_sql_plan;
truncate table dba_hist_sqltext;    etc.

Note: Not do in your production. If any hardware change / major deployment is there, then do not do at all.

-- For prod environment
SQL> delete from dba_hist_sql_plan
where sql_id =  (select sql_id from dba_hist_snapshot
       where begin_interval_time < sysdate - 90);

Here is another example of purging highly populated SQL tables in order to free-up enough space for longer AWR retention periods.

SQL> delete from dba_hist_sqltext
where sql_id =
    (select  sql_id   from dba_hist_snapshot
      where begin_interval_time < sysdate - 90);

I found the best option from Oracle community:

Sub: SYSAUX Tablespace Growing out of Control

First, I create backup tables using the below queries. (This will preserve 14 days worth of data)

create table SYS.WRI$_OPTSTAT_BAK as (select * from sys.wri$_optstat_histhead_history
where savtime > SYSDATE - 14);

create table SYS.WRI$_OPTSTAT_TAB_BAK as (select * from sys.wri$_optstat_tab_history
where savtime > SYSDATE - 14);

create table SYS.WRI$_OPTSTAT_IND_BAK as (select * from sys.wri$_optstat_ind_history
where savtime > SYSDATE - 14);

Then I truncate the original tables.

truncate table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table SYS.WRI$_OPTSTAT_TAB_HISTORY;
truncate table SYS.WRI$_OPTSTAT_IND_HISTORY;

Then I insert the 14 days worth of data back into the original tables.

insert into SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_BAK);
insert into SYS.WRI$_OPTSTAT_TAB_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_TAB_BAK);
insert into SYS.WRI$_OPTSTAT_IND_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_IND_BAK);

Drop the temporary backup tables.

drop table SYS.WRI$_OPTSTAT_BAK;
drop table SYS.WRI$_OPTSTAT_TAB_BAK;
drop table SYS.WRI$_OPTSTAT_IND_BAK;

Drop the related indexes on those tables:

SQL> drop index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST;

SQL> drop index I_WRI$_OPTSTAT_HH_ST;

Recreate the indexes:

CREATE UNIQUE INDEX "SYS"."I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST" ON "SYS"."WRI$_OPTSTAT_HISTHEAD_HISTORY" ("OBJ#", "INTCOL#", SYS_EXTRACT_UTC("SAVTIME"), "COLNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX";

CREATE INDEX "SYS"."I_WRI$_OPTSTAT_HH_ST" ON "SYS"."WRI$_OPTSTAT_HISTHEAD_HISTORY" (SYS_EXTRACT_UTC("SAVTIME"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX"; 

Make sure indexes are in usable state:

SQL> select index_name from dba_indexes where status='UNUSABLE';

Then I am able to run the below statement in a matter of minutes.

SQL> exec dbms_stats.purge_stats(SYSDATE-14);

Run Gather Schema Statistics:

I would like to note that I have had an SR open with Oracle for 8 months and they have identified this as a bug, but have not been able to provide me with a solution. My tablespace can't afford to wait any longer, so this is why I have decided to use a workaround. I have implemented this workaround in many test environments and have not had any issues. I am just a bit gun shy to pull the trigger in Production.

scheduler$_event_log tips:

The sys.scheduler$_event_log is a table that resides in the SYSAUX tablespace. The purpose of the scheduler$_event_log table is to store details about past scheduler events.

The scheduler$_event_log consumes an inordinate amount of space in the SYSAUX tablespace and it should be periodically truncated to keep the SYSAUX tablespace  from becoming full.  Either of these statements will purge the rows on demand:

SQL> exec DBMS_SCHEDULER.PURGE_LOG();
SQL> truncate table sys.scheduler$_event_log;

In addition to the conceptual job table, the scheduler uses several other tables to store metadata about scheduler objects.

SQL>
select table_name
from user_tables
where table_name like '%SCHEDULER$%'
and table_name not like '%SCHEDULER$_JOB%';

output;

TABLE_NAME
------------------------------
SCHEDULER$_EVENT_LOG
SCHEDULER$_STEP_STATE
SCHEDULER$_WINDOW_DETAILS
AQ$_SCHEDULER$_EVENT_QTAB_L
AQ$_SCHEDULER$_EVENT_QTAB_S
AQ$_SCHEDULER$_REMDB_JOBQTAB_L
AQ$_SCHEDULER$_REMDB_JOBQTAB_S
SCHEDULER$_CHAIN
SCHEDULER$_CLASS
SCHEDULER$_CREDENTIAL
SCHEDULER$_DESTINATIONS
SCHEDULER$_EVENT_QTAB
SCHEDULER$_EVTQ_SUB
SCHEDULER$_FILEWATCHER_HISTORY
SCHEDULER$_FILEWATCHER_RESEND
SCHEDULER$_FILE_WATCHER
SCHEDULER$_GLOBAL_ATTRIBUTE
...
...
AQ$_SCHEDULER$_REMDB_JOBQTAB_H
AQ$_SCHEDULER$_REMDB_JOBQTAB_I
AQ$_SCHEDULER$_REMDB_JOBQTAB_T

42 rows selected
SQL>

Under normal circumstances, one would not expect to interact with any of the scheduler tables directly. Information about the scheduler is displayed using the dba_scheduler_% views, and the dbms_scheduler package is used for the creation and manipulation of several scheduler objects including:


This script will display details from scheduler$_event_log and scheduler$_job_run_details.

Collected from : Burleson Consulting

SELECT j.LOG_ID,
       j.LOG_DATE,
       e.OWNER,
       DECODE(instr(e.NAME, '"'),
              0,
              e.NAME,
              substr(e.NAME, 1, instr(e.NAME, '"') - 1)) "JOB_NAME",
       DECODE(instr(e.NAME, '"'),
              0,
              NULL,
              substr(e.NAME, instr(e.NAME, '"') + 1)) "CHILD_JOB",
       e.STATUS,
       j.ERROR#,
       j.REQ_START_DATE,
       j.START_DATE,
       j.RUN_DURATION,
       j.INSTANCE_ID,
       j.SESSION_ID,
       j.SLAVE_PID,
       j.CPU_USED,
       decode(e.credential,
              NULL,
              NULL,
              substr(e.credential, 1, instr(e.credential, '"') - 1)) "Credential1",
       decode(e.credential,
              NULL,
              NULL,
              substr(e.credential,
                     instr(e.credential, '"') + 1,
                     length(e.credential) - instr(e.credential, '"'))) "Credential2",
       decode(bitand(e.flags, 1),
              0,
              NULL,
              substr(e.destination, 1, instr(e.destination, '"') - 1)) "Flag1",
       decode(bitand(e.flags, 1),
              0,
              e.destination,
              substr(e.destination,
                     instr(e.destination, '"') + 1,
                     length(e.destination) - instr(e.destination, '"'))) "Flag2",
       j.ADDITIONAL_INFO
  FROM scheduler$_job_run_details j, scheduler$_event_log e
 WHERE j.log_id = e.log_id
   AND e.type# = 66
   and e.dbid is null
   AND (e.owner = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') or /* user has object privileges */
       (select jo.obj#
           from obj$ jo, user$ ju
          where DECODE(instr(e.NAME, '"'),
                       0,
                       e.NAME,
                       substr(e.NAME, 1, instr(e.NAME, '"') - 1)) = jo.name
            and e.owner = ju.name
            and jo.owner# = ju.user#
            and jo.subname is null
            and jo.type# = 66) in
       (select oa.obj#
           from sys.objauth$ oa
          where grantee# in (select kzsrorol from x$kzsro)) or /* user has system privileges */
       (exists (select null
                   from v$enabledprivs
                  where priv_number = -265 /* CREATE ANY JOB */
                 ) and e.owner != 'SYS'));

I hope sure It will help. Cheers !!!