What will happen if I drop SYSAUX tablespace from Oracle 11G

oracleoracle-11g-r2tablespaces

As there are two mandatory tablespaces in Oracle SYSTEM and SYSAUX.
They have mention on docs 1 " You cannot drop the SYSTEM tablespace. You can drop the SYSAUX tablespace only if you have the SYSDBA system privilege and you have started the database in MIGRATE mode. " that means we can drop SYSAUX tablespace, then how system will be affected to this

Best Answer

First, it is important to understand the purpose of SYSAUX tablespace. As the name implies, it is intended to support system tablespace, by holding metadata about database components and other frequent changing data, such as reports.

Docs: "If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability."

The startup migrate option was introduced on version 9i, and it's only suitable when migrating oracle across different versions of database. So, why can I drop the sysaux tablespace with "startup migrate"? Only because Oracle will internally take care of the general errors that are expected to happen during migration processes. It will change a bunch of parameters through alter session, enable some trace events, all of these to make sure migration will happen smoothly. Example of parameters:

ALTER SYSTEM ENABLE RESTRICTED SESSION; 
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY; 
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY; 
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY; 
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

So do not drop sysaux tablespace if you don't have a reasonable reason to do so. If you need to manage its data, there are a lot of options:

  • You can query v$sysaux_ocuppants to determine what resources are taking space;

  • You can move some of its contents to another tablespace through packages/ procedures;

Related Question