query eating more Temp space and throwing ORA-01652: unable to extend temp segment by even for one row

Hi All,

I had situation  when tried to fetch some amount of records from v$rman_backup_job_details,its keep eating lot of temp space and finally throws error on 10204.

ORA-01652: unable to extend temp segment by 256 in tablespace TEMP 

finally identified there was a bug and below is the work around.

you are likely to be running in to Bug 5466436

The workaround for this issue is to delete statistics from the X$KCCRSR ie:

exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');

- this deletes the statistics on the fixed object  and

exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');

- this locks that object so that statistics will not be collected in future.