when executing this function, it shows one session additionaly from each nodes.įollowing is the code for viewing sessionsįunction List_My_Session return Kill_Table PIPELINED isįor c1 in ( select inst_id,sid,serial#,username,osuser,sql_id from gv_$session Suppose, i have only one session for the user " AELYAS " From No issues.īut view sessions function is not showing the results properly. Kill session procedure looks working fine.
through which they can view all of their sessions using one function and they can kill their session using another procedure. I am writing a package for the developers. Util%ORA11GR2> grant execute on dev_flush_cache to scott Util%ORA11GR2> CREATE OR REPLACE PROCEDURE dev_flush_cache AS Ops$tkyte%ORA11GR2> grant create session, create procedure, alter system to util Ops$tkyte%ORA11GR2> create user util identified by util do not use thees accounts - use your OWN accounts. Thirdly - take the above and change SYS to SYSTEM, same applies. sys is ours, sys is special, sys is magic, things work differently as sys than for other users. I strongly encourage you to STOP THIS, do not do this, it is misleading, time wasting, not useful. Further - flushing the caches presents you with a situation you would NEVER SEE in real life. Search this site for "secondary sga" - to see what I'm talking about. That means, the file system cache is already caching the stuff and just because we say we did a physical IO - it doesn't mean we did. You are probably using a file system - a buffered file system. ORA-06512: at "SYSTEM.DEV_FLUSH_CACHE", line 6ĭid you know that flushing the caches for "performance testing" is a really horribly bad, misleading, time wasting, not good for anything approach? SQL> grant execute on v_flush_cache to test SQL> create user test identified by test SQL> CREATE OR REPLACE PROCEDURE v_flush_cache ASĤ l_ddl := 'ALTER SYSTEM FLUSH SHARED_POOL' ħ l_ddl := 'ALTER SYSTEM FLUSH BUFFER_CACHE' This is for developer to flush memory cache when tuning their SQLs. This approach doesn't seem to work in 11.2.0.2 and I'm trying to wrap ALTER SYSTEM FLUSH SHARED_POOL and ALTER SYSTEM FLUSH BUFFER_CACHE in a procedure created in SYSTEM and grant the execute privilege to a TEST user. ORA-06512: at "FXBLOTTER.KILL_SESSION", line 9 SQL> exec testdbauser.kill_session(1016,808) īEGIN testdbauser.kill_session(1016,808) END Grant execute on kill_session to testuser OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY I granted the owner the following grants. I notices this thread was created in Oracle 8. That view will show them their sessions only. Select * from v$session where username = USER Īnd grant them select on that view. You would probably want to "grant select on v_$session" when connected as SYS to these people as well so they can 'see' the v$session dynamic performance view to get their sid/serial# pairs.
It would allow them to kill any session they own (running under their username). You would then grant execute on this procedure to anyone you want. To see why the caveat on granting directly to them is needed, please see:
O ALTER SYSTEM granted directly to them - not via a role. This grant must be directly to them, not via a role. O SELECT on v_$session granted to them by SYS. The owner of this procedure needs to have It could look something like:Ĭreate or replace procedure kill_session( p_sid in varchar2,Ĭursor_name pls_integer default dbms_sql.open_cursor In 7.3, we would use the dbms_sql package to accomplish this. We must use dynamic sql to accomplish this feat. In addition to the syntax described above, also add the IMMEDIATE clause.Where sid and serial# are obtained from the v$session dynamic performance table.Īlter system is considered DDL and hence cannot be executed directly from PLSQL. It will then be killed as soon as possible. In these cases the session will have a status of "marked for kill". In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. It merely asks the session to kill itself. SQL> ALTER SYSTEM KILL SESSION KILL SESSION command doesn't actually kill the session. This allows you to kill a session on different RAC node. In a RAC environment, make sure to optionally specify the INST_ID, shown when querying the GV$SESSION view. SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' The basic syntax for killing a session is shown below.
ORACLE KILL SESSION HOW TO
Link that properly explains how to kill Oracle: JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id Identify the offending session using the V$SESSION and V$PROCESS views as follows: SET LINESIZE 100 To kill the connection from the client side, use TCPVIEW. This guide explains how to kill Oracle sessions.
0 kommentar(er)
