| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How can I find out what's filling up my rollback segment?
First, a word of clarification. My interpretation of your original post is
different than that of the other replies. If you just want to know which
RBS is blowing up, or what the extents in each RBS are, then the other
replies are giving you good advice, so go with those. My query, on the
other hand, will show you the non-RBS objects (tables, clusters, etc.) that
are locked by current transactions that are allocating RBS space from a
particular RBS. If *this* is what you want to know, then proceed with my
script.
I apologize for not explaining the prompt. The USN that you are prompted for is a rollback segment number ("Undo Segment Number") that you can get from the segment_id column in dba_rollback_segs or the USN column in v$rollstat or v$rollname.
I hope this helps - let me know if you have more questions.
Best regards,
Chris
In comp.databases.oracle.server you write:
>**** Post for FREE via your newsreader at post.usenet.com ****
>Maybe something like this would help you out:
>select * from dba_dml_locks
>where session_id in
>(select sid from v$session s, v$transaction t
> where s.saddr = t.ses_addr
> and t.xidusn = &usn);
Thanks, I ran the perpetory script, and it seemed to go well, but when I run your query it promp't me for a usm, what's it looking for? rollback sgement name? tablepsce name?
-- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin "Chris Leonard" <chris_at_databaseguy.com> wrote in message news:3c0bb3dd_at_post.usenet.com...Received on Wed Dec 05 2001 - 20:37:09 CST
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Maybe something like this would help you out:
>
> select * from dba_dml_locks
> where session_id in
> (select sid from v$session s, v$transaction t
> where s.saddr = t.ses_addr
> and t.xidusn = &usn);
>
> Before running this query, you should run the Oracle-supplied script
> catblock.sql, which is found in $ORACLE_HOME/rdbms/admin.
>
> Hope this helps!
> Chris
> __________________________________
> Chris Leonard
> The Database Guy at PPI
> MCSE, MCDBA, MCT, OCP, CIW
> Productivity Point International
> dba_at_propoint.com / 319.398.7099
>
>
> "Stan Brown" <stanb_at_panix.com> wrote in message
> news:9ug09g$b6v$1_at_panix2.panix.com...
> > I was running a long runing task on my 7.3.4.5 instance when it died due
> to
> > lack of space in the rollback segment. Now what's interesting is that
this
> > task used to work, and as far as I know it was the only thing acessing
the
> > instance at the time. So I took a look at the usage in the tablespcae.
> here
> > is what I found:
> >
> > TSPACE TOT_MB ORA_BLKS TOT_USED PCT_USED
> > --------------- ---------- ---------- ---------- ----------
> > RBS 500 64000 40400 63.13
> >
> > So, what query can I run to figure out what objects exist in the
rollback
> > tablespcae?
> >
> > --
> > "They that would give up essential liberty for temporary safety deserve
> > neither liberty nor safety."
> > -- Benjamin Franklin
>
>
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.usenet.com
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 |
 |