Oracle数据库锁表查询

select value from v$parameter where name = ‘processes‘; --更改数据库连接数 alter system set processes = 3000 scope = spfile; --查看被锁的表 select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id; --查看当前活跃线程数 select s.status,s.* from v$session s where schemaname=‘数据库用户名‘ and machine=‘机器名‘; select sid,serial#,username,program,machine,status from v$session; select count(*) from v$session; --查看sql执行记录 select * from v$sqltext where sql_text like ‘%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#; --查看oracle正在执行的任务 select a.program,a.osuser, a.sql_exec_start, b.spid, c.sql_text,c.SQL_ID, c.FIRST_LOAD_TIME, c.last_load_time, c.last_active_time from v$session a, v$process b, v$sqlarea c where a.paddr = b.addr and a.sql_hash_value = c.hash_value and a.username is not null; --查出oracle当前的被锁对象 SELECT l.session_id sid, s.serial#, l.locked_mode 锁模式, l.oracle_username 登录用户, l.os_user_name 登录机器用户名, s.machine 机器名, s.terminal 终端用户名, o.object_name 被锁对象名, s.logon_time 登录数据库时间 FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial#; SELECT * FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
--杀掉进程 sid,serial# alter system kill session ‘223,10747‘; --批量杀死进程 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;

 

Oracle数据库锁表查询

标签：group   file   数据库   tar   ext   mod   roc   ora   pfile