Re: How to drop Datafile from Tablespace?

On Wed, 20 Jan 1999 12:13:08 -0500, Joe Kazimierczyk <kazimiej_at_bms.com> wrote:

>This is a multi-part message in MIME format.
>--------------BF79B1DEDB0C4608C440E299
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>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 Received on Wed Feb 03 1999 - 08:20:56 CST