Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.
There are three types of tablespaces: (1) permanent, (2) undo, and (3) temporary.
Permanent – These tablespaces store objects in segments that are permanent – that persist beyond the duration of a session or transaction.
Undo – These tablespaces store undo segments that may be retained beyond a transaction, but are basically used to:
Provide read consistency for SELECT statements that access tables that have rows that are in the process of being modified.
Provide the ability to rollback a transaction that fails to commit.
Temporary – This tablespace stores segments that are transient and only exist for the duration of a session or a transaction. Mostly, a temporary tablespace stores rows for sort and join operations.
SYSTEM Tablespace
The SYSTEM tablespace is used by the Oracle server to manage the database.
It contains the data dictionary and tables that contain administrative information about the database. These are all contained in the SYS schema and can be accessed only by the SYS user or other administrative users with the required privilege.
Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created.
SYSAUX Tablespace
This is an auxiliary tablespace to the SYSTEM tablespace. Some components and products that used the SYSTEM tablespace or their own tablespaces in earlier releases of Oracle Database now use the SYSAUX tablespace. Every Oracle Database 10g (or later release) database must have a SYSAUX tablespace.
SYSAUX tablespace stores data for auxiliary applications such as the LogMiner, Workspace Manager, Oracle Data Mining, Oracle Streams, and many other Oracle tools.
A TEMPORARY tablespace is used to manage space for sort operations. Sort operations generate segments, sometimes large segments or lots of them depending on the sort required to satisfy the specification in a SELECT statement's WHERE clause. Sort operations are also generated by SELECT statements that join rows from within tables and between tables. Each database needs to have a specified default temporary tablespace. If one is not specified, then any user account created without specifying a TEMPORARY TABLESPACE clause is assigned a temporary tablespace in the SYSTEM tablespace!.You don't want system users to execute SELECT commands that cause sort operations to take place within the SYSTEM tablespace.
If a default temporary tablespace is not specified at the time a database is created, a DBA can create one by altering the database.
A default temporary tablespace cannot be dropped unless a replacement is created. This is usually only done if you were moving the tablespace from one disk drive to another.
You cannot take a default temporary tablespace offline – this is done only for system maintenance or to restrict access to a tablespace temporarily. None of these activities apply to default temporary tablespaces.
You cannot alter a default temporary tablespace to make it permanent.
Undo Tablespaces
This is the undo tablespace used by the database server to store undo information. If a database uses Automatic Undo Management, then it can only use a single undo tablespace at any given time. This tablespace is created at database creation time.
An undo tablespace cannot be dropped if it is being used by any instance or contains any undo information needed to recover transactions
More than one UNDO tablespace can exist in DB, but only one can be active at a time
USERS, DATA and INDEXES Tablespaces
Most Oracle databases will have a USERS permanent tablespace.
This tablespace is used to store objects created by individual users of the database.
This tablespace is used to store user objects and data. If no default tablespace is
specified when a user is created then the USERS tablespace is the default tablespace for all objects created by that user. For the SYS and SYSTEM users, the default permanent
tablespace is SYSTEM.
For large applications, it is often a practice to create a seprate tablespace to store data for the application. In this case the tablespace may be named whatever name is appropriate to describe the objects stored in the tablespace accurately.
Oracle databases having a DATA (or more than one DATA) tablespace will also have an accompanying INDEXES tablespace.
There are three types of tablespaces: (1) permanent, (2) undo, and (3) temporary.
Permanent – These tablespaces store objects in segments that are permanent – that persist beyond the duration of a session or transaction.
Undo – These tablespaces store undo segments that may be retained beyond a transaction, but are basically used to:
Provide read consistency for SELECT statements that access tables that have rows that are in the process of being modified.
Provide the ability to rollback a transaction that fails to commit.
Temporary – This tablespace stores segments that are transient and only exist for the duration of a session or a transaction. Mostly, a temporary tablespace stores rows for sort and join operations.
SYSTEM Tablespace
The SYSTEM tablespace is used by the Oracle server to manage the database.
It contains the data dictionary and tables that contain administrative information about the database. These are all contained in the SYS schema and can be accessed only by the SYS user or other administrative users with the required privilege.
Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created.
- The SYSTEM tablespace is always online when the database is open.
- Accessing it requires a higher level of privilege.
- You cannot rename or drop a SYSTEM tablespace.
- You cannot take a SYSTEM tablespace offline.
- The SYSTEM tablespace always contains the data dictionary tables for the entire database.
- This is a permanent tablespace.
SYSAUX Tablespace
This is an auxiliary tablespace to the SYSTEM tablespace. Some components and products that used the SYSTEM tablespace or their own tablespaces in earlier releases of Oracle Database now use the SYSAUX tablespace. Every Oracle Database 10g (or later release) database must have a SYSAUX tablespace.
SYSAUX tablespace stores data for auxiliary applications such as the LogMiner, Workspace Manager, Oracle Data Mining, Oracle Streams, and many other Oracle tools.
- This tablespace is automatically created if you use the Database Creation Assistant software to build an Oracle database.
- ike the SYSTEM tablespace, SYSAUX requires a higher level of security and it cannot be dropped or renamed.
- Do not allow user objects to be stored in SYSAUX. This tablespace should only store system specific objects.
- This is a permanent tablespace.
A TEMPORARY tablespace is used to manage space for sort operations. Sort operations generate segments, sometimes large segments or lots of them depending on the sort required to satisfy the specification in a SELECT statement's WHERE clause. Sort operations are also generated by SELECT statements that join rows from within tables and between tables. Each database needs to have a specified default temporary tablespace. If one is not specified, then any user account created without specifying a TEMPORARY TABLESPACE clause is assigned a temporary tablespace in the SYSTEM tablespace!.You don't want system users to execute SELECT commands that cause sort operations to take place within the SYSTEM tablespace.
If a default temporary tablespace is not specified at the time a database is created, a DBA can create one by altering the database.
A default temporary tablespace cannot be dropped unless a replacement is created. This is usually only done if you were moving the tablespace from one disk drive to another.
You cannot take a default temporary tablespace offline – this is done only for system maintenance or to restrict access to a tablespace temporarily. None of these activities apply to default temporary tablespaces.
You cannot alter a default temporary tablespace to make it permanent.
Undo Tablespaces
This is the undo tablespace used by the database server to store undo information. If a database uses Automatic Undo Management, then it can only use a single undo tablespace at any given time. This tablespace is created at database creation time.
An undo tablespace cannot be dropped if it is being used by any instance or contains any undo information needed to recover transactions
More than one UNDO tablespace can exist in DB, but only one can be active at a time
USERS, DATA and INDEXES Tablespaces
Most Oracle databases will have a USERS permanent tablespace.
This tablespace is used to store objects created by individual users of the database.
This tablespace is used to store user objects and data. If no default tablespace is
specified when a user is created then the USERS tablespace is the default tablespace for all objects created by that user. For the SYS and SYSTEM users, the default permanent
tablespace is SYSTEM.
For large applications, it is often a practice to create a seprate tablespace to store data for the application. In this case the tablespace may be named whatever name is appropriate to describe the objects stored in the tablespace accurately.
Oracle databases having a DATA (or more than one DATA) tablespace will also have an accompanying INDEXES tablespace.
- The purpose of separating tables from their associated indexes is to improve I/O efficiency.
- The DATA and INDEXES tablespaces will typically be placed on different disk drives thereby providing an I/O path for each so that as tables are updated, the indexes can also be updated simultaneously.
No comments:
Post a Comment