Expert Tips on Drop Temporary Tablespace Hangs!!
Sometimes it happens that we issue the command for dropping temporary tablespace and the operation just hangs and we keep on waiting for a long time. In this article we will discuss how to resolve the frustrating situation when drop temporary tablespace hangs.
Oracle V$SORT_USAGE view tells you the space utilized within one temporary segment on a session and statement level. An entry is made in the V$SORT_USAGE table whenever an operation uses sort space. Once the operation is complete then that entry is automatically removed. However if any operation is aborted abnormally and dead connections are left then a number of entries are left in the V$SESSION and V$SORT_USAGE. Although one instance has only one sort segment per temporary tablespace but the space within the segment can be used by multiple statements running in different sessions.
Solution – I:
• Find Session Number from V$SORT_USAGE:
First of all you will have to run below command
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
• Find Session ID from V$SESSION:
If the resultset contains any tows 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;• Kill Session:
Now kill the session with IMMEDIATE.
ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;
• Check Each Instance on RAC:
If you are working on RAC then the final step will be to check each Instance on RAC.
Solution – II:
• Create New Temporary Tablespace:
First of all you will have to create a new temporary tablespace.
CREATE TEMPORARY TABLESPACE MYTEMP2 TEMPFILE '/ORADATA2/ MYTEMP02.DBF' SIZE 100M;
• Assign all users to new Tablespace:
Now you will assign all users to this new tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE MYTEMP2;
• Find Inactive Sessions:
Now you will find inactive sessions in V$SORT_USAGE table by running below command.
SELECT A2.TABLESPACE, A2.SEGFILE#, A2.SEGBLK#, A2.BLOCKS, A1.SID, A1.SERIAL#, A1.USERNAME, A1.OSUSER, A1.STATUS FROM V$SESSION A1,V$SORT_USAGE A2 WHERE A1.SADDR = A2.SESSION_ADDR;
• Kill Inactive Sessions:
Now you will kill the inactive sessions by using SID and SERIAL# from previous resultset.
ALTER SYSTEM KILL SESSION 'SID_NUMBER, SERIAL#';
• Drop Previous Tablespace:
Finally you will have to drop the previous tablespace.
DROP TABLESPACE PREVIOUS_TEMP_TBS;
相關文章
- GLOBAL TEMPORARY TABLE(轉)
- [virtualbox] temporary failure in name resolutionAI
- Incorrect MEMORY_MAX_TARGET (> Available RAM) Can Lead To Database HangsAIDatabase
- Oracle OCP(48):UNDO TABLESPACEOracle
- MySQL 5.7 InnoDB Tablespace EncryptionMySql
- Tips
- PDF Expert使用教程:如何在Mac上使用PDF Expert編輯PDFMac
- Tablespace表空間刪除
- Linux TipsLinux
- 前端 - tips前端
- Tips: EloquentModel
- NPM TipsNPM
- AutoLayout Tips
- Tips HTMLHTML
- VSCode TipsVSCode
- Matplolib Tips
- NumPy Tips
- idea tipsIdea
- pdf編輯PDF Expert for macMac
- Temporary failure resolving ‘archive.ubuntu.com‘AIHiveUbuntu
- offline tablespace 的幾種方式 (轉)
- SQL__DROPSQL
- JavaScript drop 事件JavaScript事件
- typescript + amd tipsTypeScript
- Python常用TipsPython
- 雜項 tips
- Visual Studio Tips
- 《iOS Tips 一》iOS
- laravel migrations : tipsLaravel
- jQuery tips and tricksjQuery
- PostgreSQL PSQL tipsSQL
- Sybase IQ 錯誤 : Temporary space limit exceededMIT
- ftp_rawlist: Unable to create temporary file.FTP
- temporary、interim、tentative和provisional的區別
- PDF編輯軟體PDF Expert
- pdf編輯工具 PDF Expert中文
- PDF Expert for mac(pdf編輯工具)Mac
- pdf編輯工具:PDF Expert for macMac
- Mac pdf編輯工具:PDF ExpertMac