Re: Get the size of database

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 16 Jan 1999 14:07:21 GMT
Message-ID: <36a19ccb.1855127@192.86.155.100>

A copy of this was sent to paulinelam_at_poboxes.com (if that email address didn't require changing) On Sat, 16 Jan 1999 18:41:35 +0800, you wrote:

>Hi All,
>
>How can I get the size of the database (allocated,occupied, free
>spaces)?
>
>Thanks.
>Pauline

Here is one way, it shows by tablespace

                                                             %
Tablespace Name        
KBytes allocated         
kbytes Used         
kbytes Free   
% of space Used      

Largest contigous free set of blocks (largest extent you can allocate in that tablespace)

---

• free.sql --
• This SQL Plus script lists freespace by tablespace

  ---

column dummy noprint

column  pct_used format 999.9       heading "%|Used" 
column  name    format a16      heading "Tablespace Name" 

column Kbytes format 999,999,999 heading "KBytes" column used format 999,999,999 heading "Used" column free format 999,999,999 heading "Free" column largest format 999,999,999 heading "Largest" break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,

	   kbytes_alloc kbytes, 
	   kbytes_alloc-nvl(kbytes_free,0) used,
	   nvl(kbytes_free,0) free, 
	   ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used,
	   nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free, 
			  max(bytes)/1024 largest,
			  tablespace_name
	   from  sys.dba_free_space 
	   group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc, 
			  tablespace_name 
	   from sys.dba_data_files 
	   group by tablespace_name ) b

where a.tablespace_name (+) = b.tablespace_name order by &1
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jan 16 1999 - 08:07:21 CST