How to find out who is active and locking the oracle database?

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.

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;

StackExchange Database Administrators Q#86579, answer score: 6