SYSAUX Occupants

This is a draft note that’s been hanging around a very long time for a final edit and print. I was prompted to rediscover it today when I noticed a question on the Oracle Developers’ forum asking about reducing the size of a SYSAUX tablespace that currently included 400GB of free space.

Obviously the usual problem of used extents at the top end of a file can apply even for SYSAUX, and it’s highly likely that it will be necessary to move a few objects around (possibly within the tablespace, possibly out of the tablespace and back) but it’s easy to forget that some of the objects in SYSAUX are sufficiently important that they should only be moved using a procedure that has been supplied by Oracle Corp.

The list of contents of SYSAUX and, where they exist, the relevant procedure to move them are listed in view v$sysaux_occupants, so it’s always worth a quick check of the view before you do anything else – hence this little script:

rem
rem     Script:         sysaux_list.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2012
rem 


set linesize 156
set pagesize 60
set trimspool on
set tab off

spool sysaux_list.lst

-- execute print_table('select * from v$sysaux_occupants order by space_usage_kbytes')

column  schema_name             format a32
column  occupant_name           format a32
column  move_procedure          format a45
column  space_usage_kbytes      format 999,999,999

select 
        schema_name, occupant_name, move_procedure, space_usage_kbytes 
from 
        v$sysaux_occupants
order by 
        space_usage_kbytes
;

spool off

I don’t think there’s anything in the script that needs comment, apart from the optional line I’ve put in that uses Tom Kyte’s print_table() procedure to output rows in a tabular layout that produces a convenient full dump of the view. It’s a standard bit of defensive programming that helps me (sometimes) to avoid missing changes to view contents as versions are upgrade.

Here’s a small extract from the two sections of the output from a little VM sandbox of 19.11:

OCCUPANT_NAME                  : LOGMNR
OCCUPANT_DESC                  : LogMiner
SCHEMA_NAME                    : SYSTEM
MOVE_PROCEDURE                 : SYS.DBMS_LOGMNR_D.SET_TABLESPACE
MOVE_PROCEDURE_DESC            : Move Procedure for LogMiner
SPACE_USAGE_KBYTES             : 0
CON_ID                         : 3

-----------------

...

OCCUPANT_NAME                  : SM/OTHER
OCCUPANT_DESC                  : Server Manageability - Other Components
SCHEMA_NAME                    : SYS
MOVE_PROCEDURE                 :
MOVE_PROCEDURE_DESC            : *** MOVE PROCEDURE NOT APPLICABLE ***
SPACE_USAGE_KBYTES             : 72896
CON_ID                         : 3

-----------------

OCCUPANT_NAME                  : SDO
OCCUPANT_DESC                  : Oracle Spatial
SCHEMA_NAME                    : MDSYS
MOVE_PROCEDURE                 : MDSYS.MOVE_SDO
MOVE_PROCEDURE_DESC            : Move Procedure for Oracle Spatial
SPACE_USAGE_KBYTES             : 199552
CON_ID                         : 3

-----------------

32 row(s) selected



SCHEMA_NAME                      OCCUPANT_NAME                    MOVE_PROCEDURE                                SPACE_USAGE_KBYTES
-------------------------------- -------------------------------- --------------------------------------------- ------------------
SYSTEM                           LOGMNR                           SYS.DBMS_LOGMNR_D.SET_TABLESPACE                               0
...
AUDSYS                           AUDSYS                           DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION                  39,488
SYS                              AO                               DBMS_AW.MOVE_AWMETA                                       45,696
XDB                              XDB                              XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE                     62,144
SYS                              SM/OPTSTAT                                                                                 68,288
SYS                              SM/OTHER                                                                                   72,896
MDSYS                            SDO                              MDSYS.MOVE_SDO                                           199,552

32 rows selected.

Addendum

It’s a long time since I had to help someone clean load of garbage from sysaux – typically from excess space usage by the audit table, the histogram history table, or the partition synopsis table – so I thought I’d take a quick look around the internet and MOS for any notes that might be good starting points for doing the job in a recent version of Oracle. The biggest unknown for me was the need to mess around inside a PDB, so this addendum is largely a pointer to get me started if I do need to do something with a PDB in the future.

Searching MOS for “v$sysaux_occupants” I noted a number of documents could offer helpful comments, in particular I picked on “Doc ID 1499542.1: Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace.” which made reference to the dbms_pdb package and the need to use dbms_pdb.exec_as_oracle_script() to move objects in a PDB’s sysaux tablespace, and the slightly more generic Doc ID 2798505.1 How To Move Sys Table in PDB that made a similar point and highlighted Oracle error ORA-65040: operation not allowed from within a pluggable database.

Following this detail with a further search on google I then found a note on Marco Mischke’s blog with the title: “Shrink SYSAUX of a Pluggable Database”. The note is 6 years old, so shouldn’t be followed thoughtlessly – things change with time – but it’s a good starting point.