Today we received a request to truncate the table having 0 records but truncate taking too long time.
Investigation went in the following way:
1) Identify if there are any locks on the table, and gather the session information holds the lock on the table or issue the truncate command and in another session check for the blocking sessions then we get the session information which is blocking truncate the table.
2) I found a session is active on the table and session is doing nothing by checking event column from v$session and session is two months older.
3) Since session was doing no productive work and got confirmation to kill the session. I have killed the session and got replied as session marked for kill and status of the session is killed but still couldn't truncate the table.
4) Waited for day, Still the same issue persists.
5) Identified SPID of the session and found still the os process exists in the server for the respective session.
6) Killed the os process and able to truncate the table.
Investigation went in the following way:
1) Identify if there are any locks on the table, and gather the session information holds the lock on the table or issue the truncate command and in another session check for the blocking sessions then we get the session information which is blocking truncate the table.
2) I found a session is active on the table and session is doing nothing by checking event column from v$session and session is two months older.
3) Since session was doing no productive work and got confirmation to kill the session. I have killed the session and got replied as session marked for kill and status of the session is killed but still couldn't truncate the table.
4) Waited for day, Still the same issue persists.
5) Identified SPID of the session and found still the os process exists in the server for the respective session.
6) Killed the os process and able to truncate the table.
No comments:
Post a Comment