SYSAUX tablespace is considered as an auxiliary tablespace to the SYSTEM tablespace. This is required by Oracle as a default tablespace for many database features and products. SYSAUX tablespace reduces load on SYSTEM tablespace
1. Using SYSAUX DATAFILE clause in the CREATE DATABASE statement you can specify only datafile attributes in SYSAUX tablespace.
2. You can not alter attributes like (PERMANENT, READ WRITE,EXTENT MANAGMENT LOCAL,SEGMENT SPACE MANAGMENT AUTO) with an ALTER TABLESPACE statement
3. SYSAUX tablespace cannot be dropped or renamed.
2. You can not alter attributes like (PERMANENT, READ WRITE,EXTENT MANAGMENT LOCAL,SEGMENT SPACE MANAGMENT AUTO) with an ALTER TABLESPACE statement
3. SYSAUX tablespace cannot be dropped or renamed.
select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes;
OCCUPANT_NAME ---------------------------------------------------------------- OCCUPANT_DESC SPACE_USAGE_KBYTES ---------------------------------------------------------------- ------------------ AUDIT_TABLES DB audit tables 0 WM Workspace Manager 0 TSM Oracle Transparent Session Migration User 0 ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User 0 ULTRASEARCH Oracle Ultra Search 0 STATSPACK Statspack Repository 0 EM Enterprise Manager Repository 0 ORDIM/SI_INFORMTN_SCHEMA Oracle Multimedia SI_INFORMTN_SCHEMA Components 0 ORDIM/ORDPLUGINS Oracle Multimedia ORDPLUGINS Components 0 AUTO_TASK Automated Maintenance Tasks 320 STREAMS Oracle Streams 1024 LOGSTDBY Logical Standby 1408 PL/SCOPE PL/SQL Identifier Collection 1472 SQL_MANAGEMENT_BASE SQL Management Base Schema 1728 EXPRESSION_FILTER Expression Filter System 1920 EM_MONITORING_USER Enterprise Manager Monitoring User 3264 SMON_SCN_TIME Transaction Layer - SCN to TIME mapping 3328 SM/OTHER Server Manageability - Other Components 8064 TEXT Oracle Text 11968 LOGMNR LogMiner 13696 ORDIM Oracle Multimedia ORDSYS Components 14144 ORDIM/ORDDATA Oracle Multimedia ORDDATA Components 15616 XSAMD OLAP Catalog 15936 JOB_SCHEDULER Unified Job Scheduler 21568 AO Analytical Workspace Object Table 42880 XSOQHIST OLAP API History Tables 42880 XDB XDB 60288 SDO Oracle Spatial 77504 SM/ADVISOR Server Manageability - Advisor Framework 211520 SM/OPTSTAT Server Manageability - Optimizer Statistics History 436928 SM/AWR Server Manageability - Automatic Workload Repository 33601728 31 rows selected.
check awr retention:There may also be tables in theSYSAUXtablespace that can be purged. The most likely culprit is usually old AWR snapshots. You can query theDBA_EXTENTSdata dictionary view (group by SEGMENT_NAME, filter onTABLESPACE_NAME='SYSAUX') to get a better idea of why the tablespace is growing.
select retention from dba_hist_wr_control; -- TO CHECKexecute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080); --to change retention of awr old snapshots
exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7); -->change window timing SELECT moving_window_size FROM dba_hist_baseline WHERE baseline_type = 'MOVING_WINDOW'SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT) UNION SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT WHERE snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT) /SNAP_ID BEGIN_INTERVAL_TIME ---------- --------------------------------------------------------------------------- END_INTERVAL_TIME --------------------------------------------------------------------------- 131264 17-DEC-18 07.00.07.591 AM 17-DEC-18 07.15.10.881 AM 134079 15-JAN-19 01.45.38.825 PM 15-JAN-19 01.45.41.488 PMclean up awr report old snapshotsBEGIN dbms_workload_repository.drop_snapshot_range(low_snap_id => 9723, high_snap_id=>9920); END; /check the oracle id AWR Data Uses Significant Space in the SYSAUX Tablespace (Doc ID 287679.1)
