Oracle資料庫鎖表查詢

標籤：group   file   資料庫   tar   ext   mod   roc   ora   pfile   

--查看資料庫最大串連數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_timefrom v$session a, v$process b, v$sqlarea cwhere a.paddr = b.addrand a.sql_hash_value = c.hash_valueand 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資料庫鎖表查詢