Re: Identify the User & the SQL Generating tons of Redo

  • From: "Michael McMullen" <ganstadba@xxxxxxxxxxx>
  • To: <bnsarma@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 29 Aug 2005 09:58:47 -0400

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
> --

Other related posts: