Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to drop a tablespace when datafile is lost

Re: How to drop a tablespace when datafile is lost

From: James Lorenzen <james_lorenzen_at_allianzlife.com>
Date: Mon, 22 Nov 1999 19:24:07 GMT
Message-ID: <81c58g$dre$1@nnrp1.deja.com>


It's been awhile, but I believe that you can clear this up by going into svrmgr:
1. STARTUP MOUNT
2. ALTER DATABASE DATAFILE 'filename' OFFLINE DROP ;

        — This will remove the missing datafile from the database. 3. ALTER TABLESPACE tablespace_name OFFLINE ;

        — This should take the tablespace offline. 4. ALTER DATABASE OPEN ;
5. DROP TABLESPACE tablespace_name ;

I don't know what the presence of a rollback segment on the missing file would do this process.

HTH
    James
In article <81btr4$7t4$1_at_nnrp1.deja.com>,   lbrenta_at_my-deja.com wrote:
> Hello,
>
> One of the datafiles in my database has been lost. This is not a big
> deal, because it was the only datafile in a tablespace that was not in
> use anyway. Now I want to drop the tablespace.
>
> Unfortunately, a rollback segment was in that tablespace (and
datafile)
> and it gets in the way. I can't drop it because I get the ORA-01545
> error message when trying to do so. I can't alter it to bring it
either
> online or offline since the datafile is gone.
>
> I'm not interested in recovering the datafile, and even if I were, I
> have no archived redo logs or backups for that particular datafile. I
> also don't want to lose my other tablespaces, but I can afford to shut
> down the instance.
>
> So, the question boils down to this: how can I nuke a tablespace when
> the datafile is gone?
>
> Any help will be appreciated
>
> --
> Ludo.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 22 1999 - 13:24:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US