Monitor the UNDO tablespace extents

Below query will do it:

select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents 
group by status order by status;

STATUS    NUM_EXTENTS NUM_BLOCKS         MB
--------- ----------- ---------- ----------
ACTIVE          16069   11519120   89993.13
EXPIRED           696       6048      47.25

UNEXPIRED        2286      21408     167.25

How much memory my database is using?

This query is instance wise:


select (sga+pga)/1024/1024 as "sga_pga"
from
(select sum(value) sga from v$sga),
(select sum(pga_alloc_mem) pga from v$process)
/

This query is giving the details per each process:

select vs.program, s.sid, sn.name, round( s.value/1024/1024, 2 ) mb
    from v$statname sn, v$sesstat s, v$session vs
   where sn.statistic# = s.statistic#
     and vs.sid = s.sid
     and sn.name = 'session pga memory'
order by 4
/

Oracle DB: which session is generating the most redo logs right now?

select * from v$sesstat
where STATISTIC# in (select STATISTIC# from v$statname where name like '%redo%')
and value>0
order by value
/


NAME
----------------------------------------------------------------
spare statistic 2

SQL> select name from v$statname where STATISTIC#=194;

NAME
----------------------------------------------------------------
redo size

SQL> select name from v$statname where STATISTIC#=197;

NAME
----------------------------------------------------------------
redo size for direct writes