我想 select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time 应该有记录巴. 再:alter system kill session 'sid,serial#';
在数据库告警文件中出现以下信息: Wed Jan 19 08:20:20 2005 Errors in file /oracle/admin/mr/udump/mr2_ora_18016.trc: ORA-00600: internal error code, arguments: [kjusuc:!lhandle], [0x9FFFFFFFFFFF36A0], [], [], [], [], [], [] Wed Jan 19 08:20:21 2005 Errors in file /oracle/admin/mr/udump/mr2_ora_18016.trc: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [kjusuc:!lhandle], [0x9FFFFFFFFFFF36A0], [], [], [], [], [], [] Wed Jan 19 08:20:21 2005 Trace dumping is performing id=[cdmp_20050119082021] Wed Jan 19 08:24:32 2005 Errors in file /oracle/admin/mr/udump/mr2_ora_11233.trc: ORA-00600: 内部错误代码,参数: [17285], [0x9FFFFFFFBF44A750], [4294967295], [0xC0000000F1AAEBD8], [], [], [], [] ORA-00604: 递归 SQL 层 1 出现错误 ORA-01013: 用户请求取消当前的操作 Wed Jan 19 08:24:32 2005 Trace dumping is performing id=[cdmp_20050119082432] Wed Jan 19 08:24:38 2005 Errors in file /oracle/admin/mr/udump/mr2_ora_11233.trc: ORA-00600: 内部错误代码,参数: [17285], [0x9FFFFFFFBF44A750], [4294967295], [0xC0000000F1AAEBD8], [], [], [], [] Wed Jan 19 08:24:39 2005 Errors in file /oracle/admin/mr/udump/mr2_ora_11233.trc: ORA-07445: 出现异常: 核心转储 [pfrdmp()+65] [SIGSEGV] [Address not mapped to object] [0x2A2A2A2A2A2A2AAA] [] [] ORA-00600: 内部错误代码,参数: [17285], [0x9FFFFFFFBF44A750], [4294967295], [0xC0000000F1AAEBD8], [], [], [], [] Wed Jan 19 08:24:39 2005 Errors in file /oracle/admin/mr/udump/mr2_ora_11233.trc: ORA-07445: 出现异常: 核心转储 [pfrtcs()+64] [SIGSEGV] [Address not mapped to object] [0x2A2A2A2A2A2A2C2E] [] [] ORA-07445: 出现异常: 核心转储 [pfrdmp()+65] [SIGSEGV] [Address not mapped to object] [0x2A2A2A2A2A2A2AAA] [] [] ORA-00600: 内部错误代码,参数: [17285], [0x9FFFFFFFBF44A750], [4294967295], [0xC0000000F1AAEBD8], [], [], [], []在跟踪文件mr2_ora_18016.trc中有如下信息: *** 2005-01-19 08:20:19.988 Single resource deadlock: blocked by granted enqueue, f 1 ----------enqueue------------------------ lock version : 15 Owner node : 1 grant_level : KJUSEREX req_level : KJUSEREX bast_level : KJUSERNL notify_func : 0x0000000000000000 resp : 0xc0000000d975f8b8 procp : 0xc0000000d92ba5c0 pid : 18016 proc version : 1253 oprocp : 0x0000000000000000 opid : 0 gid : 0 xid : 0 0 dd_time : 0.0 secs dd_count : 0 timeout : 0.0 secs On_timer_q? : N On_dd_q? : N lock_state : OPENING Open Options : KJUSERDEADLOCK KJUSERPROCESS_OWNED Convert options : History : 0x435143a5 Msg_Seq : 0x0 res_seq : 0 ----------enqueue------------------------ lock version : 17 Owner node : 1 grant_level : KJUSERNL req_level : KJUSERPR bast_level : KJUSERNL notify_func : 0x9fffffffbf5f1d20 resp : 0xc0000000d975f8b8 procp : 0xc0000000d92ba5c0 pid : 18016 proc version : 1253 oprocp : 0x0000000000000000 opid : 0 gid : 0 xid : 0 0 dd_time : 0.0 secs dd_count : 0 timeout : 0.0 secs On_timer_q? : N On_dd_q? : N lock_state : OPENING Open Options : KJUSERDEADLOCK KJUSERPROCESS_OWNED Convert options : History : 0x7d437d43 Msg_Seq : 0x0 res_seq : 0 估计和这些异常信息有关系,哪位大侠知道怎么回事啊? 是不是和操作系统有关系,操作系统是HP UNIX
可能是记录锁,给你个脚本. 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 然后,杀掉该进程. alter system kill session ‘sid,serial#’
2. 以DBA角色, 查看v$locked_object,v$session
被锁的表记录都可以删除,但不能trunc。怎么回事啊?
try reconnect then drop
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time
应该有记录巴.
再:alter system kill session 'sid,serial#';
Wed Jan 19 08:20:20 2005
Errors in file /oracle/admin/mr/udump/mr2_ora_18016.trc:
ORA-00600: internal error code, arguments: [kjusuc:!lhandle], [0x9FFFFFFFFFFF36A0], [], [], [], [], [], []
Wed Jan 19 08:20:21 2005
Errors in file /oracle/admin/mr/udump/mr2_ora_18016.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kjusuc:!lhandle], [0x9FFFFFFFFFFF36A0], [], [], [], [], [], []
Wed Jan 19 08:20:21 2005
Trace dumping is performing id=[cdmp_20050119082021]
Wed Jan 19 08:24:32 2005
Errors in file /oracle/admin/mr/udump/mr2_ora_11233.trc:
ORA-00600: 内部错误代码,参数: [17285], [0x9FFFFFFFBF44A750], [4294967295], [0xC0000000F1AAEBD8], [], [], [], []
ORA-00604: 递归 SQL 层 1 出现错误
ORA-01013: 用户请求取消当前的操作
Wed Jan 19 08:24:32 2005
Trace dumping is performing id=[cdmp_20050119082432]
Wed Jan 19 08:24:38 2005
Errors in file /oracle/admin/mr/udump/mr2_ora_11233.trc:
ORA-00600: 内部错误代码,参数: [17285], [0x9FFFFFFFBF44A750], [4294967295], [0xC0000000F1AAEBD8], [], [], [], []
Wed Jan 19 08:24:39 2005
Errors in file /oracle/admin/mr/udump/mr2_ora_11233.trc:
ORA-07445: 出现异常: 核心转储 [pfrdmp()+65] [SIGSEGV] [Address not mapped to object] [0x2A2A2A2A2A2A2AAA] [] []
ORA-00600: 内部错误代码,参数: [17285], [0x9FFFFFFFBF44A750], [4294967295], [0xC0000000F1AAEBD8], [], [], [], []
Wed Jan 19 08:24:39 2005
Errors in file /oracle/admin/mr/udump/mr2_ora_11233.trc:
ORA-07445: 出现异常: 核心转储 [pfrtcs()+64] [SIGSEGV] [Address not mapped to object] [0x2A2A2A2A2A2A2C2E] [] []
ORA-07445: 出现异常: 核心转储 [pfrdmp()+65] [SIGSEGV] [Address not mapped to object] [0x2A2A2A2A2A2A2AAA] [] []
ORA-00600: 内部错误代码,参数: [17285], [0x9FFFFFFFBF44A750], [4294967295], [0xC0000000F1AAEBD8], [], [], [], []在跟踪文件mr2_ora_18016.trc中有如下信息:
*** 2005-01-19 08:20:19.988
Single resource deadlock: blocked by granted enqueue, f 1
----------enqueue------------------------
lock version : 15
Owner node : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : 0x0000000000000000
resp : 0xc0000000d975f8b8
procp : 0xc0000000d92ba5c0
pid : 18016
proc version : 1253
oprocp : 0x0000000000000000
opid : 0
gid : 0
xid : 0 0
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : OPENING
Open Options : KJUSERDEADLOCK KJUSERPROCESS_OWNED
Convert options :
History : 0x435143a5
Msg_Seq : 0x0
res_seq : 0
----------enqueue------------------------
lock version : 17
Owner node : 1
grant_level : KJUSERNL
req_level : KJUSERPR
bast_level : KJUSERNL
notify_func : 0x9fffffffbf5f1d20
resp : 0xc0000000d975f8b8
procp : 0xc0000000d92ba5c0
pid : 18016
proc version : 1253
oprocp : 0x0000000000000000
opid : 0
gid : 0
xid : 0 0
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : OPENING
Open Options : KJUSERDEADLOCK KJUSERPROCESS_OWNED
Convert options :
History : 0x7d437d43
Msg_Seq : 0x0
res_seq : 0
估计和这些异常信息有关系,哪位大侠知道怎么回事啊?
是不是和操作系统有关系,操作系统是HP UNIX
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
然后,杀掉该进程.
alter system kill session ‘sid,serial#’