RE: Release space used by empty BLOB segments
Date: Mon, 10 Oct 2011 17:06:33 +0000
Message-ID: <DF78EADE484D37419A53F5C898629DB7F763BD_at_USMAIL2K1001.us.micros.int>
Hi Jack,
You might look into Bug 9532911: LOB SHOWING INCORRECT DATA ON DATA GUARD STANDBY SITE as part of your troubleshooting...
Stephan Uzzell
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of japplewhite_at_austinisd.org
Sent: Monday, 10 October, 2011 13:02
To: adar666_at_inter.net.il
Cc: ORACLE-L; oracle-l-bounce_at_freelists.org
Subject: Re: Release space used by empty BLOB segments
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_at_inter.net.il> To: ORACLE-L <oracle-l_at_freelists.org> Date: 10/10/2011 07:19 AM
Subject: Release space used by empty BLOB segments Sent by: oracle-l-bounce_at_freelists.org
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.
- How can I release the unused space without making the global indexes unusable?
- 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 10 2011 - 12:06:33 CDT