首页 > 代码库 > 解锁用户及数据库表,锁死进程

解锁用户及数据库表,锁死进程

 1 一丶解锁用户
 2 --修改时间格式
 3 alter session set nls_date_format = yyyy-MM-dd hh24:mi:ss;
 4 
 5 --查询锁住时间
 6 select username,lock_date  from dba_users where username like ODS;
 7 
 8 --查询用户状态
 9 select username,account_status from dba_users where username IN (od,yxx);
10 
11 --解锁用户
12 alter user yxx account unlock

 

 1 二、解锁数据表
 2 --01.注意你的用户有没有权限问题
 3 SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username,
 4        b.os_user_name
 5   FROM v$process p, v$session a, v$locked_object b, all_objects c
 6  WHERE p.addr = a.paddr AND a.process = b.process
 7        AND c.object_id = b.object_id
 8 ---1、带有表名称查询
 9 select object_name, machine, s.sid, s.serial#
10   from v$locked_object l, dba_objects o, v$session s
11  where l.object_id  =  o.object_id
12    and l.session_id = s.sid;
13 
14 --2、获取出sid,serial 用于解锁的参数
15  
16   select s.username, s.osuser, s.sid, s.serial#, p.spid  from v$session s,v$process p
17 where s.paddr = p.addr    and s.username is not null;
18 --2.1、拼接sql
19    select alter system kill session ,‘‘‘‘||trim(t2.sid)||,||trim(t2.serial#)||‘‘‘;
20 from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time; 
21 
22 ---2:执行sql进行解锁
23 alter system kill session   198,12231;
24 alter system kill session   194,37558;

 

三、解锁锁死系统进程
--查询进程ID
SELECT a.username, c.spid AS os_process_id, c.pid AS oracle_process_id
  FROM v$session a, v$process c
 WHERE c.addr = a.paddr
   and a.sid =2323
   and a.serial# =213;
---kill掉进程
linux 执行:KILL -9 SPID;
window执行:orakill ORACLE_SID spid 

 

解锁用户及数据库表,锁死进程