Some helpful Sqls for Oracle

1. For finding out the current running sql for a particular user

SELECT a.sql_text
 FROM v$session s,
 v$sqlarea a
 WHERE s.user    = 'USER'
AND s.status        ='ACTIVE'
AND s.sql_hash_value=a.hash_value
AND s.sql_address   =a.address;

2. To find locked objects in Oracle

3. To see all the seesion for a user

   select * from v$session s where s.username = 'PROQ';

4. To see locked objects and the session

select     oracle_username || ' (' || s.osuser || ')' username
   ,  s.sid || ',' || s.serial# sess_id
   ,  owner || '.' || object_name object
   ,  object_type
   ,  decode( l.block
      ,       0, 'Not Blocking'
      ,       1, 'Blocking'
      ,       2, 'Global') status
   ,  decode(v.locked_mode
     ,       0, 'None'
     ,       1, 'Null'
     ,       2, 'Row-S (SS)'
     ,       3, 'Row-X (SX)'
     ,       4, 'Share'
     ,       5, 'S/Row-X (SSX)'
     ,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
  from       v$locked_object v
  ,  dba_objects d
  ,  v$lock l
  ,  v$session s
  where      v.object_id = d.object_id
  and        v.object_id = l.id1
  and        v.session_id = s.sid
  order by oracle_username
  ,  session_id

And then kill the session with this

alter system kill session '94,2168';

5. Tables by user

select owner,count(*) from all_all_tables group by owner; 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s