select sql_text, executions from v$sql where cpu_time > 20000;

select * from v$session where machine = ‘xxxx’ and logon_time > sysdate - 1;

select sid, ctime from v$lock where block > 0;

select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id

select s.sid, p.spid, s.status,s.process,s.osuser, a.sql_text, p.program
from
v$session s, v$sqlarea a, v$process p
where
s.sql_hash_value=a.hash_value
and s.sql_address=a.address
and s.paddr=p.addr
and s.status='ACTIVE';


The views are owned by the SYS user. You can query $V$FIXED_TABLE to see all the view names. Read consistency is not guaranteed.

More complicated example:
Oracle Session I/O
New Performance Views to Identify Problem SQL

» Read More