Oracle解锁表、包、用户、杀会话、停job

---

一、创建包tzq_server_pkg

sys用户以sysdba身份登录Oracle数据库，创建包 tzq_server_pkg ，上代码：

CREATE OR REPLACE PACKAGE sys.tzq_server_pkg ISPROCEDURE unlock_table(table_owner IN VARCHAR2, table_name IN VARCHAR2);PROCEDURE unlock_package(package_owner IN VARCHAR2,package_name  IN VARCHAR2);PROCEDURE unlock_user(username IN VARCHAR2);PROCEDURE stop_job(job_id IN NUMBER);PROCEDURE kill_session(se_sid IN NUMBER, se_serail# IN NUMBER);PROCEDURE grant_pris(username IN VARCHAR2);
END tzq_server_pkg;
/CREATE OR REPLACE PACKAGE body SYS.tzq_server_pkg ISPROCEDURE unlock_table(table_owner IN VARCHAR2, table_name IN VARCHAR2) ISCURSOR c1 ISSELECT DISTINCT '''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || '''' AS si_idFROM gv$locked_object l,dba_objects      o,gv$session       sWHERE l.object_id = o.object_idAND l.session_id = s.sidAND l.inst_id = s.inst_idAND o.owner = upper(table_owner)AND o.object_name = upper(table_name);c1_rec c1%ROWTYPE;v_sql  VARCHAR2(2000);BEGINFOR c1_rec IN c1 LOOPv_sql := 'alter system kill session ' || c1_rec.si_id || ' immediate';dbms_output.put_line(v_sql);BEGINEXECUTE IMMEDIATE v_sql;EXCEPTIONWHEN OTHERS THENdbms_output.put_line(SQLERRM);END;END LOOP;END unlock_table;PROCEDURE unlock_package(package_owner IN VARCHAR2,package_name  IN VARCHAR2) ISCURSOR c1 ISSELECT DISTINCT '''' || a.sid || ',' || a.serial# || ',@' || a.inst_id || '''' AS si_idFROM gv$session a,gv$access  bWHERE b.object = upper(package_name)AND b.owner = upper(package_owner)AND a.sid = b.sidAND a.inst_id = b.inst_id;c1_rec c1%ROWTYPE;v_sql  VARCHAR2(2000);BEGINFOR c1_rec IN c1 LOOPv_sql := 'alter system kill session ' || c1_rec.si_id || ' immediate';dbms_output.put_line(v_sql);BEGINEXECUTE IMMEDIATE v_sql;EXCEPTIONWHEN OTHERS THENdbms_output.put_line(SQLERRM);NULL;END;END LOOP;END unlock_package;PROCEDURE unlock_user(username IN VARCHAR2) ISus_name VARCHAR2(200) := username;v_sql   VARCHAR2(2000);BEGINv_sql := 'alter user ' || us_name || ' account unlock';BEGINEXECUTE IMMEDIATE v_sql;dbms_output.put_line(us_name || '''s account is unlock');EXCEPTIONWHEN OTHERS THENdbms_output.put_line(SQLERRM);END;END unlock_user;PROCEDURE stop_job(job_id IN NUMBER) ISCURSOR c1 ISSELECT DISTINCT '''' || a.sid || ',' || a.serial# || ',@' || a.inst_id || '''' AS si_idFROM gv$session a,(SELECT v.sid,v.id2     job,v.inst_id inst_idFROM sys.job$ j,gv$lock  vWHERE v.type = 'JQ'AND j.job(+) = v.id2) b,gv$instance cWHERE a.inst_id = b.inst_idAND a.sid = b.sidAND a.inst_id = c.inst_idAND c.inst_id = b.inst_idAND b.job = job_id;c1_rec c1%ROWTYPE;v_sql  VARCHAR2(2000);BEGINFOR c1_rec IN c1 LOOPv_sql := 'alter system kill session ' || c1_rec.si_id || ' immediate';dbms_output.put_line(v_sql);BEGINEXECUTE IMMEDIATE v_sql;EXCEPTIONWHEN OTHERS THENdbms_output.put_line(SQLERRM);NULL;END;END LOOP;END stop_job;PROCEDURE kill_session(se_sid IN NUMBER, se_serail# IN NUMBER) ISp_sid           NUMBER := se_sid;p_serail        NUMBER := se_serail#;is_back_process NUMBER := 0;CURSOR c1 ISSELECT DISTINCT '''' || a.sid || ',' || a.serial# || ',@' || a.inst_id || '''' AS si_idFROM gv$session aWHERE a.sid = p_sidAND a.serial# = p_serail;c1_rec c1%ROWTYPE;v_sql  VARCHAR2(2000);BEGINIF se_sid IS NULL OR se_serail# IS NULL THENdbms_output.put_line('sid is null or serail# is null');RETURN;END IF;BEGINSELECT 1INTO is_back_processFROM gv$sessionWHERE sid = se_sidAND serial# = se_serail#AND TYPE = 'BACKGROUND';EXCEPTIONWHEN OTHERS THENis_back_process := 0;END;IF is_back_process = 1 THENRETURN;END IF;FOR c1_rec IN c1 LOOPv_sql := 'alter system kill session ' || c1_rec.si_id || ' immediate';dbms_output.put_line(v_sql);BEGINEXECUTE IMMEDIATE v_sql;EXCEPTIONWHEN OTHERS THENdbms_output.put_line(SQLERRM);NULL;END;END LOOP;END kill_session;PROCEDURE grant_pris(username IN VARCHAR2) ISus_name VARCHAR2(200) := username;v_sql   VARCHAR2(2000);v_sql2  VARCHAR2(2000);v_sql3  VARCHAR2(2000);v_sql4  VARCHAR2(2000);v_sql5  VARCHAR2(2000);v_sql6  VARCHAR2(2000);v_sql7  VARCHAR2(2000);BEGINv_sql  := 'grant create synonym,create table,create type,create sequence,create view ,create materialized view,create job,create database link,connect,resource,create procedure ,debug any procedure, debug connect session to ' ||us_name;v_sql2 := 'grant select on gv_$locked_object to ' || us_name;v_sql3 := 'grant select on dba_objects to ' || us_name;v_sql4 := 'grant select on gv_$session to ' || us_name;v_sql5 := 'grant select on gv_$process to ' || us_name;v_sql6 := 'grant select on gv_$sql to ' || us_name;v_sql7 := 'grant select on gv_$access  to ' || us_name;BEGINEXECUTE IMMEDIATE v_sql;EXECUTE IMMEDIATE v_sql2;EXECUTE IMMEDIATE v_sql3;EXECUTE IMMEDIATE v_sql4;EXECUTE IMMEDIATE v_sql5;EXECUTE IMMEDIATE v_sql6;EXECUTE IMMEDIATE v_sql7;dbms_output.put_line('grant success!');EXCEPTIONWHEN OTHERS THENdbms_output.put_line(SQLERRM);END;END grant_pris;END tzq_server_pkg;
/

二、授权给需要使用的用户log

sys用户以sysdba身份登录Oracle数据库，给需要使用该包（sys.tzq_server_pkg）的用户授予 execute 的权限，执行下面命令授权：

grant execute on sys.tzq_server_pkg to log;

三、解锁表：执行存过unlock_table(schema_name, table_name)

以上面被授权的log用户，打开命令行窗口，执行下列SQL：

set serveroutput on
execute sys.tzq_server_pkg.unlock_table('LOG','tzq_log_t');

四、解锁包：执行存过unlock_package(schema_name, pkg_name)

以上面被授权的log用户，打开命令行窗口，执行下列SQL：

set serveroutput on
execute sys.tzq_server_pkg.unlock_package('LOG','tzq_log_pkg');

五、解锁用户：执行存过unlock_user(username)

以上面被授权的log用户，打开命令行窗口，执行下列SQL：

set serveroutput on
execute sys.tzq_server_pkg.unlock_user('LOG');

六、停止job任务：执行存过stop_job(job_id)

以上面被授权的log用户，打开命令行窗口，执行下列SQL：

set serveroutput on
execute sys.tzq_server_pkg.stop_job(6);

七、杀session会话：执行存过kill_session(se_sid, se_serail#)

7.1、查询需要kill的session的SID及serial#

执行下列SQL：

SELECT * FROM gv$session;

找到你需要kill的那个session会话，拿到SID及serial#：159, 3729

7.2、执行存过kill_session(se_sid, se_serail#)

执行存过kill_session()，kill掉上面的那个session会话。在命令行执行下面的SQL：

set serveroutput on
execute sys.tzq_server_pkg.kill_session(159, 3729);

八、给新建的用户授权：执行存过 grant_pris(username)

以上面被授权的log用户，打开命令行窗口，执行下列SQL：

set serveroutput on
execute sys.tzq_server_pkg.grant_pris('log');