解锁,oracle 锁表解锁"/>
oracle锁表全部解锁,oracle 锁表解锁
常用kill表锁命令
1、
--查看被锁的对象
SELECT l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
s.logon_time,
p.SPID
FROM v$locked_object l, all_objects o, v$session s,v$process p
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
AND s.paddr = p.addr
ORDER BY sid, s.serial#;
--方法1:alter system kill session 'sid,serial#'
alter system kill session '26,7013';
--方法2(方法1不灵时采用):在操作系统命令行orakill SID spid
orakill ORCL 4436
alter system kill session '26,7013';
2、查找被锁的表并杀掉
select 'alter system kill session '''||s.sid||','||s.serial#||''';' ,a.inst_id,
a.object_id,a.session_id,a.ORACLE_USERNAME,a.OS_USER_NAME,a.process,a.locked_mode,
b.object_name,s.sql_id,s.EVENT from gv$locked_object a ,dba_objects b,gv$session s where a.object_id=b.object_id
and a.session_id =s.sid(+) and a.inst_id=10 ;
3、当然也可以分开查
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, s.action,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn Desc;
kill锁首先要取得登录Oracle用户的权限,再执行如下数据:
alter system kill session 'sid,SERIAL#'
4、这种方法简单易懂
1、SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME='TABLE1'-->OBJECT_ID=100
2、SELECT * FROM V$LOCKED_OBJECT WHERE OBJECT_ID=100 -->SESSION_ID=200
3、SELECT * FROM V$SESSION WHERE SID=200 -->SERIAL#=300
4、ALTER SYSTEM KILL SESSION '200,300'
SQL> desc v$locked_object;
Name Null? Type
----------------------------------------- -------- ----------------------------
XIDUSN NUMBER
XIDSLOT NUMBER
XIDSQN NUMBER
OBJECT_IDNUMBER
SESSION_ID NUMBER
ORACLE_USERNAME VARCHAR2(30)
OS_USER_NAME VARCHAR2(30)
PROCESS VARCHAR2(24)
LOCKED_MODE NUMBER
SQL> desc v$session;
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)
SID NUMBER
SERIAL#NUMBER
AUDSID NUMBER
PADDR RAW(8)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(16)
LOCKWAIT VARCHAR2(16)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(30)
PROCESS VARCHAR2(24)
MACHINE VARCHAR2(64)
PORT NUMBER
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_EXEC_START DATE
SQL_EXEC_ID NUMBER
PREV_SQL_ADDR RAW(8)
PREV_HASH_VALUE NUMBER
PREV_SQL_ID VARCHAR2(13)
PREV_CHILD_NUMBER NUMBER
PREV_EXEC_START DATE
PREV_EXEC_ID NUMBER
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
MODULE VARCHAR2(48)
MODULE_HASH NUMBER
ACTION VARCHAR2(32)
ACTION_HASH NUMBER
CLIENT_INFO VARCHAR2(64)
FIXED_TABLE_SEQUENCE NUMBER
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
TOP_LEVEL_CALL# NUMBER
LOGON_TIME DATE
LAST_CALL_ET NUMBER
PDML_ENABLED VARCHAR2(3)
FAILOVER_TYPE VARCHAR2(13)
FAILOVER_METHOD VARCHAR2(10)
FAILED_OVER VARCHAR2(3)
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
PDML_STATUS VARCHAR2(8)
PDDL_STATUS VARCHAR2(8)
PQ_STATUS VARCHAR2(8)
CURRENT_QUEUE_DURATION NUMBER
CLIENT_IDENTIFIER VARCHAR2(64)
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_INSTANCE NUMBER
BLOCKING_SESSION NUMBER
FINAL_BLOCKING_SESSION_STATUS VARCHAR2(11)
FINAL_BLOCKING_INSTANCE NUMBER
FINAL_BLOCKING_SESSION NUMBER
SEQ# NUMBER
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
WAIT_TIME_MICRO NUMBER
TIME_REMAINING_MICRO NUMBER
TIME_SINCE_LAST_WAIT_MICRO NUMBER
SERVICE_NAME VARCHAR2(64)
SQL_TRACE VARCHAR2(8)
SQL_TRACE_WAITS VARCHAR2(5)
SQL_TRACE_BINDS VARCHAR2(5)
SQL_TRACE_PLAN_STATS VARCHAR2(10)
SESSION_EDITION_ID NUMBER
CREATOR_ADDR RAW(8)
CREATOR_SERIAL# NUMBER
ECID VARCHAR2(64)
更多推荐
oracle锁表全部解锁,oracle 锁表解锁
发布评论