> Ari,
> 
> I have a very useful script which reports on Spacebound objects. To
> get around
> these spacebound problems, I tend to amend the next extent on the
> object size to be
> equal or below the largest amount of contiguous freespace on the
> tablespace.
> 
> I have many scripts that report on freespace for each tablespace.
> Although these
> are very useful, I really need a report that will give me a list of 
> contiguous freespace sizes
> on each tablespace.
> 
> I have a tool that reports this (at work) , but seeing that I
> sometimes have to work from home, I
> really need a SQL script that can do this.
> 
> Can you help ?
> Thankyou,
> 
> Martin.
Sure. To report the freespace sizes in each tablespace, do the following:

SELECT a.TABLESPACE_NAME, b.FILE_NAME, a.bytes
FROM DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.FILE_ID = b.FILE_ID
ORDER BY a.TABLESPACE_NAME, a.BYTES
/

-or- if you want in megs, do the following:

SELECT a.TABLESPACE_NAME, b.FILE_NAME, a.bytes/1024/1024 "Megs"
FROM DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.FILE_ID = b.FILE_ID
ORDER BY a.TABLESPACE_NAME, a.BYTES
/

-Ari Kaplan
Independent Oracle DBA Consultant

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

Back to Ari Kaplan's Home Page x.htm"> Back to Ari Kaplan's Home Page