用dba权限的用户登陆数据库。
select object_name,machine,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;
--(其中24,111分别是上面查询出的sid,serial#
alter system kill session '223,1119';
以上几个步骤即能解决对象被锁定问题。
另外附上锁定一个表的语句:
LOCK TABLE tablename IN EXCLUSIVE MODE;将锁定整个表
更新不要用for update,用下面语句
SELECT T.*,ROWID FROM T_TABLE;
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name and a.tablespace_name='USERNAME';
3、查询是什么SQL引起了锁表的原因,SQL如下:
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid
and s.prev_sql_addr = a.address
order by sid, s.serial#;
---批量解锁
declare
cursor mycur is
select b.sid, b.serial#
from v$locked_object a, v$session b
where a.session_id = b.sid
group by b.sid, b.serial#;
begin
for cur in mycur
loop
execute immediate ('alter system kill session ''' || cur.sid || ',' ||
cur.SERIAL# || ''' ');
end loop;
end;
--查看某张表索引
select index_name, column_name, descend from user_ind_columns where table_name = upper('T_TABLENAME');