oracle中查询数据库锁和解除数据库锁
下面是常用的一些sql
查询锁
select program, t2.username,t2.sid,t2.serial#,t2.logon_timefrom v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
杀进程
alter system kill session '41,3415';
查到此sid连接用户正在执行的SQL语句:
select sql_text from v$sqltext_with_newlines where (hash_value,address)
in (select sql_hash_value,sql_address from v$session where sid=62) order by address,piece;
查询当前会话数
select count(*) from v$session
ORA-28000: the account is locked.
要对账户解锁，可在数据库管理员账户(sys/system)中使用“alter user”命令的account unlock子句。
如下所示：
alter user username account unlock;
账户解锁后，username 账户再一次被允许连接。
下面是我为了监控数据库情况，根据从网上找的例子，自己改了个小程序，用做告警
查询数据库有超过两分钟的锁进行告警：
package et.wuliu.jk;import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import ecimpl.ECClient;
import et.common.bs.industry.SuperDMO;
import et.common.vo.itf.InterfaceDefVO;
import et.wuliu.jc.SCMTimerExecutor;public class GetLockSession extends SCMTimerExecutor {/*** */private static final long serialVersionUID = -1252330980599103469L;/*** */public GetLockSession(InterfaceDefVO defVO) {super(defVO);// TODO Auto-generated constructor stub}@Overrideprotected void run() {SuperDMO dmo = new SuperDMO("codeseed");try {dmo.executeSQL("truncate table my_session;");dmo.executeSQL("truncate table my_lock;");dmo.executeSQL("truncate table my_sqltext;");dmo.executeSQL("insert into my_session " +" select a.username,a.sid,a.serial#,a.lockwait,a.machine,a.status,"+" a.last_call_et,a.sql_hash_value,a.program from v$session a " +" where nvl(a.username,'NULL')<>'NULL'; ");dmo.executeSQL("insert into my_lock select id1,kaddr,sid,request,type from v$lock;");dmo.executeSQL("insert into my_sqltext select hash_value , sql_text from v$sqltext s, my_session m where s.hash_value=m.sql_hash_value;");//查询锁表个数dmo.execute("select count(*) from my_session a, my_lock b where" +" b.id1 in (select distinct e.id1 from my_session d, my_lock e where d.lockwait = e.kaddr)and a.sid = b.sid and b.request=0 " +" and a.last_call_et >(60*2);");ResultSet rs1 = dmo.getQueryResult();int locktable = 0;if(rs1.next()){locktable = rs1.getInt(1);}int waitCount = 0;SimpleDateFormat sm = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");String d = sm.format(new Date());//正在等待的数量if(locktable >0){dmo.execute("select count(*) from my_session a, my_lock b where a.lockwait = b.kaddr and a.last_call_et>(60*2);");ResultSet rs2 = dmo.getQueryResult();if(rs2.next()){waitCount = rs2.getInt(1);}dmo.executeSQL("update my_messts m set m.ists = (m.ists+1)");}else{//查询临时表是否已经有信息了dmo.execute("select * from my_messts");//提醒周期int txzq = 0;ResultSet rs3 = dmo.getQueryResult();if(rs3.next()){txzq = rs3.getInt(1);}if(txzq >0){SendMsg("提示：NCDB数据库等待锁已全部释放！时间："+d);dmo.executeSQL("update my_messts m set m.ists = 0");}}dmo.execute("select * from my_messts");//提醒周期int txzq = 0;ResultSet rs3 = dmo.getQueryResult();if(rs3.next()){txzq = rs3.getInt(1);}if((txzq % 10 == 0 && txzq >0) || txzq == 1){SendMsg("数据库等待锁告警：锁表个数："+locktable+"；等待锁个数："+waitCount+"；已扫描次数："+(txzq / 10)+"，时间："+d);}} catch (Exception e) {} finally{dmo.close();}}public static void SendMsg(String mess) throws Exception {ECClient ec = new ECClient();ec.loc.setECManagementServiceSOAPEndpointAddress(ec.ecaddress);ec.stub = ec.loc.getECManagementServiceSOAP();ec.sendSMS("13501233302",mess);}}
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！