redundant rollback space

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chris.brown@providentinsurance.co.uk - 06 Oct 2008 11:15 GMT

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

Reply to this Message

Mark D Powell - 06 Oct 2008 15:15 GMT

On Oct 6, 6:15 am, chris.br...@providentinsurance.co.uk wrote:

> I am working on a 10.2.0.3 ee database on windows 2003
>
[quoted text clipped - 26 lines]
> 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 --

Reply to this Message