Re: Delete a datafile from a tablespace

Billy Verreynne wrote:
>
> Hanne Iren Midttun wrote:
>
> > I have by accident added a datafile to the wrong tablespace. Can
> > somebody tip me on how can I delete it again?
>
> Look at the ALTER DATABASE command. You can offline/drop the file, or shrink
> it.
>
> > (Never - ever start working before 1 cup of coffee :-)
>
> In my case it is never post here until I have at least 2 or 3 cups.. or else
> I go thread hunting with a lead pipe... ;-)
>
> --
> Billy

Billy,
I'm not sure if this will work if the database is in archivelog mode. I tried this (8.1.7 archivelog):
>alter tablespace test add datafile 'c:\oracle\oradata\test\test2.ora' size 16k;

>alter database datafile 'c:\oracle\oradata\test\test2.ora' offline drop;

>shutdown

>startup

>select status from v$datafile; - Status of test2.ora is RECOVER

>recover database datafile 'c:\oracle\oradata\test\test2.ora';

>alter database datafile 'c:\oracle\oradata\test\test2.ora' online; -It's back again

I may be missing something but then again I'M still on my first cup of coffee :)

BTW The OP also has the option of recreating the tablespace having first either exported all the objects in the tablespace or moved them to another tablespace temporarily.

Anthony Hogan Received on Tue Jun 24 2003 - 03:54:04 CDT