Re: How do you determine free space in DB?

From: Ari Kaplan <akaplan_at_psycfrnd.interaccess.com>
Date: 1997/05/06
Message-ID: <5knndk$hoe@psycfrnd.interaccess.com>#1/1

akaplan_at_psycfrnd.interaccess.com (Ari Kaplan) writes: #2 should have read:  

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).

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

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

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

>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                    <->

><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-> Received on Tue May 06 1997 - 00:00:00 CDT