You may also want to look in v$sql_monitor. This will be default log all SQL that takes longer than 5 seconds. It is also visible on the SQL Monitoring page in Enterprise Manager.
I am looking for the functionality which exists in 11g EM, where blockers or lockers are displayed, which you can also kill them via the EM page.
Is this not available it 12c EM anymore?
I need to check in 12c.
In EM Express, you can see blocking session details in the Performance Hub on the Activity Tab.
Select Wait Class->Resource Consumption->Blocking Session
Click on the Session link:
Then Activity to see what the SQL was that was waiting
For sure, 12c EM Express does not do as good of a job of showing the blocker/waiter relationship
You can see the blocker with the following SQL:
SELECT DISTINCT dbw.holding_session, fu.user_name apps_user, fu.email_address "Email", vs.username, vs.process "Client Process",
vs.sid "DB SID", vs.serial# "DB Serial #", vs.MODULE "Module", vs.action "Action", vs.logon_time "Login Time", vp.spid "Server Process"
FROM v$session vs, v$process vp, fnd_logins fl, fnd_user fu, dba_waiters dbw
WHERE vp.addr(+) = vs.paddr
AND fl.spid(+) = vs.process
AND fl.end_time(+) IS NULL
AND fu.user_id(+) = fl.user_id
AND vs.sid = dbw.holding_session;
Thanks Michael, Prasant, and ALL,
So now, I need to kill them manually using sqlplus or sqldev?