Just for a technical hint, we can use temporary tablespace group in place of singe temporary tablespacce to be dynamic management of temporary space.
Using a tablespace group for temporary tablespace management
Using temporary tablespace group for version<11 should be caution of BUG 5455880, marked in METALINK.
Creating a Tablespace Group
You create a tablespace group implicitly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist.
For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:
CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'
TABLESPACE GROUP group1;
ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;
You can add a tablespace to an existing tablespace group by specifying the existing group name in the TABLESPACE GROUP clause of the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement.
The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3 to group1, so that group1 contains tablespaces lmtemp2 and lmtemp3.
CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf'
TABLESPACE GROUP group1;
The following statement also adds a tablespace to an existing group, but in this case because tablespace lmtemp2 already belongs to group1, it is in effect moved from group1 to group2:
ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;
Now group2 contains both lmtemp and lmtemp2, while group1 consists of only tmtemp3.
You can remove a tablespace from a group as shown in the following statement:
ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';
Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer any members of group1, this results in the implicit deletion of group1.
Use the ALTER DATABASE...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:
ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;
Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces lmtemp and lmtemp2.
If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablespaces. You must first remove the tablespace from the tablespace group. Likewise, you cannot drop a single temporary tablespace as long as it is the default temporary tablespace.
1 query $sort_usage, $session, $sqltext for top SQL using temporary tablespace.
2 BUG 5455880 (reference Eygle's blog):
Affected version Oracle 10.2.0.3, 10.1.0.5 (confirmed), version<11 (believed)
Top SQL impacted performance: select min(bitmapped) from ts$ where dflmaxext:1 and bitabt(flag, 1024)=1024
When using a tablespace group as the temporary group excessive recursive queried against TS$ can impact performance.
3 excessive space allocation
resize temporary tablespace data file for excessive space allocation:
alter database tempfile '/data/xxx.tmp' resize 20G;
alter tablespace temp shrink space keep 100M;
4 v$tempseg_usage relace of v$sort_usage
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26136400/viewspace-707015/，如需转载，请注明出处，否则将追究法律责任。