How to purge SYSAUX Tablespace (SQL_MANAGEMENT_BASE Occupant)
Date: Fri, 30 Aug 2019 11:51:01 +0000
Message-ID: <MA1PR0101MB14003A9BE0815933D2EA3874F0BD0_at_MA1PR0101MB1400.INDPRD01.PROD.OUTLOOK.COM>
Hi All,
What is the fastest and safest way to Purge SYSAUX tablespace. Currently it has grown to ~ 1tb in size. Please suggest as SQL_MANAGEMENT_BASE occupant needs to be released. Currently we have disabled automatic baseline gather. Its retention is 53 weeks.
Please suggest.
(1a) SYSAUX usage - Schema breakdown (dba_segments)
|
| Total SYSAUX size 931,055.5 MB ( AUTOEXTEND ON for 33 out of 36 files )
| Fixed limit 92,157.0 MB
| Auto Extent limit 1,081,343.5 MB
|
| Schema SYS occupies 925,270.8 MB ( 99.4% )
| Schema AUDSYS occupies 4,948.6 MB ( 0.5% )
| Schema APEX_050000 occupies 339.7 MB ( 0.0% )
| Schema APEX_040200 occupies 247.4 MB ( 0.0% )
| Schema MDSYS occupies 120.9 MB ( 0.0% )
| Schema XDB occupies 67.7 MB ( 0.0% )
| Schema ORDDATA occupies 17.4 MB ( 0.0% )
| Schema SYSTEM occupies 16.8 MB ( 0.0% )
| Schema WMSYS occupies 9.9 MB ( 0.0% )
| Schema DVSYS occupies 5.3 MB ( 0.0% )
| Schema GSMADMIN_INT occupies 4.6 MB ( 0.0% )
| Schema CTXSYS occupies 4.1 MB ( 0.0% )
| Schema DBSNMP occupies 1.4 MB ( 0.0% )
| Schema ORDSYS occupies 0.4 MB ( 0.0% )
| Schema DBSFWUSER occupies 0.4 MB ( 0.0% )
| Schema APPQOSSYS occupies 0.1 MB ( 0.0% )
|
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SQL_MANAGEMENT_BASE SYS 742,080.9 MB
| SM/AWR SYS 141,251.1 MB
| AUDSYS AUDSYS 4,948.6 MB
| SM/ADVISOR SYS 4,289.8 MB
| SM/OPTSTAT SYS 2,977.3 MB
| JOB_SCHEDULER SYS 124.8 MB
| SDO MDSYS 120.9 MB
| SM/OTHER SYS 72.8 MB
| XDB XDB 67.7 MB
| AO SYS 42.4 MB
| XSOQHIST SYS 42.4 MB
| ORDIM/ORDDATA ORDDATA 17.4 MB
| LOGMNR SYSTEM 14.6 MB
| WM WMSYS 9.9 MB
| TEXT CTXSYS 4.1 MB
| SMON_SCN_TIME SYS 3.3 MB
| PL/SCOPE SYS 2.6 MB
| LOGSTDBY SYSTEM 1.5 MB
| EM_MONITORING_USER DBSNMP 1.4 MB
| STREAMS SYS 1.2 MB
| ORDIM ORDSYS 0.4 MB
| AUTO_TASK SYS 0.4 MB
| AUDIT_TABLES SYS 0.0 MB
| EM SYSMAN 0.0 MB
| EXPRESSION_FILTER EXFSYS 0.0 MB
| ORDIM/ORDPLUGINS ORDPLUGINS 0.0 MB
| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA 0.0 MB
| STATSPACK PERFSTAT 0.0 MB
| TSM TSMSYS 0.0 MB
| ULTRASEARCH WKSYS 0.0 MB
| ULTRASEARCH_DEMO_USE WK_TEST 0.0 MB
| XSAMD OLAPSYS 0.0 MB
|
| Others (Unaccounted space) 34,980.1 MB
|
(1c) SYSAUX usage - Unregistered Schemas
| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema APEX_050000 occupies 339.7 MB
| Schema APEX_040200 occupies 247.4 MB
| Schema DVSYS occupies 5.3 MB
| Schema GSMADMIN_INT occupies 4.6 MB
| Schema DBSFWUSER occupies 0.4 MB
| Schema APPQOSSYS occupies 0.1 MB
|
| Total space 597.4 MB
|
Best Regards,
Sourav Biswas
+91-9650017306
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 30 2019 - 13:51:01 CEST