Re: Question regarding lob column

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Thu, 7 May 2009 09:34:35 +1000
Message-ID: <77a615a70905061634o1ff21194t6bec5c39aa33aeb2_at_mail.gmail.com>

Delete does not free up space in the tablespace. It frees up space within the table that can quite possibly be reused.

If you need to free up space within the tablespace you need to reorg the table, which is a waste of time if you are not going to be deleting regularly. It will simply grow again.

Jack

2009/5/7 Maureen English <sxmte_at_email.alaska.edu>

> Hi,
>
> We have a table that has a lob column. The table is in tablespace A, the
> lob storage is
> in tablespace B (with an associated index).
>
> Every month a job runs and adds more data to this table, so we've had to
> add datafiles
> on a regular basis. Now the user wants to delete some data so we don't
> have to keep
> adding datafiles, but can reuse some of the space that's already been
> allocated to the
> tablespaces.
>
> So, I got a rowcount from the table before the user deleted anything, and I
> also got the
> bytes and extents values from dba_segments for the tablespaces.
>
> The user then proceeded to delete at least 1/3 of the rows in the table.
> When I did my
> queries again, the rowcount had changed as expected, but the bytes and
> extents values
> didn't change for the segments in dba_segments. There were 172 extents
> before and there
> are 172 extents after the delete.
>
> Any ideas as to why no extents appear to have been freed up?
>
> We don't have Grid Control or EM available, so I can't see any pictures of
> space usage....
>
> - Maureen
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Jack van Zanen

-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 06 2009 - 18:34:35 CDT