Oracle v$sysaux_occupants Tips

Oracle Database Tips by Donald BurlesonMay 26, 2015

Question: How can see the contents of my SYSAUX tablespace? I want to move the contents, but I don't know the proper procedures.

Answer: The SYSAUX tablespace was new in Oracle10g, and there are several views that assist with SYSAUX including the v$sysaux_occupants view. The SYSAUX tablespace provides storage of non-sys-related tables and indexes that traditionally were placed in the SYSTEM tablespace.

You can query the SYSAUX tablespace with this query:

set linesize 120
set pagesize 100

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 1;

For example, the tables and indexes that were previously owned by the system user can now be specified for a SYSAUX tablespace. You can query the v$sysaux_occupants view to find the exact components stored within the SYSAUX tablespace.

OCCUPANT_NAME	OCCUPANT_DESC	SCHEMA_NAME	MOVE_PROCEDURE
LOGMNR	LogMiner	SYSTEM	SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY	Logical Standby	SYSTEM	SYS.DBMS_LOGSTDBY.SET_TABLESPACE
STREAMS	Oracle Streams	SYS
AO	Analytical Workspace Object Table	SYS	DBMS_AW.MOVE_AWMETA
XSOQHIST	OLAP API History Tables	SYS	DBMS_XSOQ.OlapiMoveProc
SMC	Server Manageability Components	SYS
STATSPACK	Statspack Repository	PERFSTAT
ODM	Oracle Data Mining	DMSYS	MOVE_ODM
SDO	Oracle Spatial	MDSYS	MDSYS.MOVE_SDO
WM	Workspace Manager	WMSYS	DBMS_WM.move_proc
ORDIM	Oracle interMedia ORDSYS Components	ORDSYS
ORDIM/PLUGINS	Oracle interMedia ORDPLUGINS Components	ORDPLUGINS
ORDIM/SQLMM	Oracle interMedia SI_INFORMTN_SCHEMA Components	SI_INFORMTN_SCHEMA
EM	Enterprise Manager Repository	SYSMAN	emd_maintenance.move_em_tblspc
TEXT	Oracle Text	CTXSYS	DRI_MOVE_CTXSYS
ULTRASEARCH	Oracle Ultra Search	WKSYS	MOVE_WK
JOB_SCHEDULER	Unified Job Scheduler	SYS

v$sysaux_occupants View Source

The Oracle data dictionary defines the v$sysaux_occupants view using the following source query:

SELECT
   occupant_name,
   occupant_desc,
   schema_name, move_procedure, move_procedure_desc, space_usage_kbytes
FROM
   gv$sysaux_occupants
WHERE
   inst_id = USERENV('INSTANCE');

��