Re: dropping a datafile

The problem is: you can't. You can do:

   alter database datafile 'filename' offline drop; but this is not what it looks like. It does *not* remove the file from the database. It puts it offline so at startup the SCN of the file is not checked, nor updated in a checkpoint. To put it online again a recovery of the file is needed. I am not sure of other impact when you do this but it should improve checkpoint performance, especially when there are many many files.

The best solution would be to use "alter table move" to move all contents to other tablespace(s) and then drop the tablespace entirely, followed by removing the datafiles on the OS level. (Don't forget to rebuild all indexes on those table because they get an "unusable" state). Tip: I always use /usr/sbin/fuser filename before I remove a file to really check no process has the file open anymore. You won't notice it till the next database open.

With less impact your only solution is to resize the files to it's smallest possible size (query DBA_EXTENTS to find the highest allocated block in each file). "alter table move" within this tablespace could free up more space but you can't influence where Oracle allocates the new extents for the table so some "good luck" is required here.
When files don't allocate any extents anymore you can resize to 10 Oracle blocks or so. I read somewhere that when <5 blocks Oracle does not allocate extents anymore in that file. But don't make it too small. Oracle needs at least 1 header block and I wonder if it needs more. We accidently resized a file once to a very small number like 1 or 2 or so (forgot to type the "M") and Oracle did it but the file was corrupted and we had a difficult job to fix it, togheter with Oracle Support on the phone.

Vince Laurent <eAddict_at_yahoo.com> schreef in berichtnieuws gvld3v4veckctvbf5dfcjrut3tftmmcnk1_at_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 - 14:11:04 CST