Defrag a tablespace

Dear Oracle Experts,

                                                                                               
                                                 Having run "alter                             
                                                 tablespace DEVELOPMENT                        
                                                 coalesce' there was no                        
                                                 change in the number of                       
                                                 chunks after running                          
                                                 this command. I suspect                       
                                                 that this means that                          
                                                 these spread out chunks                       
                                                 are not neighboring                           


                                                 (contiguous) blocks. Is                       

                                                 there a way to export a                       
                                                 tablespace, with                              
                                                 several different                             
                                                 schema owners, to                             
                                                 eliminate the                                 
                                                 fragmentation in this                         
                                                 tablespace? It has 501                        
                                                 chunks in it while                            
                                                 having 396Mb of free                          
                                                 space.                                        
                                                                                               
                                                 Would I need to do a                          
                                                 complete export of the                        
                                                 database, drop and                            
                                                 recreate the fragmented                       
                                                 tablespace, then import                       
                                                 with ignore=Y or is                           
                                                 there another way?                            


                                                 (This is on a 8.0.4 Db                        

                                                 on a Sun, if that                             
                                                 helps.)  Also I could                         
                                                 write a query to grep                         
                                                 all the tables in this                        
                                                 tablespace, taking care                       
                                                 to preserve referential                       
                                                 integrity, export all                         
                                                 the tables, grep by                           
                                                 query, then recreate                          
                                                 the tablespace and                            
                                                 import.                                       
                                                                                               
                                                 I could not find an                           
                                                 export tablespace                             
                                                 script. Does anyone                           
                                                 have one they are                             
                                                 willing to share?                             
                                                                                               

Thanks in advance for your time.

Regards,
Phil Lima Received on Thu Nov 09 2000 - 08:30:49 CST