Re: Drop datafile

The following procedure let's you erase that datafile completely. As always - be extremely carefull. Be absolutely sure no extents have been allocated in the added datafile !!! Allthough I've done this procedure a couple of times, I won't recommend doing this on your company's production servers !!! (I don't think Oracle will support it!) So - do it at your OWN risk !!!

Testcase:
create tablespace foo datafile 'd:\utl\foo01.dbf' size 1M ; create table t (i integer) tablespace foo ; insert into t values (1) ;
commit ;

alter database backup controlfile to trace ;

alter tablespace foo add datafile 'd:\utl\foo02.dbf' size 1M ; select name, file# from v$datafile ;
D:\UTL\FOO02.DBF 7 alter database datafile 'd:\utl\foo02.dbf' offline drop ; (in noarchivelog)

delete from file$ where file#=7 ;
commit ;
delete from fet$ where file#=7 ;
commit ;

shutdown ;

startup nomount ;
recreate the controlfile (without foo02.dbf !!!) alter database open ;
-> foo02.dbf never existed
select * from t;
-> 1

I've done this on a couple of TEST servers on NT and AIX ... they are still running but don't shoot me if you f*ck up your DB !

"Bernard Bourdon" <bernard.bourdon_at_pi.be> wrote in message news:9kk3nj$eva$1_at_news.planetinternet.be...
> I want to delete an empty datafile that I just created by mistake. I use
 the
> command "alter database datafile 'xxx' offline drop". No error, the
 datafile
> is offline but is not dropped. What can I do ? Can I modify some system
> tables directly (I would have done that on MS Sql Server in a few seconds)
 ?
>
>
> Thanks,
> Bernard
>
>
>
>
Received on Sun Aug 05 2001 - 14:42:09 CDT