Re: How do you determine free space in DB?

dandrade_at_icao.org (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
>database;
>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 dandrade_at_icao.org.

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,1,8)) from TABLE_NAME;

   The total ALLOCATED can be determined with:

   select blocks from user_tables where table_name = 'TABLE_NAME';

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

3. SELECT a.name, 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, a.name
   UNION
   SELECT a.name, 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, a.name
   ORDER BY 1,2,3
   /  

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 60+ technical tips, visit my Web Page:                    <->
<->                                                               <->
<->              http://homepage.interaccess.com/~akaplan         <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->