Re: drop tablespace, delete datafiles, but space not returned?

On Tue, 02 Dec 2003 21:54:40 -0800, yls177 wrote:

> okay.. i think i know what went wrong.... after dropping the tablespaces
> and removing the datafiles , the space is not reclaimed back to the volume
> group cos they are still in use by the filesystem in the logical volume.so
> what i need to do is to reduce the size of the filesystem. of course, i
> should NOT reduce beyond the size of the existing filesystem. so after
> doing that, the free space will be returned to the volume group, right?
>

My experience is HP, so disregard as appropriate !

If you create a volume group, it has a specific maximum size as governed by the amount of disc space you allocate to it. Say 250 Mb.

Within this VG, you allocate logical volumes of various sizes, say 2 LVs at 125 Mb each. You now have two 'discs' of 125 Mb each.

If you create a file of 100 Mb in each LV, you still have 2 'discs' with 25 Mb free. Subsequent deletions of said files return the free space to 125 Mb again.

If you use Oracle to create a tablespace of 100 Mb in each LV, your free space decreases to 25 Mb again.

Deleting the tablespaces doesn't delete the datafiles (unless 9i and you specify INCLUDING CONTENTS AND DATAFILES as part of the DROP TABLESPACE command) so the files are no longer used by Oracle but exist on the LVs. You currently have 25 Mb free on each LV.

Using the OS to delete the (unused) tablespace files should return you to 125 Mb free in each LV.

The only time I've personally had a problem with space not being released was on my first day as an untrained DBA. I was told that a tablespace had been deleted so I could go ahead and delete the datafile. I did this, and no space was freed up.

The server was short of disc space and I had to create a new tablespace, so I had to bounce the instance. Oops ! The previous DBA whose instructions I was following had (a) already left the company and (b) had not dropped the tablespace. The waste solids had an unscheduled interface with the air conditioning.

I had about 120 developers waiting around for their source code repository database to come back up and I had bolloxed it. I eventually found '.. backup controlfile to trace' and did so, created a new control file (having dumped the whole system) without the missing datafiles, restarted and dropped the tablespce correctly.

I suspect that if you can't free up the space by dropping the datafiles then either the tablespace didn't drop correctly, or the datafiles are still being used by another process - which could indicate a problem.

> also
>
> 1) i can reduce the size of the filesystem without affecting any other
> stuff. all i need to ensure is that i reduce the space so that there are
> enough for the existing data

You cannot reduce the size of the LV or VG without trashing all of the data. Plus, if there are any (other) processes using the LVs, then you won't be able to resize them anyway as they have to be UMOUNTed before resizing.

Cheers,
Norm.

-- 
Delete the obvious bit from my email address to reply by email.