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

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 29 2005 - 08:54:38 CDT