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.