FAQ
Oracle 10.2.0.2
Windows 64 Bit Server 2003

The past few days I've run into an interesting error in RMAN.

When making backups, RMAN is running a query that is consuming
all temp space. This causes an ORA-30928 to RMAN, and an
ORA-1652 to appear in the alert.log

ORA-30928: "Connect by filtering phase runs out of temp tablespace"
ORA-1652: "unable to extend temp segment by %s in tablespace %s"

The query in question:

select round(sum(MBYTES_PROCESSED)),round(sum(INPUT_BYTES)),round(sum(OUTPUT_BYTES)) from V$RMAN_STATUS start with (RECID=:b1 and
STAMP=:b2) connect by prior RECID=parent_recid

This query is easy to identify as coming from RMAN: it appears exactly like
this in the RMAN executable.

I doubled the temp space available in the hopes that this query would
be satiated by a double helping of disposable bytes, but alas, it appears
to be much hungrier than that. (that was 4 gig of TEMP)

This query takes about 3 minutes to run manually before consuming the
temp space (1 gig) my account is allocated.
The sort block usage amounts to 788 rows from v$sort_usage.

I have opened an SR on this, and googled as well, so far to no avail.

Has anyone else here run into this particular error with RMAN?

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Jared Still at Aug 12, 2008 at 10:03 pm

    On Tue, Aug 12, 2008 at 2:52 PM, Jared Still wrote:
    I have opened an SR on this, and googled as well, so far to no avail.
    I spoke too soon.

    Just after hitting I saw that Oracle had replied.

    It's a bug in 10g, the fix is to be in 11g

    Here's the fix:

    *Workaround

    Delete the statistics for the fixed table X$KCCRSR on target database :

    SQL> connect / as sysdba;
    SQL> execute dbms_stats.delete_table_stats(ownname=>'SYS',
    tabname=>'X$KCCRSR');
    *

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 12, '08 at 9:52p
activeAug 12, '08 at 10:03p
posts2
users1
websiteoracle.com

1 user in discussion

Jared Still: 2 posts

People

Translate

site design / logo © 2022 Grokbase