• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/Oracle Database/ALL KB Oracle/Understanding Oracle Sysaux Tablespace
Popular Search:Oracle, SQL Server, MongoDB

Understanding Oracle Sysaux Tablespace

672 views 0 August 27, 2019 September 5, 2019 admin

From Oracle 10g, the smallest Oracle database is two tablespaces. This applies to Oracle 11g.

  • SYSTEM – stores the data dictionary.
  • SYSAUX – stores data for auxiliary applications (sysaux page link).

In reality, a typical production database has numerous tablespaces. These include SYSTEM and NON-SYSTEM tablespaces.

SYSTEM – a tablespace that is always used to store SYSTEM data that includes data about tables, indexes, sequences, and other objects – this metadata comprises the data dictionary.

  • Every Oracle database has to have a SYSTEM tablespace—it is the first tablespace created when a database is created.
  • Accessing it requires a higher level of privilege.
  • You cannot rename or drop a SYSTEM.
  • You cannot take a SYSTEM tablespace offline.
  • The SYSTEM tablespace could store user data, but this is not normally done—a good rule to follow is to never allow allow the storage of user segments in the SYSTEM
  • This tablespace always has a SYSTEM Undo segment.

It Contains,

  • Data Dictionary Information
  • system rollback segment
  • sys,system objects
  • should not contain user data

When creating a table it is customary to specify a “tablespace <tablespace name>” clause. If you didn’t mention the “tablespace” , Oracle will create the table in the user’s default tablespace. To change the default tablespace for a user you can issue this command:

alter user <username> default tablespace users;

This will keep new tables from getting into the system tablespace. To move a table from the system tablespace, you can use the alter table move tablespace <tablespace name> command.

You can also use the CTAS and rename commands but you must re-add the table constraints.

RECOVERY :

When you lose the SYSTEM tablespace the instance could crash or hang: anyway the instance will alert you as soon as possible.

It’s not rare the case when you cannot even shutdown the instance and proceed with a kill or a shutdown abort command.

If you have a good backup you can  restore it, but the database could not be open until the recovery process finishes.No problem for your committed transactions if your database is running in ARCHIVE LOG mode and they will be available as soon as the database open.

Tags:Tablespace

Was this helpful?

Yes  No
Related Articles
  • Oracle Dbverify DBV Utility
  • Oracle Database Background Processes
  • Managing the Redo Log
  • Creating a Free Account in Oracle Cloud
  • Purging Oracle Sysaux Tablespace
  • Understanding Oracle System Tablespace
Leave A Comment Cancel reply

ALL KB Oracle
  • Understanding Oracle Sysaux Tablespace
  • Oracle Dbverify DBV Utility
  • Oracle Database Background Processes
  • Managing the Redo Log
  • Creating a Free Account in Oracle Cloud
  • Purging Oracle Sysaux Tablespace
View All 6  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org