Temp and Undo Tablespace

Temporary Tablespace
Default Temporary Tablespace
Temporary Tablespaces are used to manage space for database sort operations
For e.g.: if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialization parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.
The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:
Syntax:
Create temporary tablespace temp_name tempfile ‘filespec’ size 50m extent management local uniform size 16m;
Or we can assign the temporary tablespace later using alter database command
Alter database default temporary tablespace temp;
How to assign temporary tablespace to user
Create user user_name default tablespace tbs_1 temporary tablespace temp;
Or we can assign later also using alter command
Alter user user_name temporary tablespace temp;
Drop temporary tablespace
Alter database tempfile ‘filespec’ drop including datafiles;
Note: You get an error ORA-25153
Add datafile to temporary tablespace
Alter tablespace temp_nam add tempfile ‘filespec’ size 50m;
Note:
Temporary tablespace cannot contain permanent object, so no need to be back up.
We cannot take offline or drop until new default is made available.
Views :
Database_properties
V$tempfile, dba_temp_files – list the temp file information
V$sort_segment and v$sort_usage – monitor temporary segments
V$temp_space_header – record the free space in temp

Undo Tablespace
Every Oracle database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. Oracle refers to these records collectively as undo.
Undo records are used to
Ø Transaction rollback when Rollback statement issued
Ø Transaction recovery
Ø Read consistency
Specifying the mode of undo management
Ø Auto
Ø Manual
Auto undo management
Undo_management = auto (dp)
The following parameter setting causes the startup command to start an instance in automatic undo management mode.An undo tablespace must be available into oracle will store undo info. Default undo tablespace is create at database creation or create explicitly

Assign parameter in pfile or spfile
undo_tablespace=undo_tbs – (dp) specify undo tablespace name
Undo_retention =300 – (dp) length of time to retain the undo. Default is 900 sec
Undo_suppress_errors= true false – (dp) suppress the error message if manual undo are issued

Manual undo Management
Undo_management = manual
If the UNDO_MANAGEMENT initialization parameter is not specified, the instance starts in manual undo management mode. If an UNDO_TABLESPACE initialization parameter is found, it is ignored
Initialization parameters that can be specified with manual undo management mode
ROLLBACK_SEGMENTS – Specifies the rollback segments to be acquired at instance startup
TRANSACTIONS – Specifies the maximum number of concurrent transactions
TRANSACTIONS_PER_ROLLBACK_SEGMENT - Specifies the number of concurrent transactions that each rollback segment is expected to handle
MAX_ROLLBACK_SEGMENTS - Specifies the maximum number of rollback segments that can be online for any instance

Managing undo tablespace
Oracle strongly recommends operating in automatic undo management mode. Automatic undo management mode is less complex to implement and manage.
Creating undo tablespace
There are two methods of creating an undo tablespace. The first method creates the undo tablespace when the CREATE DATABASE statement is issued. This occurs when you are creating a new database, and the instance is started in automatic undo management mode (UNDO_MANAGEMENT = AUTO). The second method is used with an existing database. It uses the CREATE UNDO TABLESPACE statement.
You cannot create database objects in an undo tablespace
Now we see the second method of creating undo tablespace in existing tablespace.
Create undo tablespace undo_tbs datafile ‘filespace’ size 100m reuse Autoextend on;
Altering an undo tablespace
. Adding and renaming datafile
. Bringing the datafile online or offline
. Beginning and ending an open backup on a datafile
These are only attribute you are permitted to alter in undo tablespace.
If undo tablespace runs out of space, you can add datafile to it or resize the datafiles.
Alter tablespace undo_tbs add datafile ‘filespec’ Autoextend on next 1 maxsize unlimited;
Or you can use alter database command to resize the undo tablespace
Alter database datafile ‘filespec’ resize 200m;
Dropping an undo tablespace
An undo tablespace can only dropped if it is not currently used by any instance and if any outstanding transactions.
Since drop tablespace drop an undo tablespace even if it contains unexpired undo info (within retention time). You must careful on dropping the undo.
Drop tablespace undo_tbsl including contents;
Views
V$undostat – contain statistics for monitoring and tuning undo space.
V$rollstat
V$transaction – contain undo segment infoDba_undo_extents –show commit time for each extent