A temporary tablespace group is a
synonym for a list of temporary tablespaces.
A temporary tablespace group must consist of at least one temporary tablespace; it cannot be
empty. Once a temporary tablespace group has no members, it no longer exists.
One of the big advantages of using temporary tablespace groups is to provide a single user
with multiple sessions with the ability to use a different actual temporary tablespace for each
session. In the diagram shown in Figure 3-3, the user OE has two active sessions that need
temporary space for performing sort operations.
Instead of a single temporary tablespace being assigned to a user, the temporary tablespace
group is assigned; in this example, the temporary tablespace group TEMPGRP has been assigned
to OE. However, because there are three actual temporary tablespaces within the TEMPGRP
temporary tablespace group, the first OE session may use temporary tablespace TEMP1, and the
select statement executed by the second OE session may use the other two temporary tablespaces,
TEMP2 and TEMP3, in parallel. Before Oracle 10g, both sessions would use the same temporary
tablespace, potentially causing a performance issue.
FIGURE 3-3 Temporary tablespace group TEMPGRP
66 Oracle Database 11g DBA Handbook
Creating a temporary tablespace group is very straightforward. After creating the individual
tablespaces TEMP1, TEMP2, and TEMP3, we can create a temporary tablespace group named
TEMPGRP as follows:
SQL> alter tablespace temp1 tablespace group tempgrp;
Tablespace altered.
Pages:
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163