Subject: Re: How do you determine free space in DB?
References: <> (Dalton M. de Andrade) writes:

>I would like to know how to determine the following:
>1. How much space (in bytes) is allocated in the datafile(s) for a
>2. How much of the allocated space is used/free;
>3. How much unallocated space there still is in the datafile(s).
>We're running Oracle 7.1.4 on a SCOUnix box.
>Any help will be appreciated.  
>Pls e-mail me at

1. select tablespace_name, file_name, bytes from dba_data_files;

2. In Oracle 7.3, you can use the "dbms_space.unused_space" procedure
   to determine the used and unused space within a table.

   For your case (Oracle 7.1.4), you can see how many blocks are being USED by
   the table:

   select count(distinct substr(rowid,15,4)) from TABLE_NAME;

   The total ALLOCATED can be determined with:

   select blocks from dba_segments where segment_name = 'TABLE_NAME';

   (replace TABLE_NAME with the table you wish to analyze).

3. SELECT, b.tablespace_name,
       substr('Free : '||sum(b.bytes)/1024/1024,1,30) File_Size
   FROM dba_free_space b, v\$database a
   GROUP BY b.tablespace_name,
   SELECT, b.tablespace_name,
          substr('Total: '||sum(b.bytes)/1024/1024,1,30)
   FROM dba_data_files b, v\$database a
   GROUP BY b.tablespace_name,
   ORDER BY 1,2,3
-Ari Kaplan
Independent Oracle DBA Consultant

<-> For 60+ technical tips, visit my Web Page:                    <->
<->                                                               <->
<->                                    <->
<->                                                               <->
<->             email:                    <->

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