Undo table space using more and 100% Full

Undo table space is not release the data and using more space.

When the UNDO tablespace is created with NO AUTOEXTEND, following the allocation algorithm, here is the explanation for this correct behavior:

For a fixed size UNDO tablespace (NO AUTOEXTEND), starting with 10.2, we provide max retention given the fixed undo space, which is set to a value based on the UNDO tablespace size.
This means that even if the undo_retention is set to a number of seconds (900 default), the fixed UNDO tablespace supports a bigger undo_retention time interval (e.g: 36 hours), based on the tablespace size, thing that makes the undo extents to be UNEXPIRED. But this doesn't indicate that there are no available undo extents when a transaction will be run in the database, as the UNEXPIRED undo segments will be reused.

concerning an UNDO tablespace created with AUTOEXTEND OFF in 10gR2, so there is not need to add more space to it or be concerned by the fact that it appears to be 100% full.

Whether the undo is automatically managed by the database by checking the following instance parameter:
UNDO_MANAGEMENT=AUTO

1. Whether the undo tablespace is fixed in size:
   
    SELECT autoextensible
        FROM dba_data_files
        WHERE tablespace_name=''
   
   This returns "NO" for all the undo tablespace datafiles.
2. The undo tablespace is already sized such that it always has more than enough space to store all the undo generated within the undo_retention time, and the in-use undo space never exceeds the undo tablespace warning alert threshold (see below for the query to show the thresholds).
3. The tablespace threshold alerts recommend that the DBA add more space to the undo tablespace:
   
    SELECT creation_time, metric_value, message_type, reason, suggested_action
        FROM dba_outstanding_alerts
        WHERE object_name='';
   
   This returns a suggested action of: "Add space to the tablespace".
   
   Or,
   
   This recommendation has been reported in the past but the condition has now cleared:
   
   SQL> SELECT creation_time, metric_value, message_type, reason, suggested_action, resolution
        FROM dba_alert_history
        WHERE object_name='';
4. The undo tablespace in-use space exceeded the warning alert threshold at some point in time. To see the warning alert percentage threshold, issue:
   
    SELECT object_type, object_name, warning_value, critical_value
   FROM dba_thresholds
   WHERE object_type='TABLESPACE';
   
   To see the (current) undo tablespace percent of space in use:
   
    SELECT
            ((SELECT (NVL(SUM(bytes),0))
              FROM dba_undo_extents
              WHERE tablespace_name=''
              AND status IN ('ACTIVE','UNEXPIRED')) * 100)/       
            (SELECT SUM(bytes)
             FROM dba_data_files
             WHERE tablespace_name='')
            "PCT_INUSE"
        FROM dual
5. SOLUTION:
6. 1. Set the AUTOEXTEND and MAXSIZE attributes of each datafile of the undo tablespace in such a way that they are autoextensible and the MAXSIZE is equal to the current size (so the undo tablespace now has the AUTOEXTEND attribute but does not autoextend):
      
       ALTER DATABASE DATAFILE '' AUTOEXTEND ON MAXSIZE
      
      With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the undo tablespace size. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.
   2. Set the following instance parameter:
      
      _smu_debug_mode=33554432
      
      With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.
   3. Set the following instance parameter:
      
      _undo_autotune = false
      
      With this setting, V$UNDOSTAT (and therefore V$UNDOSTAT.TUNED_UNDORETENTION) is not maintained and the undo retention used is based on the UNDO_RETENTION instance parameter.
      
      
      NOTE: This means you loose all advantages in having automatic undo management and is not an ideal long term fix.

   Automatic Tuning of Undo_retention Causes Space Problems (Doc ID 420525.1).

ACTIVE / INACTIVE SessionsSql queries to check ACTIVE / INACTIVE Sessions in R12/11i

Sql queries to check ACTIVE / INACTIVE Sessions

Total Count of sessions

select count(s.status) TOTAL_SESSIONS

from gv$session s;

Total Count of Inactive sessions

select count(s.status) INACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR

select count(s.status) "INACTIVE SESSIONS > 1HOUR "

from gv$session s, v$process p

where

p.addr=s.paddr and

s.last_call_et > 3600 and

s.status='INACTIVE';

COUNT OF ACTIVE SESSIONS

select count(s.status) ACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='ACTIVE';

TOTAL SESSIONS COUNT ORDERED BY PROGRAM

col program for a30

select s.program,count(s.program) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.program;

TOTAL COUNT OF SESSIONS ORDERED BY MODULE

col module  for a30

prompt TOTAL SESSIONS

select s.module,count(s.sid) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.module;

TOTAL COUNT OF SESSIONS ORDERED BY ACTION

col action for a30

prompt TOTAL SESSIONS

select s.action,count(s.sid) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.action;

INACTIVE SESSIONS

prompt INACTIVE SESSIONS

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

INACTIVE

prompt INACTIVE SESSIONS

select count(s.status) INACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE';

INACTIVE PROGRAMS

col module for a40             

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, v$process p

where  p.addr=s.paddr and

s.status='INACTIVE';

INACTIVE PROGRAMS with disk reads

prompt INACTIVE SESSIONS

