Oracle DBA Handy Queries

1.How to identify who logged in?
Answer: select username, osuser,program,module,machine, terminal,process, to_char(logon_time, 'YYYY-MM-DD HH24:MI:SS') as logon_time, status, CASE status when 'ACTIVE' THEN NULL ELSE last_call_et END as idle_time from v$session where type='USER' order by status; 2.What all sessions are consuming resources?
Answer: select s.sid,s.username,s.osuser, to_char(sm.begin_time,'HH24:MI:ss') as interval_start, to_char(sm.end_time, 'HH24:MI:ss') as interval_end, s.machine,s.process,s.program,s.module, sm.cpu,sm.pga_memory,sm.logical_reads,sm.physical_reads, sm.hard_parses,sm.soft_parses, s.logon_time from v$session s inner join v$sessmetric sm on sm.session_id=s.sid where s.type='USER' order by sm.cpu desc; 3.What all statements consume Most Resources?
Answer: select * from (select sql_id,sql_text,executions, elapsed_time,cpu_time,buffer_gets,disk_reads, elapsed_time / executions as avg_elapse_time, cpu_time / executions as avg_cpu_time, buffer_gets / executions as avg_buffer_gets, disk_reads / executions as avg_disk_reads from v$sqlstats where executions >0 order by elapsed_time / executions desc ) where rownum <=25;


1-To know all used features in DB:

set pages 999;
select name,detected_usages from dba_feature_usage_statistics where detected_usages>0;

2-select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
/

3-Show all connected users:

select username,sid || ',' || serial# "ID",status,last_call_et "Last Activity" from   v$session where  username is not null
order by status desc,last_call_et desc

4-Time since last user activity

select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et

5-Sessions sorted by logon time

select  username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time

6-Show a users current sql

Select sql_text
from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value
        from v$session
        where username like '&username')

7-Display any long operations

select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc

8-To know sid details from spid:

select p.spid,s.sid,s.serial#,s.osuser,s.status,s.program,s.machine,s.logon_time,s.saddr,s.osuser,
s.module,s.action
from v$session s , v$process p where s.paddr=p.addr
and spid=&spid