Oracle Database Discussions

when undo tablespace is Auto extend mode then we know "undo_retention" parameter is working .

In this moment , we normally get more EXPIRED block rather that UNEXPIRED block .

In our production server , we make undo tablespace auto extended . So using the below query
we get the output :

SQL> select status,
2 round(sum_bytes / (1024*1024), 0) as MB,
3 round((sum_bytes / undo_size) * 100, 0) as PERC
4 from
5 (
6 select status, sum(bytes) sum_bytes
7 from dba_undo_extents
8 group by status
9 ),
10 (
11 select sum(a.bytes) undo_size
12 from dba_tablespaces c
13 join v$tablespace b on b.name = c.tablespace_name
14 join v$datafile a on a.ts# = b.ts#
15 where c.contents = 'UNDO'
16 and c.status = 'ONLINE'
17 );

STATUS MB PERC
--------- ---------- ----------
ACTIVE 2 0
EXPIRED 324 2
UNEXPIRED 57 0

(This output is taken during peak hour .)






SQL> select status,
2 round(sum_bytes / (1024*1024), 0) as MB,
3 round((sum_bytes / undo_size) * 100, 0) as PERC
4 from
5 (
6 select status, sum(bytes) sum_bytes
7 from dba_undo_extents
8 group by status
9 ),
10 (
11 select sum(a.bytes) undo_size
12 from dba_tablespaces c
13 join v$tablespace b on b.name = c.tablespace_name
14 join v$datafile a on a.ts# = b.ts#
15 where c.contents = 'UNDO'
16 and c.status = 'ONLINE'
17 );

STATUS MB PERC
--------- ---------- ----------
EXPIRED 324 2
UNEXPIRED 1 0


(This output is taken during off-peak hour )


Now my question,(regarding with the above two outputs),

> sometimes I see, on peak hour EXPIRED block get decreasing . I want to know , when and on which condition
>EXPIRED block release the space of UNDOTS ?

> Is this condition depends on the UNDO_RETENTION parameter ? If yes , then on the off-peak hour why
>some EXPIRED blocks are exists ? Here , my UNDO_RETENTION is 15 minutes .

> Is there any UNDO related operation (DML operation) is occuring during the off-peak hour(when no user activity
>is present in my database) ? Here also please inform me , does Oracle own Maintenance Task/Scheduled task
>(Like AWR,ANALYZE) create any undo blocks ?

> During auto extend size of the UNDO Tablespace , where RETENTION_PARAMETER is working , Then how
>"TUNED_UNDORETENTION" field is calculated (from v$undostat) ?

> Is there any background process in oracle , which helps to free the space from UNDOTBS ?




Since I am not an oracle expert , I need to know these to clear my concept . using google I cannot get proper answer . so if any expert
will help me , then I can get clear idea about this .

Waiting for kind reply ... ...

Edited by: shipon_97 on Jun 6, 2012 11:01 AM