> 
> Hi Ari,
> 
> I have a tablespace fragmentation problem (large free space fragmented into
> small non-contiguous chunks of space) on an tablespace dedicated to indexes
> for all db schemas. While looking at your site, I found among the Oracle
> tips on your site the #67 one about Tablespace fragmentation.
> 
> You answered that "Exporting, dropping, and importing everything will do the
> job." when Nadira Gangadhar talks about "exporting, dropping the tablespace,
> and importing" to defragment tablespaces.
> Does this mean that, in my case, I will have to export the whole database
> before dropping the tablespace, then re-import the whole database, to
> de-fragment one tablespace only?

You could do that, or find out which objects are in a tablespace (see below),
drop only those objects, coalesce the tablespace, and import only those
objects. It's trickier (you have to recompile any packages/procedures/triggers/
views that are dependent on the objects being dropped, if any).

> 
> Another question: what would be the query to find out which objects are into
> a given tablespace?

Try:

SELECT * FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'tablespace_here';

> 
> Thank you very much,
> 
> Karina Chami
> 
Best of luck,


-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 175+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page tm"> Back to Ari Kaplan's Home Page