Drop a Temporary Segment
From: <japplewhite_at_austinisd.org>
Date: Wed, 27 Jun 2012 16:31:27 -0500
Message-ID: <OF0D01EF84.AC9B69A9-ON86257A2A.00750094-86257A2A.007639F1_at_austinisd.org>
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: Wed, 27 Jun 2012 16:31:27 -0500
Message-ID: <OF0D01EF84.AC9B69A9-ON86257A2A.00750094-86257A2A.007639F1_at_austinisd.org>
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
Received on Wed Jun 27 2012 - 16:31:27 CDT