Re: dropping a datafile

From: Vince Laurent <eAddict_at_yahoo.com>
Date: Tue, 28 Jan 2003 13:27:42 -0600
Message-ID: <gvld3v4veckctvbf5dfcjrut3tftmmcnk1@4ax.com>

I guess I am not too clear on what I want to do. Here is my setup:

We have a tablespace, PSAPBTABD, that currently contains nearly 200+ individual tables, such like:

Owner Object Type Tablespace KBytes Blocks Extents

SAPR3  GLPCA  TABLE  PSAPBTABD 8,938,560 1,117,320       47 
SAPR3   BSIS  TABLE  PSAPBTABD 4,309,520   538,690       41 
SAPR3  TST03  TABLE  PSAPBTABD 4,270,800   533,850       51 
SAPR3 ACCTIT  TABLE  PSAPBTABD 3,942,800   492,850       44 
SAPR3   COEP  TABLE  PSAPBTABD 3,941,480   492,685       43 

and so on...

The tablespace itself is made up of 25 data files.

/oracle/PRD/sapdata1/btabd_1/btabd.data1 /oracle/PRD/sapdata1/btabd_2/btabd.data2 ...
/oracle/PRD/sapdata11/btabd_24/btabd.data24 /oracle/PRD/sapdata11/btabd_25/btabd.data25

Now, I have managed to pull some of the tables, lets say GLPCA, into its own tablespace (PSAPGLPCAD) and now I have 8+G free in PSAPBTABD. I can see, using the 3rd party utility, that the last 2 datafiles are no longer being used (#24 and #25). How can I safely remove the datafiles from the database? I know how to remove them from the OS level, but how do I tell Oracle that they are no longer there/needed?

Thanks!
Vince

On Tue, 28 Jan 2003 12:54:52 -0500, "D.J Platt" <david-platt_at_cogeco.ca> wrote:

>Once you are sure there is nothing in the tablespace:
>
>- take the tablespace offline.
>- drop the tablespace
>- remove the associated files with your favorite OS command.
>
>"Vince Laurent" <eAddict_at_yahoo.com> wrote in message
>news:9ded3v4ajn3k5lil868tj7v6pt4jdtqaic_at_4ax.com...
>> On our SAP system, we have a few tablespaces that are made up of MANY
>> datafiles. I have managed (via a 3rd party tool) to reorganize the
>> largest tables into thier own tablespacs. Now the original tablespace
>> is sitting very empty BUT the physical datafiles are still there. Is
>> there a way to drop them?
>>
>> I found this for growing them but nothing for removing them. I would
>> like to reclaim the space.
>>
>> Since I can apparently ADD space to a datafile as well as remove
>> space, can I remove the datafile?
>>
>> ----------------------------------------------------------------
>> One can manually increase or decrease the size of a datafile from
>> Oracle 7.2 using the
>>
>> ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
>>
>> command.
>> Because you can change the sizes of datafiles, you can add more space
>> to your database without adding more datafiles. This is beneficial if
>> you are concerned about reaching the maximum number of datafiles
>> allowed in your database.
>>
>> Manually reducing the sizes of datafiles allows you to reclaim unused
>> space in the database. This is useful for correcting errors in
>> estimations of space requirements.
>> ----------------------------------------------------------------
>>
>> Thanks!
>> Vince Laurent
>> p.s We are running Oracle 8.1.7.3 on HP-UX 11.0
>
Received on Tue Jan 28 2003 - 13:27:42 CST