patternMinor
What will happen if I drop SYSAUX tablespace from Oracle 11G
Viewed 0 times
oracle11gtablespacewhathappendropwillsysauxfrom
Problem
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
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
Solution
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:
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;
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;
Code Snippets
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;Context
StackExchange Database Administrators Q#110626, answer score: 2
Revisions (0)
No revisions yet.