Re: Rollback Segment Question

ame..._at_iwc.net wrote:
> Frank van Bortel wrote:
> > amerar_at_iwc.net wrote:
> > > Hey All,
> > >
> > > Take a look at the results of this query. Why is it that
rollback
> > > segment 23 (RBS23) seems to have all the extends?
> >
> > The usual reason... a long running transaction.
> >
> > And... time to upgrade.
> > Now, how do I know that?
> > --
> > Regards,
> > Frank van Bortel
>
> If I can ever convince this palce to upgrade to 9i to 10g, it would
be
> a miracle. They are so damn scared of every little change.....

I've found this to be helpful (at least, when the nasty trans is running):

select

   substr(a.os_user_name,1,8)    "OS User",
   substr(a.oracle_username,1,8) "DB User",
   substr(b.owner,1,8)  "Schema",
   substr(b.object_name,1,20)    "Object Name",
   substr(b.object_type,1,10)    "Type",
   substr(c.segment_name,1,5)  "RBS",
   substr(d.used_urec,1,12)      "# of Records"

   v$locked_object a,
   dba_objects b,
   dba_rollback_segs c,
   v$transaction d,
   v$session e
where a.object_id = b.object_id

   and a.xidusn    =  c.segment_id
   and a.xidusn    =  d.xidusn
   and a.xidslot   =  d.xidslot
   and d.addr      =  e.taddr

/

I just tried it on a 9206, and it still works. Large transactions using up rollback space, expected and otherwise, are a fact of life in all versions.

As a DBA with your type of RBS, you may need to remind your management that they are paying you to monitor these things, and you could perhaps be a better return on their investment to move to a supported environment where these particular things are more automatically managed. Yes, there will be pain, but there eventually will be more pain if you don't - and the value of "eventually" is difficult to compute.

Just don't use OPTIMAL in your current config, as tempting as it may seem on the surface.

jg

--
@home.com is bogus.
http://news.bbc.co.uk/1/hi/world/middle_east/4430851.stm