Re: redundant rollback space

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 6 Oct 2008 07:15:34 -0700 (PDT)
Message-ID: <0a3a4f53-fdb6-43ec-a14c-bf4902273823@y79g2000hsa.googlegroups.com>


On Oct 6, 6:15 am, chris.br..._at_providentinsurance.co.uk wrote:
> I am working on a 10.2.0.3 ee database on windows 2003
>
> A tablespace that was available in 8i has been taken offline
> ( alter tablespace RBS offline )
>  ..in anticipation of  dropping as it was thought it was not being
> used.
>
> When I ran a truncate command got the following error
>
> truncate table insert_failure_log drop storage
>                *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-00376: file 70 cannot be read at this time
> ORA-01110: data file 70: 'H:\ORACLE\ORADATA\DM2\RBS02.ORA'
>
> How can I tell which tablespaces are used by the database for
> rollback ?
>
> Show parameters didn't refer to the RBS tablespace
>
> undo_management                      string      AUTO
> undo_retention                       integer     900
> undo_tablespace                      string      undo_rbs1
> use_indirect_data_buffers            boolean     FALSE
>
> My goal was to save space by removing a redundant tablespace
>
> cheers
> Chris B

Chris, first question, what tablespace is table insert_failure_log created in? Second question, are you running RAC? (Each instance would need its own undo tablespace)

Here is a query that will show you what rbs segments are in use in which tablespaces: (add instance_num if RAC)

select segment_name, tablespace_name, status from dba_rollback_segs
order by tablespace_name, segment_name

HTH -- Mark D Powell -- Received on Mon Oct 06 2008 - 09:15:34 CDT

Original text of this message