3

At work, we are sharing oracle credentials among many developers. Often time team runs heavy adhoc queries on the database and it is causing lot of trouble to oracle server. We are working to fix this issue permanently by creating readonly replica instance. Mean time I wanted to find out if I can quickly identify who are all currently in session by looking at windows user id, and/or machine names etc, so that we don't need to maintain too many credentials for database.

Also is it great if I can find out which user/query is creating trouble for oracle server. For example one query caused temp space outage in server because we had not limited the resource for the account.

1

3 Answers 3

6

Please try the below query to see if it's helps:

  select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from 
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;
2
  • 2
    I get ORA-00942: table or view does not exist for dba_objects :( Nov 17, 2016 at 9:42
  • This should also work using all_objects instead of dba_objects but you'll only be able to look at objects that you have access to instead of any object in the database.
    – saritonin
    Apr 30, 2019 at 21:08
0

first you have to connect as 'sys' and execute this query

select
    c.owner,
    c.object_name,
    c.object_type,
    b.sid,
    b.serial#,
    b.status,
    b.osuser,
    b.machine
from 
    v$locked_object a,
    v$session b,
    dba_objects c
where
    b.sid = a.session_id
    and
    a.object_id = c.object_id;
0

Select the session id and kill the process which is holding the lock on schema user.

select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a, v$session b, dba_objects c  where 

b.sid = a.session_id and a.object_id = c.object_id;

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';
INST_ID        SID    SERIAL# 
---------- ---------- ---------- 
    1        130        620
SQL>  alter system kill session '130,620,1'; 
alter system kill session '130,620,1' 
* 
ERROR at line 1: 
ORA-00026: missing or invalid session ID
Now, it works:
SQL>  alter system kill session '130,620,@1';
System altered.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.