BN, I agree with Michael if you have STATSPACK configured. Otherwise, look in V$SQL, joined with V$SESSION. It doesn't directly have redo logs, but this process will probably stand out in other ways, like the amount of execution time. Dennis Williams On 8/29/05, Michael McMullen <ganstadba@xxxxxxxxxxx> wrote: > > I had the same problem once. My statspack snapshots located the offender > pretty quickly. > ----- Original Message ----- > From: "BN" <bnsarma@xxxxxxxxx> > To: <oracle-l@xxxxxxxxxxxxx> > Sent: Monday, August 29, 2005 9:52 AM > Subject: Identify the User & the SQL Generating tons of Redo > > > > 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 > > > -- > //www.freelists.org/webpage/oracle-l >