Re: How to find out how how big a database is?

From: sysadmin <sysadmin_at_vdoj.vic.gov.au>
Date: 1998/01/23
Message-ID: <34C828A5.6F96@vdoj.vic.gov.au>#1/1

All the previous replies will show how much space is allocated, but not necessarily the amount of space used.

This SQL will show the amount of space allocated, used and a % used figure.

You need to run this as SYS

set verify off
set termout off
column bls new_value BLOCK_SIZE
select blocksize bls
from sys.ts$
where name='SYSTEM'
/

set termout on
set verify on
SET ECHO OFF
prompt Print Free database space
set newpage 0
ttitle center 'Free Space in the Database'- right 'Page:' format 999 sql.pno skip skip set feedback off
column TABLENAME format a30 heading 'Tablespace Name'

column tot format 99,999,999 heading 'Size (K)'
column fsp Format 99,999,999 heading 'Free (K)'
column pctused Format 999.99 heading '% Used'

set termout off
drop view dictools_free_space
/

create view dictools_free_space as

select 	t.name tablespace_name,
	sum(fet.length)*&&BLOCK_SIZE/1024	free_space
from 	sys.ts$ t,
	sys.fet$ fet
where 	fet.ts# = t.ts#

group by t.name
/

set termout on
column tsno noprint
compute sum of tot fsp on report
break on report
spool c:\orant\bin\freespac.txt

select 	t.ts# 		tsno,
	t.name		TABLENAME,
	sum(fil.blocks)*&&BLOCK_SIZE/1024       tot,
        fre.free_space fsp,
	(((sum(fil.blocks)*&&BLOCK_SIZE/1024 ) - fre.free_space )*100 )/
	(sum(fil.blocks)*&&BLOCK_SIZE/1024)       pctused
from 	sys.file$ fil,
	sys.ts$ t,
        dictools_free_space fre
where 	fil.ts# = t.ts#
and     fre.tablespace_name = t.name

group by t.ts#,t.name,fre.free_space
order by t.ts#
/

prompt End of Report
set termout off
drop view dictools_free_space
/

set termout on
spool off
set feedback 6
clear breaks
clear columns
clear computes
set verify on

This is some sample output:

                           Free Space in the Database                
Page:   1
                                                                              
Tablespace Name                   Size (K)    Free (K)  %
Used                
------------------------------ ----------- -----------
-------                
SYSTEM                              51,200      40,932  
20.05                
RBS                                 15,360       4,368  
71.56                
TEMP                                51,200      51,198    
.00                
USER_1                              10,240       7,208  
29.61                
LIC_1                              307,200     307,188    
.00                
LIC_1X                             102,400     102,388    
.01                
DBA_1                                1,024       1,022    
.20                
                               -----------
-----------                        
                                   538,624    
514,304                        

End of Report

Hope this helps.

Andy Horne

L120bj wrote:

> 
> >Subject: How to find out how how big a database is?
> >From: "Metrix" <sherryl_at_metrix-inc.com>
> >Date: 1/19/98  7:13PM  GMT
> >Message-id: <01bd250e$22f20c60$1a0110ac_at_metrix4047.metrix-inc.com>
> >
> >In Oracle 7.2 how do I find out how big a database is, i.e. how much disk
> >space it's used?
> >
> >Sherry Li
> >

Received on Fri Jan 23 1998 - 00:00:00 CST