If I execute this statement outside of the stored procedure, it runs fine and the session is killed. Here's an example: ALTER SYSTEM KILL SESSION "34,91" I have granted the following rights to SYSTEM: GRANT SELECT ON v$session TO SYSTEM ĮDIT: I added a dbms_output.putline to print out the stmt variable before executing it. If I change line 17 to stmt := 'ALTER SYSTEM KILL SESSION "' || session_rec.sid || ',' || session_rec.ser || '"' ORA-06512: at "SYSTEM.KILL_USER_SESSION", line 17 If I execute it like this exec kill_user_session('myuser') dbms_output.put_line('Error killing session: ' || stmt) Stmt := 'ALTER SYSTEM KILL SESSION ''' || session_rec.sid || ',' || session_rec.ser || '''' This is my attempt: CREATE OR REPLACE PROCEDURE kill_user_session ( The only parameter the procedure accepts is the user name of the owner of the sessions to kill. I'd like to create a stored procedure that kills Oracle sessions. You can see the TIME, the USERNAME, the EM13c USERNAME in column CLIENT_ID and the SQL statement which was executed in background.This has been asked several times on the web, but none of the answers I found on Google could solve my problem. Login as user SYS and execute this query. On the target database, an audit record was generated. The Named Credential is already filled in.Ĭonfirm the action to kill the selected session immediate – Yes On the target site we go to the Blocking Sessions page On the target database TVD12 user SCOTT has locked some data. The role APPL_ADMIN was granted to my user APPL_BERGER.
Search for the user, in my case it is APPL_BERGERįeel free to test ist against a target which contains the APPL_ADMIN user. The Credential Properties are according to our new created user APPL_ADMIN. Set Credential Name, Authenticated Target Type, Credential Type and set Scope to Global. Setup – Security – Named Credentials – Create We create a named credential and give the admins the permission to use it.
The application adninistrators don’t have to know the password for the created user with the ALTER privileges. The role is now created and be granted to a user.
Sure, we can build a small PL/SQL procedure on every database and give them the executions rights so they can kill a session in their terminal theirself. Until now they called the DBA: “Please do it for me”. And sometimes, they have to kill a hanging Oracle session. In one of my projects, a small team of well known application administrators is having a read-only account in Enterprise Manager 12c to verify the performance, see the user sessions and many more of their subset of databases. This user is now able to change a lot of parameters like memory parameters, NLS settings etc. Granting the ALTER SYSTEM privilege to a Non-DBA has big risks. Basically to execute a ALTER SYSTEM KILL SESSION command you have to be a) a DBA or b) you need the ALTER SYSTEM privilege.