Re: datafile removal

Kevin Kirkpatrick wrote in message <3606A9D1.35BA8B7F_at_hrb.com>...
>I was wondering what would happen if I went to remove one of the two
>datafiles from my tablespace?

Don't do it. Your database will not shutdown cleanly nor restart without significant intervention on your part.

> Is this safe, meaning that Oracle will
>move data from one datafile to the other one and then remove it?

Absolutely not. Once data is stored in a datafile, it's not moved unless a row becomes too long and is migrated to another block.

Also, the contents of the datafile don't matter to the control files - if it's been created as part of the database, then it has to be there for Oracle to function properly.

>I am
>not exactly sure what the advantages of having two datafiles are,

The benefit of having multiple datafiles is that it allows you to place the files on separate I/O devices and thus distribute the I/O burden of the tablespace across multiple devices.

> Is there a way that you can control what goes into
>a particular datafile?

Sometimes. You can do this via the ALLOCATE EXTENT clause of ALTER TABLE, and via the FILE clause of SQLLoader. At msot other times, you have to be creative. For example, if your datafiles are 50M and 100M in size, then creating a table with an initial extent of 60M will put the first extent of the table in the second file. Future extents of the table may go in either datafile, depending on their extent sizes. An extent cannot span datafiles.

If you want to get rid of a datafile, you have to rebuild the tablespace it is a part of. Export the objects in the tablespace (along with grants, etc), rebuild the tablespace with a single datafile that is large enough to hold all the objects, and Import the data back in.

There's plenty of documentation on these subjects, including <DISCLAIMER> Chapters 4 and 10 of Oracle8 DBA Handbook and Chapter 5 of Oracle8 Advanced Tuning & Administration </DISCLAIMER> among others.

Hope this helps.

Kevin Loney
http://www.kevinloney.com Received on Tue Sep 22 1998 - 09:08:45 CDT