Oracle Forums Community of Oracle Professionals including Fusion/Cloud Application Consultants, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com 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 by upgrading to "Premium Membership".
Oracle Training

Thread Rating:
  • 16 Vote(s) - 2.56 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Script for Undo Information
#1
REM:**********************************************************************************************
REM: Script : Undo Informations
REM: Author: Kumar Menon
REM: Date Submitted: 16-July-2009
REM:FileName: Undoinfo.sql
REM:
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
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') ;

SELECT r.name rbs,
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 x.name = 'db_block_size'
ORDER
BY r.name ;

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'
select
owner, segment_name, segment_type, tablespace_name,
(bytes / 1048576) "Mbytes",
extents
from sys.dba_segments
where tablespace_name = '&UNDO01'
order by owner, segment_name ;

spool off
Reply
Thanks given by:




Users browsing this thread: 1 Guest(s)
Get Premium Membership Get Oracle Tutorials, Tips! Post a Question!