Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • FRA Information. Oracle
  • find_idle_cpu.sql Oracle
  • good note for shared pool tunnig Oracle
  • Temporary Tablespsace Temp tablespace behaviour Oracle
  • catall.sh Linux/Unix
  • _B_TREE_BITMAP_PLANS issue during 8.1.7 to 9.2.0.8 upgrade Oracle
  • Reading config file from other folder inside class Python/PySpark
  • Find long Running Transaction Linux/Unix
  • logminer and my_lbu Oracle
  • Linux CPU info. Linux/Unix
  • upload.html PHP/MYSQL/Wordpress
  • telnet listening Linux/Unix
  • fkwoindex.sql /* Find FK without Index */ Oracle
  • Gather Stats manually using DBMS_STATS after disabling DBMS_SCHEDULER jobs as previous entry Oracle
  • Jai Shree Ram Oracle

How to find who is using which Rollback segment and how many rows or blocks in that rollback segments,

Posted on 16-Jun-2006 By Admin No Comments on How to find who is using which Rollback segment and how many rows or blocks in that rollback segments,

Using following query, we can estimate # of rows resides in each USN (Rollback Segment) and who fired that transactions.

New modified GCU.sql on 16-JUN-2006

/* Get Curent USN */

column start_dt format a20

set lines 132

select /*+ ALL_ROWS */ a.usn, a1.name, a.extents, a.hwmsize, a.status usn_status,

to_char(b.start_date, ‘DD-MON-RRRR:HH24:MI:SS’) start_dt, b.status tx_status,

c.sid, c.sql_id, b.used_ublk, b.used_urec,

d.sql_text

from v$rollstat a, v$rollname a1, v$transaction b, v$session c, v$sqlarea d

where a.usn = b. xidusn

and a.usn = a1.usn

and b.ses_addr = c.saddr

and c.sql_id = d.sql_id;

Oracle, SQL scripts

Post navigation

Previous Post: Generate SSH without password authentication.
Next Post: My Minimum Tuning Programs

Related Posts

  • Best approach for Oracle database patching sequence to latest/required patchset along with CPU/PSU/any-other-one-off patch ID 865255.1 Oracle
  • Jai Shree Ram Oracle
  • Sample WW22 listener.ora Oracle
  • Good links for x$ tables in oracle. Oracle
  • sql_doing_fts.sql Oracle
  • Reclaim temp tablespace for oracle 8, 8i Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Add new columns in dataframe Python/PySpark
  • Jai Shree Ram Oracle
  • standard Monitoring – 1 Oracle
  • Read CSV file using PySpark Python/PySpark
  • sid_wise_sql.sql Further explaination Oracle
  • Jai Shree Ram Oracle
  • How to Modify the statistics collection by MMON for AWR repository Oracle
  • Generate SSH without password authentication. Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme