Re: Large record count in fet$ for TEMP tablespace

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 <ORACLE-L_at_fatcity.com>        
                    <hamcdc_at_yahoo        cc:                                                                        
                    .co.uk>              Subject:     Re: Large record count in fet$ for TEMP  tablespace           
                    Sent by:                                                                                        
                    root_at_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" <cjordan_at_imcwv.com> 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: Rajesh.Rao_at_jpmchase.com

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: ListGuru_at_fatcity.com (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).