select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

GROUP BY S.PROGRAM;

INACTIVE SESSIONS COUNT WITH PROGRAM

col program for a30

prompt TOTAL INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

group by s.program

order by 2 desc;

TOTAL INACTIVE SESSIONS MORE THAN 1HOUR

col program for a30

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Inactive_Sessions_from_1Hour

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

and s.last_call_et > (3600)

group by s.program

order by 2 desc;

TOTAL INACTIVE SESSIONS GROUP BY  MODULE

col program for a60

COL MODULE FOR A30

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.module,count(s.module) Total_Inactive_Sessions

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

group by s.module;

INACTIVE SESSION DETAILS MORE THAN 1 HOUR

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et_Hrs for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.last_call_et > (3600)

order by last_call_et;

INACTIVE PROGRAM  --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

And s.program='&PROGRAM_NAME'

order by last_call_et;

INACTIVE MODULES  --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr

And s.module like '%order_cleanup_hazmat_v3.sql'

order by last_call_et;

INACTIVE JDBC SESSIONS

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,

s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

COUNT OF INACTIVE SESSIONS MORE THAN ONE HOUR

SELECT COUNT(P.SPID)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

FORMS

TOTAL FORM SESSIONS

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%');

FORMS SESSIONS DETAILS

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600

order by spid;                      

col machine for a15

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

S.ACTION,s.process Client_Process,s.machine

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600;         

order by 4;                           

INACTIVE FORMS SESSIONS DETAILS

col program for a15

col last_call_et for 999.99

select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

order by last_call_et desc;

UNIQUE SPID

select unique(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

COUNT FORMS

select COUNT(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

ZERO HASH VALUE

select COUNT(p.spid)

from gv$session s,gv$process p

where

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

AND S.SQL_HASH_VALUE=0;

INACTIVE FORM BY NAME

select count(s.sid) from v$session S

where s.action like ('%&ACTION%')

AND S.STATUS='INACTIVE';

GROUP BY ACTION

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%')

group by s.action;

FROM A SPECIFIC USERNAME

SET LINSIZE 152

col spid for a10

col process_spid for a10

col user_name for a20

col form_name for a20

select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,

d.user_form_name "FORM_NAME"

from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,

apps.fnd_form_tl d

where

a.login_id=b.login_id

and c.user_name like 'JROMO'

and a.user_id=c.user_id

and trunc(b.start_time) >trunc(sysdate -11)

and trunc(b.end_time) is null

and b.form_id=d.form_id

and d.language='US';

INACTIVE FORM

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like ('FRM%')

and s.last_call_et > (3600*3)

order by last_call_et;

INACTIVE FORM SESSIONS 

col cli_proc for a9

COL AUDSID FOR A6

COL PID FOR A6

COL SID FOR A5

COL FORM_NAME FOR A25

COL USER_NAME FOR A15

col last_call_et for 9999.99

SELECT

-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */

( SELECT SUBSTR ( fu.user_name, 1, 20 )

FROM apps.fnd_user fu

WHERE fu.user_id = fl.user_id

) user_name,vs.status,

TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,

TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,

vs.last_call_et/3600 last_call_et,

SUBSTR ( fl.process_spid, 1, 6 ) spid,

SUBSTR ( vs.process, 1, 8 ) cli_proc,

SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,

SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,

SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,

SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,

SUBSTR ( vs.module || ' - ' ||

( SELECT SUBSTR ( ft.user_form_name, 1, 40 )

FROM apps.fnd_form_tl ft

WHERE ft.application_id = rf.form_appl_id

AND ft.form_id        = rf.form_id

AND ft.language       = USERENV('LANG')

), 1, 40 ) form_name

FROM apps.fnd_logins           fl,

gv$process            vp,

apps.fnd_login_resp_forms rf,

gv$session            vs

WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */

AND fl.login_type   = 'FORM'

AND fl.process_spid = vp.spid

AND fl.pid          = vp.pid

AND fl.login_id     = rf.login_id

AND rf.end_time    IS NULL

AND rf.audsid       = vs.audsid

and vs.status='INACTIVE'

ORDER BY

vs.process,

fl.process_spid;

ACTIVE

prompt ACTIVE SESSIONS

select count(s.status) ACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='ACTIVE';

MODULE

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr 

and s.MODULE like ('&MODULE_NAME_1HR%')

and s.last_call_et > ('&TIME_HRS' * 3600)

order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

p.addr=s.paddr 

and s.MODULE like ('%TOAD%')

Order by last_call_et;

TOAD SESSIONS

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$process p

where

p.addr=s.paddr 

and s.MODULE like ('%TOAD%')

Order by last_call_et;

CLIENT MACHINE SESSIONS COUNT

select count(s.process) TOTAL from v$session S

where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0

AND S.LAST_CALL_ET > 3600;

Unique Actions

col module for a40             

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

s.machine like ('%&CLIENT_MACHINE%') AND

p.addr=s.paddr and

s.status='INACTIVE';

GROUP BY  program

col program for a60

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr  AND

s.machine like ('%&CLIENT_MACHINE%') AND

s.status='INACTIVE'

group by s.program;