Re: Datafile of tablespace with rollback segment lost!

> I have accidentally deleted a datafile of a tablespace wish contains
> only one rollback segment.
> I try to drop the tablespace with the following commands:
> startup mount
> alter tablespace datafile 'datafile_name' offline drop;
> alter tablespace open;
>
> all this fine, when the database was opened I try to drop the tablespace
> with the command :
> drop tablespace 'tablespace_name' including contents;
>
> and I have the error message :
> ORA-01548: active rollback segment 'RB_DATA' found,
> terminate dropping tablespace
>
> It's nor online or offline.
> I can't drop this rollback segment, his status is 'needs recovery' !!
>
> I heard that I need to restart the database after adding a variable to the
> INIT.ORA file for the rollback segment...

Hi Haithem,
What U have to do is, the following thing:

start the instance;
mount the database;(Don't open it);
give the command---->ALTER DATABASE BACKUP CONTROLFILE TO $file_name; This is will give U, your controlfile's content, and it contains the SQL statement to create your control file ; edit the file and remove the entry, which refers the tablespace name where your ROLLBACK Segement exist;

Then, execute this SQL Script, which creates new CONTROLFILE;

Also, edit your INIT%x.ORA file and comment the rollback_segment line;(cause, U won't be having any ROLLBACK SEGMENT, except the SYSTEM ROLLBACK Segment)
Then open the database.

It's advisable to take a COLD BACKUP BEFORE and AFTER THIS PROCESS. Hope this helps in a way ....
Thanks,
Siva.

 [7m ************************       [0m
 [5m [4m  [1mSivasubramaniam. E             [0m
 [7m Hcl Consulting Ltd.            [0m
 [7m Gurgaon. India.                [0m
 [7m e-mail : siva_at_hclind.hcla.com  [0m
 [7m ************************       [0m

REBIRTH -- Is it to attain our desire,
           That we wish in this BIRTH .?
 [4m                                          [0m