Expert Tips on Drop Temporary Tablespace Hangs!!
来源:互联网 发布:淘宝助理打印 编辑:程序博客网 时间:2024/05/01 00:35
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_TBSINCLUDING CONTENTS AND DATAFILES;
- Expert Tips on Drop Temporary Tablespace Hangs!!
- Expert Tips on Drop Temporary Tablespace Hangs!!
- Expert Tips on Drop Temporary Tablespace Hangs!!
- temporary tablespace
- 删除ORACLE的临时表空间报错:ORA-12906: cannot drop default temporary tablespace
- temporary tablespace usage
- Oracle Temporary tablespace group
- Create / Alter Oracle TableSpace / Temporary TableSpace, User...
- DROP TABLESPACE相关内容
- drop tablespace ORA-02429
- ORA-14452: attempt to create, alter or drop an index on temporary table already in use
- ORA-14452: attempt to create, alter or drop an index on temporary table already in use
- transport tablespace Tips
- Temporary Segment Handling in Temporary Tablespace (Doc ID 73439.1)
- ORA-10615:Invalid tablespace type for temporary tablespace
- Temporary Tablespace AND the Sort Extent Pool
- ORA-25153: Temporary Tablespace is Empty
- ORA-25153: Temporary Tablespace is Empty
- 【设计模式攻略】创建型模式之Abstract Factory模式
- OnCommand 与OnCmdMsg
- 转载 从linux转到vs的问题(制作FFmpeg SDK for MS Visual Studio)
- 锁存器和自旋锁(Latch&Spinlock)----监视Latch和Spinlock
- jquery的几种异步请求,ajax
- Expert Tips on Drop Temporary Tablespace Hangs!!
- Linux下PPTP的VPN拨号设置(客户端)
- 枚举类型与结构体初步
- Android 文字对齐
- Qt 平台中使GUI保持响应流畅
- Linux 下使用 NMON 分析系统性能
- iOS开发:Objective-C中nil、Nil与NULL的区别
- pat: 1014,部分错误
- 【数据结构】 利用栈求解 括号匹配问题