RMAN - ORA-30928 and ORA-1652

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