Been there... Done that. After we estimated coalescing free space would take 10 hours.
drop all the objects in the tablespace delete from fet$ for that tablespace add a single row in fet$ for the entire tablespace drop the tablespace (reduced from 10 hours to couple of seconds). recreate the tablespace. DISCLAIMER: I do not warrant the accuracy, adequacy or completeness of this information. In no event, will I be liable for any damages, direct or indirect, losses, expenses, or outages, or system failure. If you are brave and helpless enough, to do the things I did, it's at your own risk. And for the answer to question 3), this is not normal. You should never let any tablespace run into this many number of free extents. Unless you want to start defragmenting, and stop living ;-) http://www.dbatoolbox.com/WP2001/spacemgmt/defrag.htm Regards Raj Connor McDonald To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> <hamcdc@yahoo cc: .co.uk> Subject: Re: Large record count in fet$ for TEMP tablespace Sent by: root@fatcity. com March 19, 2002 12:08 PM Please respond to ORACLE-L I've seen some hacks in the past involving dropping all segments for that tspace, direct deletes from fet$ and then dropping the tablespace... Not for the faint hearted, certainly not supported and you never heard it from me :-) Cheers Connor --- "Casey A. Jordan" <[EMAIL PROTECTED]> wrote: > When trying to select * from > DBA_FREE_SPACE_COALESCED the query hangs. It also > hangs when trying to calculate free space from the > DBA_FREE_SPACE view. I looked in the sys.fet$ table > and found that the TEMP tablespace has 21423 > entries and I believe this is my problem. > > Now for my questions. > > 1. Is coalescing the best solution for this > problem? > 1. Can I coalesce the tablespace without changing > it to a permanent tablespace? > 2. Is there a faster way to drop the exents then > the alter tablespace coalesce command? I ran it for > about 10 minutes and canceled it. > 3. Is this normal and do I need to coalesce the > TEMP tablespace on a regular basis to avoid this > happening again? > > > Thanks, > CAJ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
