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：后边又找了个测试库打补丁，还是不行。只要解决占用问题即可。
