Useful Queries in Oracle to Check the sessions

--check the sessions
SELECT S.BLOCKING_SESSION,
  S.sid,
  S.SERIAL#,
  SCHEMANAME,
  S.OSUSER,
  S.MACHINE,
  S.SECONDS_IN_WAIT,
  s.state,
  s.status
FROM v$session s
WHERE blocking_session IS NOT NULL;

--check the blockings
SELECT username U_NAME,
  owner OBJ_OWNER,
  object_name,
  object_type,
  s.osuser,
  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 gv$locked_object v,
  dba_objects d,
  gv$lock l,
  gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id  = l.id1)
AND v.session_id  = s.sid
ORDER BY username,

  session_id;

--remove sessions
ALTER system kill session '[SID],[SERIAL$]';

Oracle SQL Pivot Usage

Sometimes we might have requirements to get the results through SQL for a kind of upside down way. In Oracle, they have added this feature using a keyword called pivot. Lets take a simple example as follows.

Assume we have a simple table for employees with categories

SELECT * FROM t_employee;

Now if we get the counts group by the category, it will be as follows

SELECT emp.category,COUNT(*) AS total FROM t_employee emp GROUP BY category ;

Now using pivot, you can get the above result as another table kind of as follows

SELECT *

FROM

  (SELECT emp.category,COUNT(*) AS total FROM t_employee emp GROUP BY category

  ) pivot ( SUM(total) FOR category IN ('Dev' AS DEV,'QA' AS QA,'Mgmt' AS MANAGEMENT)) ;