Archive Generation is more in oracle

chceck tbs in begin backup mode:
================================

select a.tablespace_name
from sys.dba_data_files a, sys.v_$backup b
where b.status = 'ACTIVE'
and b.file# = a.file_id
group by a.tablespace_name;


Query to find the session that is generating more Archives
This Query is to find the session that is generating more Archives.
====================================================================

col program for a10
col username for a10
select to_char(sysdate,'hh24:mi'), username, program , a.sid, a.serial#, b.name, c.value
from v$session a, v$statname b, v$sesstat c
where b.STATISTIC# =c.STATISTIC#
and c.sid=a.sid and b.name like 'redo%'
order by value;





Check which session is generating more redo.
============================================
set pages 1000
set lines 140
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;


SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t
WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


The o/p of the query gives the sid , Number of undo blocks used and Number of undo records used that were happening during that time.
From the sid we can get the request id and the request name.



select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid');

select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
from v$session where sid=&123;