Re: Question regarding lob column
- From: Maureen English <sxmte@xxxxxxxxxxxxxxxx>
- To: jack@xxxxxxxxxxxx
- Date: Wed, 06 May 2009 16:00:12 -0800
Thanks Jack,
The user was supposed to be deleting records older than 6 months, but
that never happened. Now, 14 months later, after the tablespace has
grown to 32G, they're ready to delete rows and want to see that the
delete really does free up the space. I just want to be able to run
a few queries to show them that the space really was made available.
I do expect that I will only need to do this once, then, as time goes
by rows will get added, then they will run the purge job on a monthly
basis. Hopefully, oracle will reuse the space made available by the
deletes, instead of taking new extents, and we won't have to keep
adding datafiles...we limit our datafiles to 2G each.
- Maureen
Jack van Zanen wrote:
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@xxxxxxxxxxxxxxxx
<mailto:sxmte@xxxxxxxxxxxxxxxx>>
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
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
