Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.
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