insufficient privileges

I try to kill a session with "alter system kill session" command, but I am unable to kill due to lack of privileges

I check out dba_sys_privs table to see if the user I have logged into has the alter system privilege; and i found that
abc does have "alter system" privilege as shown below.

So why am I not able to kill the query using alter system kill session command?

SQL> select * from dba_sys_privs where PRIVILEGE like 'ALTER SESSION';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
abc                              ALTER SESSION                            NO
def                            ALTER SESSION                            NO
ghi                              ALTER SESSION                            NO
jkl                              ALTER SESSION                            NO
mno                              ALTER SESSION                            NO
pqr                            ALTER SESSION                            NO
stu                              ALTER SESSION                            NO
vwx                            ALTER SESSION                            NO
xyz                             ALTER SESSION                            NO



SQL> show user
USER is "abc"


SQL> alter system kill session '35,4911' immediate;
alter system kill session '35,4911' immediate
*
ERROR at line 1:
ORA-01031: insufficient privileges

gram77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

adrian_angCommented:
You are looking at ALTER SESSION, the required one is ALTER SYSTEM
0
sonicefuCommented:
If you want to access V$SESSION, connect as SYS user and grant select privilege on v$session to the user:
GRANT SELECT ON v_$session TO userName;

-------------------------------------------------------

To kill sessions, a user needs to have ALTER SYSTEM privilege

ALTER SYSTEM KILL SESSION 'sid,serial#';
0
sonicefuCommented:
sorry for above comment

connect as DBA user and grant
GRANT alter system TO userName;
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

gram77Author Commented:
Only the following users have ALTER SYSTEM privilege, and I am not among them.

SQL> select * from dba_sys_privs where PRIVILEGE like 'ALTER SYSTEM';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DBA                            ALTER SYSTEM                             YES
DBA_UTILITY                    ALTER SYSTEM                             NO
SYS                            ALTER SYSTEM                             NO

However, I can't even kill the running query using KILL -9 <spid>, as I don't have the admin rights in unix.

 SELECT b.SID "Oracle Sid",
        b.serial# "Oracle Serial",
        a.spid "Unix PID",
        c.sql_text "SQL Text"
 FROM v$process a, v$session b, v$sql c
 WHERE a.addr = b.paddr
   AND b.sql_address = c.address
   AND b.sql_hash_value = c.hash_value;

What is the way out for me to kill the errand query apart from asking the DBA to provide me the ALTER SYSTEM
privilege or assign me administrator role in unix?
0
sonicefuCommented:
connect as any DBA user (sys, system, any other) and grant

GRANT alter system TO userName;

without having "alter system" you cannot kill a session
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schwertnerCommented:
If you want to kill the session without DBA help you have
to stop the application (so you will run in the so called 'death connection').

The DBA should edit the SQLNET.ORA file and to add the entry

SQLNET.EXPIRE_TIME = 10

In 10 minutes the Listener automatically will kill the session.
Meanwhile you can establish a new session.

Another workaround is to ask the DBA to write a procedure to kill a particular session under SYS account and to grant you the privilige to run this procedure. In this case you do not need any rights except to execute that procedure.
0
anandmahajanCommented:
maybe you can crate Unix shell script and run your sql from that shell script.

If you want to kill session you can kill your unix session and sql will get killed as its child process.

But sometimes SQL doesnt get killed immediately in that case u might need to ask dba to kill it manually.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.