alter tablespace 等待时间太长

显示全部楼层 · 2015-7-28 14:26:07

我在10.2.0.2上alter tablespace perfstat read only;就死那了
SQL> alter session set tracefile_identifier='LIAOHEDIGITALTRACEFILE';
SQL>alter session set events'10046 trace name context forever,level 12';
SQL>alter tablespace perfstat read only;就停那了

千问 · 2015-7-28 14:26:07

肯定数据库中有未提交的事物
session 1
SQL> insert into test values(23);
1 row created.

session2
SQL> alter tablespace tools read only
2;
一直等待
SQL> select sid,event from v$session_wait;
SID EVENT
---------- ----------------------------------------------------------------
10 enqueue

千问 · 2015-7-28 14:26:07

SQL> commit;
Commit complete.

SQL> alter tablespace tools read only
2;
Tablespace altered.

等待事件消失

千问 · 2015-7-28 14:26:07

there is a active transaction on special object
pls:
select * from v$transaction
where XIDUSN=9 and
XIDSLOT=12 and
XIDSQN=17963;
or you can get locked object information from:
select * from v$locked_object
whereXIDUSN=9 and

XIDSLOT=12 and

XIDSQN=17963;

千问 · 2015-7-28 14:26:07

怎么查出来是哪个等待进程

千问 · 2015-7-28 14:26:07

解决了,把v$locked_object的进程给kill了,就ok了