[Solved] How to find long running / resource consuming SQL queries in Oracle without OGC?

You can use these queries and commands to help in DBs without grid control:

--identifies long-running sessions, if any, and their SIDs
SELECT sid, to_char(start_time,'hh24:mi:ss') stime, 
message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1;

--gives information about currently running SQL
select S.USERNAME, s.sid, s.serial#, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece;

--kill a specific session (in this example, the above query gave SID:140 and SERIAL#:60729
alter system kill session '104,60729';

I've started using a variation of Jonathan's original which I find a little easier to read:

select TO_CHAR(sql_exec_start, 'YYYYMMDDHH24MISS'), db_user, os_user, session_id, sql_id, machine,
  nvl2(delta, lpad(extract(DAY FROM delta), 2, ' ') || 'd ' || lpad(extract(HOUR FROM delta), 2, ' ') || 'h ' || lpad(extract(MINUTE FROM delta), 2, ' ') || 'm ' || lpad(extract(SECOND FROM delta), 2, ' ') || 's', '             ') elapsed, sql_text
from (
  select s.sql_exec_start, S.USERNAME db_user, s.osuser os_user, s.sid || ',' || s.serial# session_id, t.sql_id,
  TO_TIMESTAMP(TO_CHAR(systimestamp, 'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') -
    TO_TIMESTAMP(TO_CHAR(s.sql_exec_start, 'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') delta,
  s.machine,
  substr(listagg(sql_text, '') within group (order by t.piece), 1, 4000) as sql_text
  from v$sqltext_with_newlines t,V$SESSION s
  where t.address =s.sql_address
  and t.hash_value = s.sql_hash_value
  and s.status = 'ACTIVE'
  and s.username <> 'SYSTEM' /*ignoreme*/
  and t.piece < 62
  -- and s.osuser = 'p10pltd'
  group by S.USERNAME, s.osuser, s.sid || ',' || s.serial#, t.sql_id, s.sql_exec_start, s.machine
  order by s.sid || ',' || s.serial#
) where sql_text not like '%ignoreme%';

alter system kill session '500,55981';

Here is a newer version of this query which improves on the previous one.

It includes "client identifier" information that is used by NEO for auto-killing long runners in NEO 3.6+ and also includes the full sql text without truncation.

with sessions1 as (
  select S.USERNAME db_user, s.osuser os_user, s.sid, s.serial#, t.sql_id,
    TO_TIMESTAMP(TO_CHAR(systimestamp, 'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') -
    TO_TIMESTAMP(TO_CHAR(s.sql_exec_start, 'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') delta,
    s.machine,
    s.client_identifier
  from v$sqltext_with_newlines t, V$SESSION s
  where t.address =s.sql_address
    and t.hash_value = s.sql_hash_value
    and s.status = 'ACTIVE'
    and s.username <> 'SYSTEM' /*ignoreme*/
    and t.piece < 62
  group by S.USERNAME, s.osuser, s.sid, s.serial#, t.sql_id, s.sql_exec_start, s.machine, s.client_identifier
),
sessions2 as (
  select db_user, os_user, sid, serial#, sql_id, machine,
    delta,
    extract(DAY FROM delta) || 'd ' || extract(HOUR FROM delta) || 'h ' || extract(MINUTE FROM delta) || 'm ' || extract(SECOND FROM delta) || 's' elapsed,
    client_identifier,
    (select sql_fulltext from v$sqlarea a where a.sql_id = sessions1.sql_id and rownum = 1) sql_text
  from sessions1
)
select s2.db_user, s2.os_user, s2.sid, s2.serial#, s2.machine, s2.elapsed, s2.client_identifier, s2.sql_id, substr(s2.sql_text, 1, 40) sql_fragment, s2.sql_text sql_fulltext
from sessions2 s2
where s2.sql_text not like '%ignoreme%'
order by s2.delta DESC;