`

Procedure to kill a session

阅读更多
On the Oracle-L mailing list a user requested a procedure to allow a developer to kill his own process. This contributed quite a few comments which I will list below and I posted a method that I use when I have the same requirement.
To create the stored procedure logon as a sysdba through sqlplus and explicitly grant the required rights to system and then create the procedure.

grant alter system to system;
grant select on sys.v_$session to system;

create or replace procedure system.killsession (killsid IN VARCHAR2)
as
killserial   varchar2(20);

begin
killserial:='none';
select serial#
into killserial
from sys.v_$session
where sid = killsid
and username = 'WES';

execute immediate 'alter system kill session ''' || to_char (killsid) || ', ' || to_char (killserial) || '''' ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('The SID ' || killsid || ' does not exist or cannot be killed');

end ;


Grant execute on the procedure to the user
For example, userxxx needs access to kill sessions so you would type the following:-
grant execute on system.killsession to userxxx;


The user needs to know the sid of the session being killed. For example, if a user needs to kill a session with a SID of 140 they would type:-
set serveroutput on
exec system.killsession ('140');



If they get the following error then there is no SID with a username of WES currently connected:-
The SID 140 does not exist or cannot be killed
  The various thoughts on allowing such a procedure centred on two strands, security and practical considerations

Security issues
• Why do they need to kill jobs, what is causing the issue
What will developers want next
Sarbanes-Oxley considerations
Practical
• How do you know the developer will kill one of his own processes and not a co-worker (think apps or sysadm job)
Can you use resource profiles to limit CPU usage
Use a standby to allow unrestricted resource usage
My view is that in certain development environments, for certain users it is reasonable to allow them to kill a process that is running rogue and as such I am happy to provide such a facility. This would not be allowed either unrestricted or in any sort of controlled environment such as production and pre-production.
分享到:
评论

相关推荐

    PLSQL.Developer(X64) v12.0.1.1814 主程序+ v11中文包+keygen

    You can now kill a session with the "immediate" option. Connection Enhancements A "Set current schema" item has been added to the connection popup menu. It will set the current schema for all windows...

    PLSQL.Developer(X32) v12.0.1.1814主程序+ v11中文包+keygen

    You can now kill a session with the "immediate" option. Connection Enhancements A "Set current schema" item has been added to the connection popup menu. It will set the current schema for all windows...

    plsqldev12.0.4.1826x32主程序+ v12中文包+keygen

    You can now kill a session with the "immediate" option. Connection Enhancements A "Set current schema" item has been added to the connection popup menu. It will set the current schema for all windows ...

    plsqldev12.0.4.1826x64主程序+ v12中文包+keygen

    You can now kill a session with the "immediate" option. Connection Enhancements A "Set current schema" item has been added to the connection popup menu. It will set the current schema for all windows ...

    清除Oracle中长时间持锁的session

    - **NAMESPACE**: 库缓存中对象的命名空间,例如`TABLE`、`PROCEDURE`等。 - **TYPE**: 对象的类型,如`INDEX`、`TABLE`等。 - **SHARABLE_MEM**: 对象在共享池中消耗的共享内存数量。 - **LOADS**: 对象被加载的...

    SqlServer查询和Kill进程死锁的语句

    select request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT' ``` 这个查询返回当前正在等待对象锁的会话ID(SPID)以及它们...

    Oracle常用问题1000问

    alter system kill session ,把那个session给杀掉,不过你要先查出她的session id or 把该过程重新改个名字就可以了。 57. SQL Reference是个什么东西? 是一本sql的使用手册,包括语法、函数等等,oracle官方...

    常用SQL如何查看正在执行的语句

    - 使用`ALTER SYSTEM KILL SESSION`命令可以强制结束指定的会话。 - 需要提供会话的SID和序列号(serial#)。 ##### 2.2 杀死操作系统进程 ```sql SELECT a.username, c.spid AS os_process_id, c.pid AS oracle_...

    查看数据库锁和解锁方法

    2. **终止会话**:使用`ALTER SYSTEM KILL SESSION`命令来终止锁定的会话。例如: ```sql ALTER SYSTEM KILL SESSION 'sid, serial#'; ``` 这里需要注意的是,`sid`和`serial#`应替换为实际查询到的值。 #### ...

    最全的oracle常用命令大全.txt

    SQL> alter system kill session 'sid,serial#'; 如果这命令不行,找它UNIX的进程数 SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr; 说明:21是某个连接的sid...

    99乘法表java源码-OracleDBUtils:适用于Oracle数据库的有用PL/SQL实用程序

    99乘法表java源码OracleDBUtils ...killSession( tSid in number ); 此过程将会话 SID(来自视图 V$SESSION)作为参数并通过调用 orakill.exe(在 Windows 服务器上)或 kill -9(在 UNIX 机器上)来终止相应的会话。

    oracle基础知识考试题答案.pdf

    7. **解决死锁问题**:首先,通过查询`v$locked_object`和`v$session`视图找到死锁的会话信息,然后使用`ALTER SYSTEM KILL SESSION 'sid,serial#';`终止会话。 8. **连接字符**:在Oracle中,连接两个字符串通常...

    oracle 常用指令

    - `ALTER SYSTEM KILL SESSION`:强制结束会话。 - `DBA_HIST_SYSMETRIC_SUMMARY` 视图可用于性能历史分析。 9. **权限和角色管理**: - `CREATE ROLE` 和 `DROP ROLE`:创建和删除角色。 - `GRANT ROLE TO` 和...

    oracle管理常用sql脚本

    - `ALTER SYSTEM KILL SESSION`:结束特定会话以解除死锁。 8. **分区表管理**: - `CREATE TABLE ... PARTITION BY`:创建分区表,提高大规模数据的查询和维护效率。 9. **存储过程和函数**: - `CREATE ...

    数据库操作处理笔记

    alter system kill session 'sid, serial#'; ``` **2. 用户密码修改和用户账号加锁、解锁:** - 修改密码: ```sql alter user username identified by new_password; ``` - 锁定用户: ```sql alter user ...

    Oracle数据库常用命令整理(实用方法)

    要为用户授予调试任何过程的权限,使用`GRANT debug any procedure, debug connect session TO USER XXX;`。 8. **查询用户信息** `SELECT username, password FROM dba_users;`可查看所有用户及其密码(注意:在...

    ocp原厂培训笔记(第三天)

    阻塞通常由于长时间未提交的事务或高优先级锁导致,可以通过`KILL SESSION`解除。死锁则是两个或更多事务相互等待对方释放资源的情况,Oracle会检测并自动解决死锁,可能将其中一个会话回滚。 关于Undo管理,Oracle...

    重新编译PLSQL中的无效对象或者指定的对象 的方法

    当对象被其他会话锁定时,可以使用`SELECT * FROM v$locked_object`查询锁定的对象,并通过`ALTER SYSTEM KILL SESSION 'session_id,serial#'`终止相关会话,但需谨慎操作,避免数据丢失。 4. **查看对象状态**: ...

Global site tag (gtag.js) - Google Analytics