高分请教:怎么查看当前表空间在作什么操作? 关联v$session和v$sqlarea可以看到session执行的sql不能insert,总有错误提示吧,贴出来 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 关联v$session和v$sqlarea可以看到session执行的sql查询出来的不一定就是当前该进程正在执行SQL。临时表不能insert,没有错误,原因就是该临时表会死锁;那怕是新建的临时表,进行insert操作也会死锁。 --查看锁 select sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid, sys.v_$session.serial#, decode(v$lock.type, 'MR', 'Media Recovery', 'RT','Redo Thread', 'UN','User Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalida-tion', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table', 'Unknown') LockType, rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name, decode(lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', 'Unknown') LockMode, decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', 'Unknown') RequestMode, ctime, block b from v$lock, all_objects, sys.v_$session where v$Lock.sid > 6 and sys.v_$session.sid = v$lock.sid and v$lock.id1 = all_objects.object_id;--查看死锁SELECT SUBSTR(s1.username,1,12) "WAITING USER" , SUBSTR(s1.osuser,1,8) "OS User" , SUBSTR(TO_CHAR(w.session_id),1,5) "Sid" , p1.spid "PID" , SUBSTR(s2.username,1,12) "HOLDING User" , SUBSTR(s2.osuser,1,8) "OS User" , SUBSTR(TO_CHAR(h.session_id),1,5) "Sid" , p2.spid "PID" FROM sys.v_$process p1 , sys.v_$process p2 , sys.v_$session s1 , sys.v_$session s2 , dba_locks w , dba_locks h WHERE h.mode_held != 'None' AND h.mode_held != 'Null' AND w.mode_requested != 'None' AND w.lock_type (+) = h.lock_type AND w.lock_id1 (+) = h.lock_id1 AND w.lock_id2 (+) = h.lock_id2 AND w.session_id = s1.sid (+) AND h.session_id = s2.sid (+) AND s1.paddr = p1.addr (+) AND s2.paddr = p2.addr (+) 捕捉运行很久的sqlcolumn username format a12 column opname format a16 column progress format a8 select username,sid,opname, round(sofar*100 / totalwork,0) || '%' as progress, time_remaining,sql_text from v$session_longops , v$sql where time_remaining <> 0 and sql_address = address and sql_hash_value = hash_value 查看还没提交的事务select * from v$locked_object;select * from v$transaction;查找object为哪些进程所用select p.spid,s.sid,s.serial# serial_num,s.username user_name, a.type object_type,s.osuser os_user_name, a.owner, a.object object_name, decode(sign(48 - command), 1, to_char(command), 'action code #' || to_char(command) ) action, p.program oracle_process, s.terminal terminal, s.program program, s.status session_status from v$session s, v$access a, v$process p where s.paddr = p.addr ands.type = 'user' and a.sid = s.sid anda.object='subscriber_attr'order by s.username, s.osuser 创建的函数带有编译错误,急~~~ 字段中部分字符进行替换(急!) 关于一个ODBC问题 oracle中报缺失表达式的错误,写错了吗,希望各位指点下 高分求助oralce 10g正则表达式的问题~ 达人帮忙啊 oracle 如何取前几行 救急!无归档日志,如何恢复数据? oracle分页查询慢 如何重定位控制文件 sql报错啦~求大神瞅瞅。 有没有比较好用的ORACLE的参考书 请问这样SQL语句怎么写?急!在线等!!!
select sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid,
sys.v_$session.serial#,
decode(v$lock.type,
'MR', 'Media Recovery',
'RT','Redo Thread',
'UN','User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType,
rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,
decode(lmode, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') LockMode,
decode(request, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') RequestMode,
ctime, block b
from v$lock, all_objects, sys.v_$session
where v$Lock.sid > 6
and sys.v_$session.sid = v$lock.sid
and v$lock.id1 = all_objects.object_id;--查看死锁
SELECT
SUBSTR(s1.username,1,12) "WAITING USER"
, SUBSTR(s1.osuser,1,8) "OS User"
, SUBSTR(TO_CHAR(w.session_id),1,5) "Sid"
, p1.spid "PID"
, SUBSTR(s2.username,1,12) "HOLDING User"
, SUBSTR(s2.osuser,1,8) "OS User"
, SUBSTR(TO_CHAR(h.session_id),1,5) "Sid"
, p2.spid "PID"
FROM
sys.v_$process p1
, sys.v_$process p2
, sys.v_$session s1
, sys.v_$session s2
, dba_locks w
, dba_locks h
WHERE
h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND w.lock_type (+) = h.lock_type
AND w.lock_id1 (+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
column opname format a16
column progress format a8 select username,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
from v$session_longops , v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value
查看还没提交的事务select * from v$locked_object;
select * from v$transaction;查找object为哪些进程所用select p.spid,s.sid,s.serial# serial_num,s.username user_name, a.type object_type,s.osuser os_user_name, a.owner, a.object object_name, decode(sign(48 - command), 1, to_char(command), 'action code #' || to_char(command) ) action, p.program oracle_process, s.terminal terminal, s.program program, s.status session_status
from v$session s, v$access a, v$process p
where s.paddr = p.addr and
s.type = 'user' and
a.sid = s.sid and
a.object='subscriber_attr'
order by s.username, s.osuser