Re: undotbs01 growing , filling up filesystem

Hi John,

1. Ask the developers which huge and long-running transactions they have going on. This is my first choice, as it involves the developers in the trouble-shooting and motivates them to write better code as they better understand the workload problems.
2. When discovering a transaction eating your disc space, first find the rollback segment growing rapidly in v$rollstat and then find the sinner with :

select username,osuser,b.xidusn,c.sql_text  from v$session a ,v$transaction b,v$sql c where b.ses_addr = a.saddr
and xidusn = < rollback segment growing rapidly> and a.sql_hash_value = c.hash_value;

3) Check various other views in the DD. A list of other useful views is found in the Database Performance Guide and Reference.

4) Use tools like sql trace, profiler etc..

• Kenneth Koenraadt

On 31 Dec 2003 00:50:10 -0800, johnleslie_at_madasafish.com (John Leslie) wrote:

><<In a lot of cases this problem can be fixed by tuning or
> redesigning the long running process>.
>
>Thanks for responding. I agree. Any idea how I can identify the SQL or
>package that is to blame?
>
Received on Wed Dec 31 2003 - 05:08:40 CST