Method 1
Why does SYSAUX tablespace grows much larger than expected?
There could be number of potential reasons:
1. ASH data has grown too large (SM/AWR)
2. High Retention Period
3. Segment Advisor has grown too large
4. Increase in older version of Optimizer Statistics (SM/OPTSTAT)
5. Bugs Bugs Bugs!!!!!
How do we identify the SYSAUX space Usage?
There are basically 2 ways to identify that i know of
1. Running @?/rdbms/admin/awrinfo.sql –> Detailed Info like Schema breakdown, SYSAUX occupants space usage etc.
2.
COLUMN "Item" FORMAT A25 COLUMN "Space Used (GB)" FORMAT 999.99 COLUMN "Schema" FORMAT A25 COLUMN "Move Procedure" FORMAT A40 SELECT occupant_name "Item", space_usage_kbytes/1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants ORDER BY 2 / col owner for a6 col segment_name for a50 select * from (select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024)
size_m from dba_segments where tablespace_name = 'SYSAUX' ORDER BY BLOCKS desc) where rownum < 11;
n my case, below 2 were occupying most of the space :-
1. SM/AWR
2. SM/OPTSTAT
SM/AWR — It refers to Automatic Workload Repository.Data in this section is retained for a certain amount of time (default 8 days). Setting can be checked through DBA_HIST_WR_CONTROL.
SM/OPSTAT — Stores older data of optimizer statistics.Setting can be checked through dbms_stats.get_stats_history_retention. This is not a part of AWR and is not controlled by AWR retention.
When looking at the top segments, i saw WRH$_ACTIVE_SESSION_HISTORY occupying most of the space. Sometimes AWR tables are not purged to settings in sys.wrm$_wr_control.
As per Oracle :-
Oracle decides what rows need to be purged based on the retention policy. There is a special mechanism which is used in the case of the large AWR tables where we store the snapshot data in partitions. One method of purging data from these tables is by removing partitions that only contain rows that have exceeded the retention criteria. During the nightly purge task, we only drop the partition if all the data in the partition has expired. If the partition contains at least one row which, according to the retention policy shouldn’t be removed, then the partition won’t be dropped and as such the table will contain old data.
If partition splits do not occur (for whatever reason), then we can end up with a situation where we have to wait for the latest entries to expire before the partition that they sit in can be removed. This can mean that some of the older entries can be retained significantly past their expiry date. The result of this is that the data is not purged as expected.
Diagnose and Reduce Used Space of SYSAUX.
Once the major occupants and top segments is identified as discussed above, we can start with the steps to rectify it. Expecting SM/AWR occupying most of the space , i think we can follow 3 methods. In this blog i will be posting one of the method only
To check Orphaned ASH rows :-
SELECT COUNT(1) Orphaned_ASH_Rows FROM wrh$_active_session_history a WHERE NOT EXISTS (SELECT 1 FROM wrm$_snapshot WHERE snap_id = a.snap_id AND dbid = a.dbid AND instance_number = a.instance_number );
Check minimum snap_id in ASH table and then compare to the minimum snap_id
in dba_hist_snapshot.
select min(snap_id) from WRH$_ACTIVE_SESSION_HISTORY; select min(snap_id) from dba_hist_snapshot;
Example :-
select min(snap_id),MAX(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
17754 18523
select min(snap_id),MAX(snap_id) from WRH$_ACTIVE_SESSION_HISTORY;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
1 18523
Above as per the retention period, we should have data from snap_id 17754 till 18523, but the WRH$_ASH table has data from snap_id 1.
From Oracle MOS Doc :-
A potential solution to this issue is to manually split the partitions of the partitioned AWR objects such that there is more chance of the split partition being purged.You will still have to wait for all the rows in the new partitions to reach their retention time but with split partitions there is more chance of this happening. you can manually split the partitions using the following undocumented command:
alter session set “_swrf_test_action” = 72;
select table_name, count(*) from dba_tab_partitions where table_name like 'WRH$%' and table_owner = 'SYS' group by table_name order by 1; TABLE_NAME COUNT(*) -------------------------------------------------- ---------- WRH$_ACTIVE_SESSION_HISTORY 2 WRH$_DB_CACHE_ADVICE 2 WRH$_DLM_MISC 2 WRH$_EVENT_HISTOGRAM 2 WRH$_FILESTATXS 11 WRH$_INST_CACHE_TRANSFER 2 WRH$_INTERCONNECT_PINGS 2 ........................ 25 rows selected.
SQL> alter session set "_swrf_test_action"=72; Session altered.
SQL> select table_name,partition_name from dba_tab_partitions where table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------------------------------- WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1798927129_0 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1798927129_18531 --> New Partition created WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN
col table_name for a80 select table_name, count(*) from dba_tab_partitions where table_name like 'WRH$%' and table_owner = 'SYS' group by table_name order by 1 TABLE_NAME COUNT(*) ------------------------------------------- ---------- WRH$_ACTIVE_SESSION_HISTORY 3 WRH$_DB_CACHE_ADVICE 3 WRH$_DLM_MISC 3 WRH$_EVENT_HISTOGRAM 3 ...................... 25 rows selected.
In the above example, WRH$_ACTIVE_1798927129_18531 is the new partition created where 1798927129 being the DBID and 18531 is the max(snap_id) when it was partitioned. So, now we can start dropping the snapshots range,which in my case is from 1 to 17753 as 17754 is the min(snap_id) in dba_hist_snapshot.
SQL> EXEC dbms_workload_repository.drop_snapshot_range(1,17753,1798927129);
It can generate good amount of redo and use undo. So keep monitoring undo tablespace and make sure you have sufficient space.
So, what happens when run the above :-
SQL> @sqlid ft7m07stk3dws
old 9: sql_id = ('&1')
new 9: sql_id = ('ft7m07stk3dws')
SQL_ID HASH_VALUE SQL_TEXT
--------------------------------------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
ft7m07stk3dws 857847704 delete from WRH$_SYSTEM_EVENT tab where (:beg_snap <= tab.snap_id and tab.snap_id = b.start_snap_id) and
(tab.snap_id @sqlid 854knbb15976z
old 9: sql_id = ('&1')
new 9: sql_id = ('854knbb15976z')
SQL_ID HASH_VALUE SQL_TEXT
--------------------------------------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
854knbb15976z 3260325087 delete from WRH$_SQLSTAT tab where (:beg_snap <= tab.snap_id and tab.snap_id = b.start_snap_id) and
(tab.snap_id <= b.end_snap_id))
So, internally oracle runs delete command which cause high redo and undo generation
Once the procedure is completed successfully, check the min(snap_id) in WRH$_ACTIVE_SESSION_HISTORY and perform shrink space cascade.
Select owner,segment_name,round(sum(bytes/1024/1024),2)MB, tablespace_name from dba_segments where segment_name = upper('WRH$_ACTIVE_SESSION_HISTORY') group by owner,segment_name,tablespace_name
OWNER SEGMENT_NAME MB TABLESPACE_NAME
------- ---------------------------------- ----------- -------------------
SYS WRH$_ACTIVE_SESSION_HISTORY 3538.06 SYSAUX
SQL> alter table WRH$_ACTIVE_SESSION_HISTORY shrink space cascade;
Table altered.
OWNER SEGMENT_NAME MB TABLESPACE_NAME
------- ---------------------------------- ----------- -------------------
SYS WRH$_ACTIVE_SESSION_HISTORY 46.75 SYSAUX
In similar fashion, other WRH$ tables can be shrink ed to free up space in SYSAUX.
Reference :-
WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)
Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)
Method 2
Manage SYSAUX tablespace in Oracle 11gR2
Manage SYSAUX tablespace
-- Purge SysAux tablespace in oracle 11g R2
-- SYSAUX tablespace growth tips
-- scheduler$_event_log tips
Introduction:
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 320To 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 ci=ommunity:
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.
Use this link : https://community.oracle.com/thread/2394310?tstart=0
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 !!!
-- Purge SysAux tablespace in oracle 11g R2
-- SYSAUX tablespace growth tips
-- scheduler$_event_log tips
Introduction:
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 320To 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 ci=ommunity:
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.
Use this link : https://community.oracle.com/thread/2394310?tstart=0
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 !!!
