Re: Drop a Temporary Segment
From: Jeffrey Beckstrom <JBECKSTROM_at_gcrta.org>
Date: Thu, 28 Jun 2012 08:01:02 -0400
Message-Id: <4FEC0F3E0200000B004ED33E_at_groupwise.gcrta.org>
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.
Jack C. Applewhite - Database Administrator Austin I.S.D. - MIS Department
512.414.9250 (wk)
Date: Thu, 28 Jun 2012 08:01:02 -0400
Message-Id: <4FEC0F3E0200000B004ED33E_at_groupwise.gcrta.org>
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-lReceived on Thu Jun 28 2012 - 07:01:02 CDT