Re: Release space used by empty BLOB segments
- From: japplewhite@xxxxxxxxxxxxx
- To: adar666@xxxxxxxxxxxx
- Date: Mon, 10 Oct 2011 12:01:48 -0500
Yechiel,
Have you considered "Alter Table <Table> Shrink Space Cascade"? I just
discovered it recently and it's very useful. I've used it on several very
large tables, one with a huge LOB segment. It compacts the table segment,
as well as all LOB and index segments, releasing the unused space. The
excellent part is that the table can still have DML activity - it remains
open and available.
The tables on which I've used it were multi tens of millions of rows,
occupying up to 35 GB. It takes a LONG time for tables that large - 9 to
12 hours - but works like a charm. The only downside I've discovered is
that it appears to give Logical Standby DBs indigestion and they simply
will not perform SQL Apply. I'm working on discovering why, but so far no
luck.
Anyway, I'm very happy with this and wish I'd discovered it years ago -
first available in 10gR1.
We're on Enterprise 64 bit 10.2.0.4 on Linux.
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk) / 512.935.5929 (pager)
From: Yechiel Adar <adar666@xxxxxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Date: 10/10/2011 07:19 AM
Subject: Release space used by empty BLOB segments
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
I have a partitioned (by week) table that holds data and images.
The data is kept for 7 years (banking) while the images are kept only
for one year.
The images are deleted by updating the fields to empty blobs.
The tables has local indexes and a bunch of global indexes.
The problem is that the empty blob segments are not released after their
content id deleted.
1) How can I release the unused space without making the global indexes
unusable?
2) Preferably while the users are working (but this not a must)?
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
--
Yechiel Adar
Israel
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
