You have alter ssession via a role, roles are not enabled in a trigger (or proc/func generally and never in a view) hence the alter session fails. Sys has alter session directly, not via a role NEVER NEVER NEVER do anything as sys -- sys is special.
. Article Type: General. Product: Aleph Desired Outcome Goal: Kill an unwanted Oracle database session in a clean way.
Procedure: (1) Determine the process ID (PID) of the operating system process which corresponds to the database session. Examples: (1.1) You suspect that a database session is spinning and consuming a significant amount of CPU time. In this case the UNIX command top may report the corresponding operating system process as one of the top consumers of CPU time and provide its PID. (1.2) You know the parent process which initiated the database session you want to kill, e.g.
A script that called SQL.Plus which in turn opened the database session. In this case the parent process has an sqlplus process as its child, and that sqlplus process has a database server process as its child: parent process: aleph 8482 32481 0 16:35 pts/1 00:00:00 csh -f pcustom02 USM50,loanst,09, sqlplus process: aleph 8605 8482 0 16:35 pts/1 00:00:00 sqlplus @/exlibris/aleph/u211/alephe/scripts/sql/patron.sql USM50 loanst 09 database server process: oracle 8606 8605 99 16:35? 00:59:28 oraclealeph21 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) (2) Connect to the Oracle database with privilege sysdba, e.g.
There are a number of reasons to kill non-essential Oracle user processes. In Oracle the alter system kill session command allows you to kill an Oracle session. Also see: The alter system kill session command requires two unique arguments that uniquely identify the Oracle session, the session identifier and serial number.
First you have to identify the session to be killed with alter system kill session. The SID and SERIAL# values of the Oracle session to be killed can then be substituted and the alter system kill session command issued. SQL ALTER SYSTEM KILL SESSION 'sid,serial#'; Sometimes Oracle.exe is not able to kill the session immediately with the alter system kill session command alone. Upon issuing the alter system kill session command, the session will be 'marked for kill'.
It will then be killed as soon as possible. SQL @runningjobprocesses JOB USERNAME SID SERIAL# SPID LOCKWAIT LOGONTIME - - - - - - - 42 JOBUSER 265 3 3231 23-JUN-2004 08:21:25 99 JOBUSER 2 23-JUN-2004 08:55:35 Running jobs that were scheduled using the dbmsscheduler package can be identified using the dbaschedulerrunningjobs view. The following jobsrunning10g.sql script uses this view along with the v$session and v$process views to gather all information needed about the running jobs. SQL @runningjobprocesses10g JOBNAME USERNAME SID SERIAL# SPID LOCK LOGONTIME - - - - - - - TESTFULLJOBDEFINITION SYS 272 125 3199 23-JUN-2004 09:22:12 Regardless of the job scheduling mechanism, the important thing to note is that there are sid, serial#, and spid values associated with the running jobs. The sid and serial# values are necessary in order to kill the session, while the spid value is necessary if the associated operating system process or thread must be killed directly. To kill the session from within Oracle, the sid and serial# values of the relevant session can then be substituted into the following statement. SQL alter system kill session '272,125'; System altered.
This command tells the specified session to rollback any un-committed changes and release any acquired resources before terminating cleanly. In some situations, this cleanup processing may take a considerable amount of time, in which case the session status is set to 'marked for kill' until the process is complete.
Under normal circumstances, no further actions are needed, but occasionally it may be necessary to bypass this cleanup operation to speed up the release of row and object locks held by the session. Killing the operating system process or thread associated with the session releases the session's locks almost immediately, forcing the PMON process to complete the rollback operation.
WARNING: Killing the operating system processes associated with Oracle sessions should be used as a last resort. Killing the wrong process could result in an instance crash and loss of data. In UNIX and Linux environments, the kill command is used to kill specific processes. In order to use this command, the operating system processes ID must be specified. The jobsrunning.sql and jobsrunning10g.sql scripts list the operating system process ID associated with each running job in the spid column. With this information, the operating system process can be killed by issuing the following command.
C: orakill.exe DB10G 3199 These processes can be used to kill jobs, sessions or processes as needed. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their.
Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page. Burleson Consulting The Oracle of Database Support Copyright © 1996 - 2017 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation. Remote Emergency Support provided.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |