ALTER SYSTEM KILL SESSION '4033,33518'; --解除被鎖定的會(huì)話信息
公司專注于為企業(yè)提供成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)、微信公眾號開發(fā)、電子商務(wù)商城網(wǎng)站建設(shè)，微信小程序定制開發(fā)，軟件按需求定制網(wǎng)站等一站式互聯(lián)網(wǎng)企業(yè)服務(wù)。憑借多年豐富的經(jīng)驗(yàn)，我們會(huì)仔細(xì)了解各客戶的需求而做出多方面的分析、設(shè)計(jì)、整合，為客戶設(shè)計(jì)出具風(fēng)格及創(chuàng)意性的商業(yè)解決方案，成都創(chuàng)新互聯(lián)公司更提供一系列網(wǎng)站制作和網(wǎng)站推廣的服務(wù)。
select session_id from v$locked_object; --查看哪個(gè)會(huì)話被鎖了
SELECT sid, serial#, username, osuser FROM v$session where sid = 4033; --查看具體被鎖會(huì)話信息
如果發(fā)生了鎖等待，我們可能更想知道是誰鎖了表而引起誰的等待
以下的語句可以查詢到誰鎖了表:
復(fù)制代碼
代碼如下:
SELECT
/*+
rule
*/
s.username,
decode(l.type,'TM','TABLE
LOCK',
'TX','ROW
LOCK',
NULL)
LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM
v$session
s,v$lock
l,dba_objects
o
WHERE
l.sid
=
s.sid
AND
l.id1
=
o.object_id(+)
AND
s.username
is
NOT
NULL
以下的語句可以查詢到誰在等待:
復(fù)制代碼
代碼如下:
SELECT
/*+
rule
*/
lpad('
',decode(l.xidusn
,0,3,0))||l.oracle_username
User_name,
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
解鎖命令:
復(fù)制代碼
代碼如下:
alter
system
kill
session
'sid,serial#'
1).
復(fù)制代碼
代碼如下:
select
LOCK_INFO.OWNER
||
'.'
||
LOCK_INFO.OBJ_NAME
as
"已鎖物件名稱",
--物件名稱(已經(jīng)被鎖住)
LOCK_INFO.SUBOBJ_NAME
as
"已鎖子物件名稱",
--
子物件名稱(已經(jīng)被鎖住)
SESS_INFO.MACHINE
as
"機(jī)器名稱",
--
機(jī)器名稱
LOCK_INFO.SESSION_ID
as
"會(huì)話ID",
--
會(huì)話SESSION_ID
SESS_INFO.SERIAL#
as
"會(huì)話SERIAL#",
--
會(huì)話SERIAL#
SESS_INFO.SPID
as
"OS系統(tǒng)的SPID",
--
OS系統(tǒng)的SPID
(SELECT
INSTANCE_NAME
FROM
V$INSTANCE)
"實(shí)例名SID",
--實(shí)例名SID
LOCK_INFO.ORA_USERNAME
as
"ORACLE用戶",
--
ORACLE系統(tǒng)用戶名稱
LOCK_INFO.OS_USERNAME
as
"OS用戶",
--
作業(yè)系統(tǒng)用戶名稱
LOCK_INFO.PROCESS
as
"進(jìn)程編號",
--
進(jìn)程編號
LOCK_INFO.OBJ_ID
as
"對象ID",
--
對象ID
LOCK_INFO.OBJ_TYPE
as
"對象類型",
--
對象類型
SESS_INFO.LOGON_TIME
as
"登錄時(shí)間",
--
登錄時(shí)間
SESS_INFO.PROGRAM
as
"程式名稱",
--
程式名稱
SESS_INFO.STATUS
as
"會(huì)話狀態(tài)",
--
會(huì)話狀態(tài)
SESS_INFO.LOCKWAIT
as
"等待鎖",
--
等待鎖
SESS_INFO.ACTION
as
"動(dòng)作",
--
動(dòng)作
SESS_INFO.CLIENT_INFO
as
"客戶資訊"
--
客戶資訊
from
(select
obj.OWNER
as
OWNER,
obj.OBJECT_NAME
as
OBJ_NAME,
obj.SUBOBJECT_NAME
as
SUBOBJ_NAME,
obj.OBJECT_ID
as
OBJ_ID,
obj.OBJECT_TYPE
as
OBJ_TYPE,
lock_obj.SESSION_ID
as
SESSION_ID,
lock_obj.ORACLE_USERNAME
as
ORA_USERNAME,
lock_obj.OS_USER_NAME
as
OS_USERNAME,
lock_obj.PROCESS
as
PROCESS
from
(select
*
from
all_objects
where
object_id
in
(select
object_id
from
v$locked_object))
obj,
v$locked_object
lock_obj
where
obj.object_id
=
lock_obj.object_id)
LOCK_INFO,
(select
SID,
SERIAL#,
LOCKWAIT,
STATUS,
(select
spid
from
v$process
where
addr
=
a.paddr)
spid,
PROGRAM,
ACTION,
CLIENT_INFO,
LOGON_TIME,
MACHINE
from
v$session
a)
SESS_INFO
where
LOCK_INFO.SESSION_ID
=
SESS_INFO.SID
order
by
LOCK_INFO.SESSION_ID;
2).
復(fù)制代碼
代碼如下:
select
sql_text
from
v$sqltext
where
address
in
(select
sql_address
from
v$session
where
sid
=
sid)
order
by
piece;
3).
復(fù)制代碼
代碼如下:
ALTER
SYSTEM
KILL
SESSION
'會(huì)話ID,會(huì)話SERIAL#';
4).
kill
-9
OS系統(tǒng)的SPID
原因：默認(rèn)Oracle10g的scott不能登陸。解決： （1）conn sys/sys as sysdba;//以DBA的身份登錄 （2）alter user scott account unlock;// 然后解鎖 （3）conn scott/tiger //彈出一個(gè)修改密碼的對話框，修改一下密碼就可以了具體操作步驟如下： C: sqlplus 請輸入用戶名：sys 輸入口令：sys as sysdba //注意：在口令這里輸入的密碼后面必須要跟上 as sysdba 才可以。 SQL alter user scott account unlock;用戶已更改. SQL commit;提交完成. SQL conn scott/tiger 更改scott口令 新口令：tiger 重新鍵入新口令：tiger 口令已更改 已連接。另一種方法：你打開命令提示符，不要登錄直接輸入下面： sqlplus sys/tiger as sysdba 以dba方式進(jìn)入sys帳戶； alter user scott account unlock; 給scott用戶解鎖；
自己本地的庫的話，重啟數(shù)據(jù)庫就好了。自動(dòng)回滾，重要數(shù)據(jù)庫不可以重啟的話，查到用戶會(huì)話的sid和serial#然后kill
重慶分公司
重慶分公司
