Re: Drop a Temporary Segment
Date: Thu, 28 Jun 2012 17:16:24 +0300
Message-ID: <OFB580A6F1.7D65EB16-ONC2257A2B.004C51E9-C2257A2B.004E681F_at_seb.lt>
DBMS_SPACE_ADMIN was helpfull once in a while.
Please consider the environment before printing this e-mail
From: "Jeffrey Beckstrom" <JBECKSTROM_at_gcrta.org>
To: <japplewhite_at_austinisd.org>, <Oracle-L_at_freelists.org>, <oracle-l-bounce_at_freelists.org>
Date: 2012.06.28 15:02
Subject: Re: Drop a Temporary Segment
Try emptying the recycle bin. We have had a problem where if drop a table
with a lob, segments remain until clear the recycle bin.
>>> <japplewhite_at_austinisd.org> 6/27/12 5:31 PM >>>
64bit Oracle10g 10.2.0.4 EE on Linux. Locally-managed tablespaces.
Earlier today I did a Segment-Exchange on a large table with a huge
out-of-line LOB Segment. The empty single partition exchanged-into table
had local indexes and all the structures matched. The Segment Exchange
was successful. I then mistakenly did a Truncate Table on the now huge
partitioned exchanged-into table. That ran for hours until I killed it -
my 2nd mistake. I then did a Drop Table on it, which completed in
seconds. However, now I have a 56 GB Temporary segment named 10.5755 in
the tablespace designated for LOB storage. This is a Production database
so I can't bounce it in hopes that SMon will clean it up. It has
persisted for a couple hours now.
How do I get rid of it? I tried Alter Tablespace xxxxx Coalesce, but that didn't do the trick.
BTW, when do you think this Temp segment got created, during the Segment Exchange, the Truncate, or the Drop?
Thanks.
Jack C. Applewhite - Database Administrator Austin I.S.D. - MIS Department
512.414.9250 (wk)
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 28 2012 - 09:16:24 CDT