Hi,
we can see few of the internal schema users maintained by oracle database are under USERS tablespace instead of SYSAUX, can we change default tablespace on the fly for these schemas?
Oracle Internal schema users:
USERNAME O DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ - ------------------------------ ------------------------------
ANONYMOUS Y SYSAUX TEMP
APPQOSSYS Y SYSAUX TEMP
DBSNMP Y SYSAUX TEMP
GSMADMIN_INTERNAL Y SYSAUX TEMP
WMSYS Y SYSAUX TEMP
XDB Y SYSAUX TEMP
OUTLN Y SYSTEM TEMP
SYS Y SYSTEM TEMP
SYSTEM Y SYSTEM TEMP
AUDSYS Y USERS TEMP
DIP Y USERS TEMP
GSMCATUSER Y USERS TEMP
GSMUSER Y USERS TEMP
ORACLE_OCM Y USERS TEMP
SYSBACKUP Y USERS TEMP
SYSDG Y USERS TEMP
SYSKM Y USERS TEMP
XS$NULL Y USERS TEMP
Yes you can, but most of these do not use any database space anyway. AUDSYS is the probably the only exception.
SQL> select owner, count(*)
2 from dba_segments
3 where owner in (
4 'AUDSYS'
5 ,'DIP'
6 ,'GSMCATUSER'
7 ,'GSMUSER'
8 ,'ORACLE_OCM'
9 ,'SYSBACKUP'
10 ,'SYSDG'
11 ,'SYSKM'
12 ,'XS$NULL'
13 )
14 group by owner;
OWNER COUNT(*)
------------------------------ ----------
AUDSYS 70
Note that this will not move any *existing* segments. You can use DBMS_AUDIT_MGMT to manage the audit entries in your database