TIP
You don??™t need to specify UNDO_RETENTION unless you have
Flashback or LOB retention requirements; the UNDO_RETENTION
parameter is not used for managing transaction rollback.
Multiple Undo Tablespaces
As mentioned earlier in this chapter, a database can have multiple undo tablespaces, but only one
of them can be active for a given instance at any one time. In this section, we??™ll show an example
of switching to a different undo tablespace while the database is open.
NOTE
In a Real Application Clusters (RAC) environment, one undo
tablespace is required for each instance in the cluster.
218 Oracle Database 11g DBA Handbook
In our dw database, we have three undo tablespaces:
SQL> select tablespace_name, status from dba_tablespaces
2 where contents = 'UNDO';
TABLESPACE_NAME STATUS
--------------------------- ---------
UNDOTBS1 ONLINE
UNDO_BATCH ONLINE
UNDO_BI ONLINE
2 rows selected.
But only one of the undo tablespaces is active:
SQL> show parameter undo_tablespace
NAME TYPE VALUE
-------------------------- ----------- ----------------------
undo_tablespace string UNDOTBS1
For overnight processing, we change the undo tablespace from UNDOTBS1 to the tablespace
UNDO_BATCH, which is much larger to support higher DML activity. The disk containing the
daytime undo tablespace is much faster but has a limited amount of space; the disk containing
the overnight undo tablespace is much larger, but slower. As a result, we use the smaller undo
tablespace to support OLTP during the day, and the larger undo tablespace for our data mart and
data warehouse loads, as well as other aggregation activities, at night when response time is not
as big of an issue.
Pages:
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376