Lock _rac environment killed status after Kill lock table session (resolved)

Original works, from "Deep Blue Blog" blog, dark blue blog:http://blog.csdn.net/huangyanlong/article/details/46876961

RAC Production library kill lock table session killed state processing occurs

Environment:

Operating system: CentOS 6.4 64BIT

Database: Oracle RAC 11.2.0.4 R2 64bit

In a project, when you perform a big data extraction task, the extraction error occurs and the large table needs to be re-extracted. The insert operation is canceled and then the truncate operation is performed.

As below, error, prompt resource is busy, judge should be before the operation is not completely canceled, there is a lock wait.

So, the user who tries to query the lock table is as follows:

Note: Here is the RAC environment, which requires querying the gv$ Class View.

[SQL]View Plain Copy

2. SELECT
4. A.owner,--object-owned users
6. A.object_name,--object name
8. B.XIDUSN,
10. B.xidslot,
12. B.XIDSQN,
14. B.SESSION_ID,--the SESSION of the lock table user
16. B.oracle_username,--The ORACLE user name of the lock table user
18. B.os_user_name,--lock table user's operating system login username
20. B.process,
22. B.locked_mode,
24. C.machine,--the computer name of the lock table user
26. C.status,--Lock table status
28. C.server,
30. C.sid,
32. c.serial#,
34. C.program--The database management tool used by the lock table user
36. From
38. All_objects A,
40. Gv$locked_object B,
42. SYS. Gv_$session C
44. WHERE
46. a.object_id = b.object_id
48. and b.process = c.process
50. --and c.status= ' ACTIVE '
52. orderby1,2

In the query results, lock the session information to the table that needs to be unlocked, as follows:

So, the user who tries to query the lock table is as follows:

Note: Here is the RAC environment, which requires querying the gv$ Class View.

[SQL]View Plain Copy

2. SELECT
4. A.owner,--object-owned users
6. A.object_name,--object name
8. B.XIDUSN,
10. B.xidslot,
12. B.XIDSQN,
14. B.SESSION_ID,--the SESSION of the lock table user
16. B.oracle_username,--The ORACLE user name of the lock table user
18. B.os_user_name,--lock table user's operating system login username
20. B.process,
22. B.locked_mode,
24. C.machine,--the computer name of the lock table user
26. C.status,--Lock table status
28. C.server,
30. C.sid,
32. c.serial#,
34. C.program--The database management tool used by the lock table user
36. From
38. All_objects A,
40. Gv$locked_object B,
42. SYS. Gv_$session C
44. WHERE
46. a.object_id = b.object_id
48. and b.process = c.process--and c.status= ' ACTIVE '
50. ORDER by

In the query results, lock the session information to the table that needs to be unlocked, as follows:

Try to kill the session.

Query which instance the session belongs to, as follows:

SELECT * FROM Gv$session where sid=1228

--See, this is the session of Example 2.

Kill this session.

Example: Alter system kill session ' SID, serial#, @ inst_id '

Alter system kill session ' 1228, 42549, @2 '

Prompt, this session is marked as a kill state.

Indicates that the session has not been completely killed. Let's check it out again.

[SQL]View Plain Copy

2. SELECT
4. A.owner,
6. A.object_name,--object name (table name)
8. B.XIDUSN,
10. B.xidslot,
12. B.XIDSQN,
14. B.SESSION_ID,--the SESSION of the lock table user
16. B.oracle_username,
18. B.os_user_name,--lock table user's operating system login username
20. B.process,
22. B.locked_mode,
24. C.machine,--the computer name of the lock table user (for example: workgroup\hyl)
26. C.status,--Lock table status
28. C.server,
30. C.sid,
32. c.serial#,
34. C.program--The database management tool used by the lock table user (for example: Developer.exe)
36. From
38. All_objects A,
40. Gv$locked_object B,
42. SYS. Gv_$session C
44. WHERE
46. a.object_id = b.object_id
48. and b.process = c.process
50. ORDER by

See the state of the session is killed.

Below try to kill the process under the operating system.

View the addr that corresponds to the SID (Sid 1228) through the gv$session view.

Then, through addr to Gv$process view, view the SPID to session, and finally kill the process under the operating system. Below, you see the SPID for this session,

SELECT * from gv$process where addr = ' addr Information ';

As follows:

To the operating system, look at this process, as follows:

[Email protected] ~]$ Ps-ef |grep 71941

Oracle 36647 36545 0 16:27 pts/1 00:00:00 grep 71941

Oracle 71941 1 Jul09? 09:34:55 oraclexzxt2 (Local=no)

Kill 71941 of this process, as follows:

[Email protected] ~]$ kill-9 71941

[Email protected] ~]$ Ps-ef |grep 71941

Oracle 36687 36545 0 16:28 pts/1 00:00:00 grep 71941

--This message is grep itself

Use the following command to start the query, more clearly displayed, as follows:

[Email protected] ~]$ ps-ef |grep 71941|grep-v grep

No information

The session with SPID 71941 has been killed.

Truncate the table again, successfully.

TRUNCATE TABLE tb_ name;

Summary:

When you kill a lock table session under RAC, you need to be careful to see which instance the session is attributed to and then kill it. If you cannot kill under Sqlplus, try to kill it under the operating system.

The sequential directives are as follows:

Query the lock table session information for active under RAC:

[SQL]View Plain Copy

2. SELECT
4. A.owner,--object-owned users
6. A.object_name,--object name
8. B.XIDUSN,
10. B.xidslot,
12. B.XIDSQN,
14. B.SESSION_ID,--the SESSION of the lock table user
16. B.oracle_username,--The ORACLE user name of the lock table user
18. B.os_user_name,--lock table user's operating system login username
20. B.process,
22. B.locked_mode,
24. C.machine,--the computer name of the lock table user
26. C.status,--Lock table status
28. C.server,
30. C.sid,
32. c.serial#,
34. C.program--The database management tool used by the lock table user
36. From
38. All_objects A,
40. Gv$locked_object B,
42. SYS. Gv_$session C
44. WHERE
46. a.object_id = b.object_id
48. and b.process = c.process
50. and c.status= ' ACTIVE '
52. orderby1,2;

Query the Gv$session view to see which instance the session belongs to:

[SQL]View Plain Copy

1. SELECT * from Gv$session wheresid=1228;

Kill a session in a clustered environment:

[SQL]View Plain Copy

1. Altersystemkillsession ' 1228,42549,@ instance serial number ';

The system process number that the query session corresponds to:

[SQL]View Plain Copy

1. SELECT * from Gv$session where sid= ' session ID ';
3. SELECT * from gv$process where addr= ' addr information ';

Find the SPID in the message.

To the operating system, kill the process (under Oracle user):

[Plain]View Plain Copy

1. $ kill-9 Process number is SPID

Lock _rac environment killed status after Kill lock table session (resolved)