Newsgroups: comp.databases.oracle.server,culist.oracle-l
Subject: Re: How do you determine free space in DB?
References: <336a4a86.25286403@news.videotron.ca>

dandrade@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@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,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 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:                    <->
<->                                                               <->
<->              www.arikaplan.com                                <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

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