Monitoring Occupants of the SYSAUX Tablespace

Post by **jimb** » Wed Jul 18, 2012 2:10 pm

To check who is occupying large portion on SYSAUX.

You can monitor the occupants of the SYSAUX tablespace using the V$SYSAUX_OCCUPANTS view. 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

Use the below query to verify:

set pages 0
set lines 350
col space_mb format 999,999,999.99
select occupant_desc,space_usage_kbytes/1024 space_mb, substr(schema_name,1,30) Schema from v$sysaux_occupants order by 2 desc
/

Sample Output:

Code: Select all

SQL> set pages 0
set lines 350
col space_mb format 999,999,999.99
select occupant_desc,space_usage_kbytes/1024 space_mb, substr(schema_name,1,30) Schema from v$sysaux_occupants order by 2 desc
/
SQL> SQL> SQL>   2
Server Manageability - Automatic Workload Repository                   61,596.63 SYS
Server Manageability - Optimizer Statistics History                       457.94 SYS
Server Manageability - Advisor Framework                                  297.06 SYS
XDB                                                                        54.94 XDB
LogMiner                                                                    7.63 SYSTEM
Server Manageability - Other Components                                     7.06 SYS
Workspace Manager                                                           6.44 WMSYS
PL/SQL Identifier Collection                                                3.94 SYS
Transaction Layer - SCN to TIME mapping                                     3.25 SYS
Unified Job Scheduler                                                       1.94 SYS
SQL Management Base Schema                                                  1.69 SYS
Analytical Workspace Object Table                                           1.38 SYS
OLAP API History Tables                                                     1.38 SYS
Logical Standby                                                             1.00 SYSTEM
Oracle Streams                                                              1.00 SYS
Automated Maintenance Tasks                                                  .31 SYS
Oracle Transparent Session Migration User                                    .25 TSMSYS
Oracle interMedia ORDPLUGINS Components                                      .00 ORDPLUGINS
Oracle interMedia SI_INFORMTN_SCHEMA Components                              .00 SI_INFORMTN_SCHEMA
Enterprise Manager Repository                                                .00 SYSMAN
Oracle Text                                                                  .00 CTXSYS
Oracle Ultra Search                                                          .00 WKSYS
Oracle Spatial                                                               .00 MDSYS
Expression Filter System                                                     .00 EXFSYS
Enterprise Manager Monitoring User                                           .00 DBSNMP
Statspack Repository                                                         .00 PERFSTAT
OLAP Catalog                                                                 .00 OLAPSYS
Oracle interMedia ORDSYS Components                                          .00 ORDSYS
Oracle Ultra Search Demo User                                                .00 WK_TEST

29 rows selected.

SQL> SQL>

Verify also if there are regular users using the SYSAUX as the default tablespace :

select username,account_status,default_tablespace, created from dba_users where default_tablespace='SYSAUX';

Code: Select all

SQL> select username,account_status,default_tablespace, created from dba_users where default_tablespace='SYSAUX';
ANONYMOUS                      LOCKED                           SYSAUX                         12-FEB-11
XDB                            EXPIRED & LOCKED                 SYSAUX                         12-FEB-11
WMSYS                          EXPIRED & LOCKED                 SYSAUX                         22-JUL-09

#Taniaangel · Post by **#Taniaangel** » Wed Aug 22, 2012 5:49 am

Hello everyone, I'm Tania.
Hope i'll get along well with you guys.

by drake » Wed Aug 22, 2012 4:34 pm

Hello Tania,

Thanks for visiting this forum.
We're happy to have you here. Just keep in touch.

Are you also working around with Oracle Technology?