oracle 10g服务器如何清理临时表空间，请提供linux平台的步骤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30972394/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to cleanup the temp tablespace in oracle 10g server , Please provide the steps for linux plateform
提问by Anand Yadav
I want to clean up the Temptable space which have datafiles
temp01.dbfand temp02.dbf, so please suggest me should I drop
temp01.dbffile or drop the temp tablespace. Datafiles of Temptablespaces is given below
我想清理Temp包含数据文件temp01.dbf和的表空间
temp02.dbf，所以请建议我应该删除
temp01.dbf文件还是删除临时表空间。Temp表空间的数据文件如下
33G temp01.dbf
1.5G temp02.dbf
回答by anudeepks
Create Temporary Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE ‘/u01/app/oradata/temp01.dbf′ SIZE 2000M ;Move Default Database temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;Make sure No sessions are using your Old Temp tablespace
a. Find Session Number from V$SORT_USAGE: SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; b. Find Session ID from V$SESSION: If the resultset contains any rows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset. SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM; OR SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR; c. Kill Session: Now kill the session with IMMEDIATE. ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;Drop temp tablespace
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;Recreate Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/temp/temp01.dbf′ SIZE 2000M;
创建临时表空间 Temp
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE ‘/u01/app/oradata/temp01.dbf′ SIZE 2000M ;移动默认数据库临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;确保没有会话正在使用您的旧临时表空间
a. Find Session Number from V$SORT_USAGE: SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; b. Find Session ID from V$SESSION: If the resultset contains any rows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset. SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM; OR SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR; c. Kill Session: Now kill the session with IMMEDIATE. ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;删除临时表空间
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;重新创建表空间临时
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/temp/temp01.dbf′ SIZE 2000M;
6 Move Tablespace Temp, back to new temp tablespace
6 移动表空间临时，回到新的临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Drop temporary for tablespace temp
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
删除临时表空间临时
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
回答by José Gabriel Zalazar
Thanks @anudeepks! Just wanna point something, in step 5 the sentence should be as follows:
谢谢@anudeepks！只是想指出一些事情，在第 5 步中，句子应如下所示：
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/temp/temp.dbf′ SIZE 2000M;
(Note that the filename has changed from temp01.dbf to temp.dbf). Otherwise we get an error because the file already exists, and also such file will be deleted on step 7.
（请注意，文件名已从 temp01.dbf 更改为 temp.dbf）。否则我们会得到一个错误，因为该文件已经存在，并且该文件将在第 7 步中被删除。
