Re: How to drop Datafile from Tablespace?

Adrian,

  What Mark is saying is right. Want to add one more point here. If you do not have Enterprise Manager you CAN always find out whether any datafile contains data or not. This can be done by finding unique file id from rowid from rows from all tables. rowid format is BLOCK Id.Sequence Number of the row within that block.FILE ID This file id can be compared with the file id from dba_data_files for that particular data file.

Good Luck
Vivek

> >Your best option at this point is to try resizing the datafiles as small as
possible, so
> >that nothing else will ever use them. But, you can't get rid of them
entirely without
> >recreating the tablespace
> >
> >Rick wrote:
> >
> >> John Koo wrote:
> >> >
> >> > Adrian Harrison wrote:
> >> >
> >> > > Had to add several datafiles to an existing Tablespace for testing
reasons!
> >> > >
> >> > > Can't find how to remove them! - I don't want to have to drop the
tablespace itself!
> >> > >
> >> > > Any ideas
> >> > >
> >> > > Thanks
> >> > >
> >> > > A.H
> >> >
> >> > Just want to let you know there is NO WAY to drop an added datafile from
a tablespace.
> >> > If you don't believe then go to check the ALTER TABLESPACE syntax.
> >> > What you ARE SUPPOSED to do is to create a temporary-used tablespace for
testing
> >> > and then dropped it completely after use. Get it ?
> >> >
> >> > John Koo
> >> > johnkoo_at_i-wave.net
> >> John is absolutely right on this. You have no way of knowing whether
> >> any table data is stored in the datafile you want to drop. So it makes
> >> no sense to try.
> >>
> >> Rick
> >
> >--------------BF79B1DEDB0C4608C440E299
> >Content-Type: text/x-vcard; charset=us-ascii;
> > name="kazimiej.vcf"
> >Content-Transfer-Encoding: 7bit
> >Content-Description: Card for Joe Kazimierczyk
> >Content-Disposition: attachment;
> > filename="kazimiej.vcf"
> >
> >begin:vcard
> >n:Kazimierczyk;Joe
> >tel;fax:609-818-6045
> >tel;work:609-818-4134
> >x-mozilla-html:TRUE
> >url:http://wwwlv.bms.com/~kazimiej/
> >org:Bristol-Myers Squibb
> >version:2.1
> >email;internet:kazimiej_at_bms.com
> >title:Senior Database Administrator
> >adr;quoted-printable:;;PO Box 5400=0D=0Amailstop
HW8T-2.17;Princeton;NJ;08543-5400;USA
> >x-mozilla-cpt:;24576
> >fn:Kazimierczyk, Joe
> >end:vcard
> >
> >--------------BF79B1DEDB0C4608C440E299--
> >

>
> Just to throw the cat among the pigeons ...
>
> you CAN drop a datafile from a tablespace and you can see whether
> there are any objects which exist in the datafile you want to drop.
>
> If you have it, the Tablespace Manager tool (part of the Enterprise
> Manager - optional Tuning Pack) will show you graphically what objects
> exist in a tablespace and even show you which datafile they exist in.
>
> If there are any objects whatsoever in this tablespace, you should NOT
> drop the datafile.
>
> As regards dropping the datafile....
>
> ALTER DATABASE DATAFILE '/u04/oradata/<SID>/datafile_not_wanted.dbf'
> OFFLINE DROP;
>
> will work.  As to whether this screws the tablespace up afterwards, I
> can't say.  I just used it to get around the problem of my tablespaces
> trying to look for non-existent files that had accidentally been
> deleted!!  Once I had dropped them and opened the database
> successfully, I dropped and then rebuilt my tablespaces properly.
>
> Be VERY careful if you are doing this with data you want to keep!!!!
>
> Be lucky!
>
> Mark Griffiths
>
>

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own