Re: ROLLBACK tablespace needs media recovery when startup mount;

From: tmgn <>
Date: Wed, 25 Aug 1999 12:17:23 -0400
Message-ID: <>

Yes you can Drop the Tablespace containing the Rollback Segments only and Recreate it. However you cannot drop it if it has Active rollback Segments ; Make them offline , drop the Rollback Segments and than Drop the Tablespace including Contents;

However,if the Rollback Segments are corrupted it may not even allow you to drop it.In that case, You many need to Restart the Instance with _corrupted_rollback_segments =( ..)
entry added to the init.ora file and then Re-try Dropping them.

Hope this helps


Raymond wrote:

> Hi,
> I am new to be database administrator. Currently running Oracle 8.0.3
> on NT4.0 SP3.
> Is it possible to drop the whole tablespace for ROLLBACK segments?
> Any precautions? Actually, I want to delete the 2nd datafile as it is
> no longer used. The story behind is as follows.
> I have created rollback segments for both SYSTEM and user in the same
> ROLLBACK_DATA tablespace. The tablespace consists of 2 physical
> datafiles. One day, I found One of the datafile is in OFFLINE status
> one day for unknown reason.
> When I am trying to disable the archive log mode for importing data to
> another database, and trying to start the database in mount mode but
> failed.
> Checked the Alert.log and found the error ORA-01143: cannot disable
> media recovery - file name needs media recovery.
> According to Oracle8 Online document, cause of ORA-01143 is -
> "An attempt to disable media recovery found a file that needs media
> recovery. Therefore, media recovery cannot be disabled."
> According to Oracle8 Online document, action towards ORA-01143 is -
> "Recover the offending file or drop the tablespace to which it
> belongs, then retry the operation."
> Thanks in advance.
> Phoebe+
Received on Wed Aug 25 1999 - 18:17:23 CEST

Original text of this message