Oracle 10.2.0.3中切换TEMP Tablespace Group

 SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production

TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

--当前库有两个临时表空间TEMP和TEMP2
SQL> SELECT tablespace_name ,file_name FROM dba_temp_files;

TABLESPACE_NAME                FILE_NAME
------------------------------  --------------------------------------------------------------------------------
TEMP                                E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEMP01.DBF
TEMP2                             E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEMP2.DBF

--临时表空间TEMP2属于临时表空间组TEMP_GROUP
SQL> SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------        ------------------------------
TEMP_GROUP                     TEMP2

--当前用户使用的是临时表空间组TEMP_GROUP，即临时表空间TEMP2
SQL> SELECT D.USERNAME, D.TEMPORARY_TABLESPACE
  2    FROM DBA_USERS D
  3   WHERE USERNAME = 'LW_TEST';

USERNAME                       TEMPORARY_TABLESPACE
------------------------------    ------------------------------
LW_TEST                          TEMP_GROUP

而临时表空间组在oracle10.2.0.3中会导致cpu的使用过高，从而导致系统性能下降。

而我新接手的这个库正好存在这个问题。

表象之一是在AWR报告中可以看到下面这条sql的解析和执行频率都比较高。
select min(bitmapped) from ts$ where dflmaxext =:1 and bitand(flags, 1024) = 1024.

解决方法很简单，使用普通的临时表空间或打相应的补丁。

在MOS上的【ID 394381.1】中有相应的记录。
1> Use single temp tablespace as user's temp tablespace.
2> Apply patch for the bug 5455880.

 

下面记录一下将临时表空间组切换为临时表空间的过程。

一、先将临时表空间temp2从临时表空间组中移除。
SQL> alter tablespace temp2 tablespace group '' ;

SQL> SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------         ------------------------------

但此时查询到用户使用的还是临时表空间组TEMP_GROUP

SQL> SELECT D.USERNAME, D.TEMPORARY_TABLESPACE
  2    FROM DBA_USERS D
  3   WHERE USERNAME = 'LW_TEST';

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
LW_TEST                        TEMP_GROUP

 

二、设置当前用户的临时表空间为普通的临时表空间。
SQL> ALTER USER lw_test TEMPORARY TABLESPACE TEMP;

 

三、查询当前用户的临时表空间已经是我们刚设置的临时表空间。
SQL> SELECT D.USERNAME, D.TEMPORARY_TABLESPACE
  2    FROM DBA_USERS D
  3   WHERE USERNAME = 'LW_TEST';

USERNAME                       TEMPORARY_TABLESPACE
------------------------------     ------------------------------
LW_TEST                            TEMP

 

--创建临时表空间组的方法

 CREATE TEMPORARY TABLESPACE temp2  TEMPFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEMP2.DBF'
SIZE 10m TABLESPACE GROUP temp_group;