Use this sql to find the person who is generating lots of redo. SELECT s.sid, s.serial#, s.username, s.program, i.block_changes FROM v$session s, v$sess_io i WHERE s.sid = i.sid ORDER BY 5 desc, 1, 2, 3, 4; Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session. Read the Doc 167492.1 in metalink. quite helpfull. I set this up with 9I Oem performance manager to find the delta and I was able to some of the guys generating massive redo's in one of our production database. --- BN <bnsarma@xxxxxxxxx> wrote: > Greetings, > > Is there a simple/SQL (other than Logminer) to > Identify the User(s) > and the affending SQL thats generating tons of REDO. > > It so happend last week that one of our DBs started > generating lot of > redologs , at one tme it became difficult to manage > the free space for > archived log files. > > We never see more than 300+ archived redolog files > in day, that day > we just crossed half day and we had already crossed > 500+ archived > redologs. > > Generally we will have around 300+ connections, but > for some strange > reason, we had 600+ connections. I cannot go and > kill the connections > unless I bring all the users and DEV teams on the > call to see what > they are trying to do. > > Nobody came forward to tell that they may be running > a batch job. > This is Orlace 9i and HP box. I couldn't see any > thing specific at the > unix level, when i searched sql_text, I found > a few INSERT statements ... > > I would like to know if there is a SQL way in 8i and > 9i to identify > the users and SQL that's generating the REDO. > > I was trying to hit the long running jobs... I > couldn't figure out... > -- > Regards & Thanks > BN > -- > //www.freelists.org/webpage/oracle-l > __________________________________________________________ How much free photo storage do you get? Store your friends 'n family snaps for FREE with Yahoo! Photos http://in.photos.yahoo.com -- //www.freelists.org/webpage/oracle-l