Oracle如何切换temp表空间以及需要注意的点

Oracle如何切换temp表空间以及需要注意的点

昨天有套库报/u01的磁盘90%使用率报警了，无法通过LVM在原盘基础上扩容，计划新增加磁盘并将能在线迁移的相关数据库文件迁移走。

其中，对于temp表空间的处理，我采取的方法实际和undo差不多，均是通过切换来达到目的。

版本是11.0.2.4.0.200714

记录一下遇到的一些问题。

假设现在的临时表空间为temp，我要切换成temp2。

参考mos文档How to Resize the Temporary Tablespace in a Production Environment (文档 ID 160681.1)，切换的官方步骤很简单，如下：

1.create temporary tablespace temp2 tempfile '/data/temp02.dbf' size 10g autoextend off;   <------这里文件总大小保持和原临时表空间一致，超过32G需要多个文件
2.alter database default temporary tablespace temp2;
3.alter database tempfile '/u01/app/oracle/oradata/xxxxdb/temp01.dbf' offline; <------官方的语句是"alter tablespace TEMP offline;"，这个语法不支持临时表空间，也就是官方给的是错的；另外，有多少个文件就offline多少个
4.drop tablespace TEMP including contents and datafiles;

以上步骤需要注意的是，第2步骤原本我是通过修改当前默认临时表空间为temp的用户，将默认临时表空间修改为temp2。因为可能原来数据库中不止存在temp，甚至可能有临时表空间组。

--以下SQL会生成DCL语句，执行这些语句
select 'alter user '||username||' temporary tablespace temp2;' from dba_users where temporary_tablespace='TEMP';

但是这一步遇到了一个问题，我的库中存在用户“XS$NULL”，会导致报错ORA-01031。(如果你的库没有这个用户，则可以用上边的语句这么做)。

17:32:46 SYS@test(201)> alter user XS$NULL temporary tablespace temp2;
alter user XS$NULL temporary tablespace temp2
*
ERROR at line 1:
ORA-01031: insufficient privileges


Elapsed: 00:00:00.00

官方明确指出无法对用户XS$NULL做任何alter user的语句。

如果想要更改默认临时表空间，只能通过上边第2步骤来更改库级别的默认临时表空间，也会同步更改XS$NULL 用户临时表空间。

只是，对于多个临时表空间的库来说(或者多个临时表空间组)，会导致所有用户都使用了temp2，需要提前记录用户原来使用的临时表空间情况方便后续还原。

关于用户XS$NULL，这里简单介绍：

XS$NULL 是在安装数据库组件 Oracle XML Database (XDB) 时创建的。它是一个内部帐户，表示会话中没有用户。轻量级会话基础结构(APEX 和 XDB 会使用它)会使用它，并且此用户的名称在使用轻量级会话的模块中是硬编码的。因此，XS$NULL 必须存在于数据库中。请注意，由于 XS$NULL 并不是真正的用户，因此只有 Oracle Database 实例才能访问此帐户。XS$NULL 没有任何权限，没有人可以以 XS$NULL 的身份进行身份验证，也不能将身份验证凭据分配给 XS$NULL。

卸载 XDB 时会删除此帐户。

另外，上边第4步骤执行的时候可能卡住。

https://www.cnblogs.com/PiscesCanon/p/18279318

此时，查询该会话等待事件的信息，如下：

15:38:11 SYS@xxxxdb(597)> select event,status,state,blocking_session from v$session where sid=399;

EVENT                 STATUS  STATE      BLOCKING_SESSION
--------------------- ------- ---------- ----------------
enq: TS - contention  ACTIVE  WAITING                   3

sid为399是执行第4步骤的会话，此时被sid=3的会话卡住，再次查询sid=3的会话信息：
15:38:35 SYS@xxxxdb(597)> select program,event,status,state,blocking_session from v$session where sid=3;

PROGRAM               EVENT                 STATUS  STATE      BLOCKING_SESSION
--------------------- --------------------- ------- ---------- ----------------
oracle@xxxxdb (SMON)  smon timer            ACTIVE  WAITING

根据SMON Blocking Drop Temporary Tablespace (文档 ID 1500044.1)、DDL Operations (Alter and Drop) on Temporary Tablespace Hang (文档 ID 1911167.1)，可能命中BUG。

BUG特征如下：防。

1.会话等待事件为："enq: TS - contention"，符合。

2.会话被SMON阻塞，符合。

3.阻塞会话的SMON等待事件为"SMON TIMER"，符合。

4.数据库版本在11.2.0.3及以上，符合。

官方建议是打上补丁15913577，或者采取临时操作，这里贴上原始英文。

- Complete all the needed prerequisites (create the new temp tablespace and set as default and no user is assigned to the old tablespace)
- Bounce the DB
- Once the DB starts up disconnect from SQLPLUS completely and close the console
- Open a new console and launch SQLPLUS
- Run the drop command as the VERY FIRST COMMAND

我试了下临时操作是不行的，后边看了下当前临时段的占用情况，发现旧的临时表空间temp依然被占用着。

16:16:15 SYS@xxxxdb(17)> SELECT s.username,sid,serial#,t.tablespace,contents,sql_address,extents,last_call_et
16:17:23   2      FROM v$session s,v$tempseg_usage t
16:17:23   3  WHERE s.saddr = t.session_addr;

USERNAME                         SID    SERIAL# TABLESPACE      CONTENTS                    SQL_ADDRESS         EXTENTS LAST_CALL_ET
------------------------- ---------- ---------- --------------- --------------------------- ---------------- ---------- ------------
DBSNMP                             8       7869 TEMP            TEMPORARY                   00                        1           22
SYS                              399      34247 TEMP2           TEMPORARY                   00                        1         1386

Elapsed: 00:00:00.00

DBSNMP用户是OEN客户端的会话，重启OEM客户端之后，第4步骤的会话立刻执行完成。

嘶，，，，，，感觉也不像BUG啊。

后续测试了下，新创建的数据库会话一旦执行删除临时表空间的语句时，会在当前临时表空间占用一点临时段空间。

如果是同一个会话操作同时切换两次表空间(比如temp->temp2,temp2->temp)，需要注意这个问题。

PS：后边又找了个测试库打补丁，还是不行。只要解决占用问题即可。