Warning against manipulating the datadictionary directly was Re: Drop datafile

Hopefully you never did this on your production servers. You are creating havoc, and I'm not sure whether you addressed *all* dictionary tables affected.
For one thing you are missing uet$.
So after these commands, the dictionary will be inconsistent.

Following your procedure will invalidate the users support contract.

Thus, IMO, I think posting such advice you should be prepared to be sued by anyone following it.

Also, again IMO, I consider this advice as unprofessional and I caution anyone reading it NOT to follow it.

I strongly urge you to refrain from posting similar advice in the future, it is very inappropiate in a professional usenet newsgroup.

Regards,

Sybrand Bakker, Senior Oracle DBA

"koert54" <k_at_k.com> wrote in message
news:lkhb7.14674$lB.2810081_at_afrodite.telenet-ops.be...
> 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 - 16:20:51 CDT