Since 2010, OraERP is a Oracle Forums, Community of Oracle Professionals including Fusion/Cloud Application Consultants, Enterprise Architects, ERP Cloud, HCM Cloud, CX Cloud and OCI Experts, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Digital Architect, PaaS Experts, IaaS, OCI Architects, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple. Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips .
Thread Rating:
  • 32 Vote(s) - 2.84 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Script for Undo Information
01-29-2011, 01:35 AM,
Script for Undo Information
REM: Script : Undo Informations
REM: Author: Kumar Menon
REM: Date Submitted: 16-July-2009
REM:FileName: Undoinfo.sql
REM: Author will not be responsible for any damage that may be cause by this script.

spool d:\undoinfo.txt
SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(SELECT value AS DBS FROM v$parameter WHERE name = 'db_block_size') ;

NVL(s.username, 'None') oracle_user,
s.osuser client_user,
p.username unix_user,
TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) as sid_serial,
p.spid unix_pid,
t.used_ublk * TO_NUMBER(x.value)/1024 as undo_kb
FROM v$process p,
v$rollname r,
v$session s,
v$transaction t,
v$parameter x
WHERE s.taddr = t.addr
AND s.paddr = p.addr(+)
AND r.usn = t.xidusn(+)
AND = 'db_block_size'
BY ;

select l.sid, s.segment_name from dba_rollback_segs s, v$transaction t, v$lock l
where t.xidusn=s.segment_id and t.addr=l.addr ;
select to_char(begin_time,'hh24:miConfuseds'),to_char(end_time,'hh24:miConfuseds')
, maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
order by undoblks ;
set lines 160 pages 40
col machine format A20
col username format A15
select xidusn, xidslot, trans.status, start_time, ses.sid, ses.username, ses.machine ,proc.spid, used_ublk
from v$transaction trans, v$session ses , v$process proc
where trans.ses_addr =ses.saddr and ses.paddr=proc.addr
order by start_time ;

select to_char(begin_time,'hh24:miConfuseds'),to_char(end_time,'hh24:miConfuseds')
, maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
order by undoblks ;
Promot "following to show how much undo is being used:"

set pagesize 24
set lin 132
set verify off
col owner format a13
col segment_name format a25 heading 'Segment Name'
col segment_type format a15 heading 'Segment Type'
col tablespace_name format a15 heading 'Tablespace Name'
col extents format 99999999 heading 'Extent'
owner, segment_name, segment_type, tablespace_name,
(bytes / 1048576) "Mbytes",
from sys.dba_segments
where tablespace_name = '&UNDO01'
order by owner, segment_name ;

spool off
Thanks given by:

Users browsing this thread: 1 Guest(s)
Open Tech Talks Get Oracle Tutorials, Tips! Post a Question!