2

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

  • 1
    I'd just leave it the way it is. If you want to use different tablespaces for your data, then fine, but I would advise against dropping the SYSAUX tablespace. Just because something is possible doesn't mean that you should do it. – Vérace Aug 11 '15 at 5:40
  • Ya we are not going to do that ! But there should be something that Oracle has provided such kind of option, that something I want to know. – Paresh Zawar Aug 11 '15 at 7:34
  • Just because you can doesn't mean you should.... – kevinsky Aug 11 '15 at 14:55
1

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;

0

Oracle specifically split out the sysaux from the system, and IMO this was a good choice by them. Essentially, without going deeply into the documentation which you can read for yourself, SYSAUX stores things like statistics history, non-essential performance views, and other pieces of metadata that may or may not be in-use in a particular database, depending on what that database is used for. It is sort of a "separation of duties", and allows for easier manipulation of these non-critical objects w/o SYSTEM level downtime (I'm generalizing here, 11g isn't 100% perfect in that regard).

I'd break it down like this, as-if the database was the human body: SYSTEM is your nervous and cardiovascular system. You cannot live without it.

SYSAUX is all of the other systems of your body - you can survive without them but for optimal performance it's best to keep them in good working order.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.